Convert Timestamp from GMT to EST

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Junior Supporter

Convert Timestamp from GMT to EST

Hi,

I have posted this question in another thread, but i dont see that, so posting again. I find this new interface very confusing, apologies for that.

 

I have to convert a timestamp from GMT to EST. I can use 'at   ameriacan eastern' for that.I wanted to check if this takes into account the day light saving as well. I am pretty sure it does take into account as mentioned in the manuals, but wanted to confirm my understanding.

Note: Teradata Database will automatically adjust the time zone displacement to account for
the start or end of daylight saving time only if you specify a time zone using a time zone string
that follows different DST and standard time zone displacements.

--Samir Singh

 

3 REPLIES
Junior Supporter

Re: Convert Timestamp from GMT to EST

Hi All,

Any help on this ?

I have another input on this. I will have a column in a table which will have a GMT time. Now i want to convert it to EST. I can use at  'America eastern'  to convert it to EST. As a test, when i use below sql on my sql assist, it considers '2015-12-19 16:44:00'  to be EST and convers to MST appropiately.

sel cast ('2015-12-19 16:44:00' as timestamp(0))  at  'America mountain'. 

This is becasue my system/session setting is EST, it takes '2015-12-19 16:44:00'  as EST. Now, when i have data in the table as GMT, will that still consuder it in EST ? But, i want it to consider it to be GMT and conver to EST. So, do i need to convert my column value to GST first and then use at  'America eastern'  to convert to EST ? How do do that ? Any input will be helpful.

 

--Samir Singh

Enthusiast

Re: Convert Timestamp from GMT to EST

Just append -00:00 at the end of your date as time zone infomation and below query should work for you.

 

SEL CAST ('2015-12-19 16:44:00-00:00' AS  TIMESTAMP WITH TIME ZONEAT  'America Eastern';

Enthusiast

Re: Convert Timestamp from GMT to EST


prsently im getting time from GMT, so i'm converting time based the record is coming from which time zone.
We have seperate table which will capturu all timezones and using that table i'm identifying that record is coming from which zone.

CASE WHEN TZ.TIME_ZONE = 'EAST' THEN TST.START_DT - INTERVAL '4' HOUR
WHEN TZ.TIME_ZONE = 'CENTL' THEN TST.START_DT - INTERVAL '5' HOUR
WHEN TZ.TIME_ZONE = 'MTN' THEN TST.START_DT - INTERVAL '6' HOUR
WHEN TZ.TIME_ZONE = 'PACF' THEN TST.START_DT - INTERVAL '7' HOUR


Based on below logic i'm adjusting time. But, during day light saving (November and March) how do i modify this logic not fot temporarly i'm looking for permanent solution.
I'm trying to use parameter file something like that but i'm not sure exactly how to do that.
Can anyone help me on this?