When i am trying to do an update as below, the open_ts becomes - 3/8/2009 03:01:05 instead i expect it to be - 3/8/2009 02:01:05
UPDATE mydata.mytab1 SET OPEN_TS = '2009-03-08 02:01:05' WHERE ACCT_ID = 17001565;
I think, this has soemthing to take with time zone and day light saving, but i am unable to decipher it. Can someone help me on this ?
You can do it 2 ways.
At system level
You might need to change the setting for the DBS Control flag TimeDateWZControl.
At Session Level
SET TIME ZONE 'GMT+5:30' ( This is India Time zone where they dont use Day light savings and the time '2009-03-08 02:01:05' is a valid time there)
run the update SQL, you should see the data as you expected.
If you want to check the allowed time zone strings please refer to below link.
WARNING - Do not change TimeDateWZControl value without reviewing all the implications carefully and talking it over with Teradata support.