How to Update a timestamp field in a table in Teradata to convert from GMT o CST including daylight saving

Database

How to Update a timestamp field in a table in Teradata to convert from GMT o CST including daylight saving

We are having a table in teradata with a time stamp field defined as UPDATE_TS TIMESTAMP(6) and having values in GMT. We need to UPDATE those values to American Central Time(With Daylight Saving).

Because the field is not defined as "timestamp with timezone", it is tricky to update with a method that takes into consideration daylight savings time.

Sample values existing in table:

05/13/2016 10:05:32.456000

Can you please help me on this ?

1 REPLY
Senior Apprentice

Re: How to Update a timestamp field in a table in Teradata to convert from GMT o CST including daylight saving

Try if this works:

UPDATE_TS + (EXTRACT(TIMEZONE_HOUR FROM UPDATE_TS AT 'America Central') 
* INTERVAL '1' HOUR)

Of course you will encounter problems with two timestamps returning the same result when DST is switched back in fall.