All Forums Database
teradatauser2 236 posts Joined 04/12
20 Oct 2015
Timestamp upation issue

Hi,
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 ?
Thanks !
Samir

teradatauser2 236 posts Joined 04/12
21 Oct 2015

Hi All,
Can someone help me on this ?
--Samir Singh

kirthi 65 posts Joined 02/12
21 Oct 2015

That was a Daylight savings date, the clocks will be pushed by 1 hour at 2.00 AM to 3.00 AM. 

teradatauser2 236 posts Joined 04/12
22 Oct 2015

Thanks Kirthi, so will there be no 2:00 AM on that day ? What if i want to update the time as 2:00 on that day ?

kirthi 65 posts Joined 02/12
22 Oct 2015

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.
http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1184_111A/End_Logging-Details.015.190.html
 

Fred 1096 posts Joined 08/04
22 Oct 2015

WARNING - Do not change TimeDateWZControl value without reviewing all the implications carefully and talking it over with Teradata support.
 

teradatauser2 236 posts Joined 04/12
23 Oct 2015

Thanks everyone, this was helpful.

You must sign in to leave a comment.