how to convert UTC time zone to EST Timezone.

Database
Highlighted
sk1
Enthusiast

how to convert UTC time zone to EST Timezone.

Hi all ,

 

thank you in advances

Can you please help me for timezone conversation ,

i want to convert UTC time zone to EST time ,

i treid with interval  but that is not working proper becasuse in day light timing it  will vary.

 """column LOG_TMSTMP conatin time zone in UTC , i want to convert into EST """

cast(cast(ENT.LOG_TMSTMP as format 'yyyy-mm-dd') as char(10)) as "Date",
cast(cast(ENT.LOG_TMSTMP as format 'HH:MI') as char(5)) as "Time",

FROM DDWP_V.CDN_RQST_EVT   ENT
    inner JOIN DDWP_V.PGM_CNTNT_DIM DIM ON DIM.MATRL_ID = ENT.DTV_MATRL_ID
    LEFT join ddwp_v.CUST_ACCT_DIM C on ENT.cust_acct_key =  C.cust_acct_key
where cast((ENT.LOG_TMSTMP   ) as format 'yyyy-mm-dd') in date -(37)

 

Tags (2)
9 REPLIES
Enthusiast

Re: how to convert UTC time zone to EST Timezone.

Converting UTC to EST is about MINUS 5 hours.

 

With a CASE you can check if the timestamp is in that period of time (hours less than 5) when it should minus a day too.

 

 

 

 

 

SyntaxEditor Code Snippet

SELECT 
cast(cast(
CASE WHEN (EXTRACT(HOUR from ENT.LOG_TMSTMP) < 5) THEN (ENT.LOG_TMSTMP - INTERVAL '1' DAY)
ELSE ENT.LOG_TMSTMP END as format 'yyyy-mm-dd') as char(10)) as "Date"
,cast(cast((ENT.LOG_TMSTMP - INTERVAL '5' HOUR) as format 'HH:MI') as char(5)) as "Time"

FROM DDWP_V.CDN_RQST_EVT   ENT
    inner JOIN DDWP_V.PGM_CNTNT_DIM DIM ON DIM.MATRL_ID = ENT.DTV_MATRL_ID
    LEFT join ddwp_v.CUST_ACCT_DIM C on ENT.cust_acct_key =  C.cust_acct_key
where cast((ENT.LOG_TMSTMP   ) as format 'yyyy-mm-dd') in date -(37)

 

Junior Contributor

Re: how to convert UTC time zone to EST Timezone.

Did you try

 ENT.LOG_TMSTMP AT 'America Eastern'

 

sk1
Enthusiast

Re: how to convert UTC time zone to EST Timezone.

hi dnoeth

thank you ..............

I used that but in my code there is below  condition 

where cast((ENT.LOG_TMSTMP   ) as format 'yyyy-mm-dd') in date -(37)

 if i will use here also , I'm getting error "request terrminted by TDWM and CPU error"

Because i have pull the data besed on this condition

 

 

Tags (1)
sk1
Enthusiast

Re: how to convert UTC time zone to EST Timezone.

hi Tryker,

 

i have condition

where cast((ENT.LOG_TMSTMP   ) as format 'yyyy-mm-dd') in date -(37)

 i need to fatch the data based on the codition  (

ENT.LOG_TMSTMP

) this should be EST time , then i will fatch ....

 

 

2. i need to convert time also  below one

,cast(cast((ENT.LOG_TMSTMP - INTERVAL '5' HOUR) as format 'HH:MI') as char(5)) as "Time"
Tags (1)
Enthusiast

Re: how to convert UTC time zone to EST Timezone.

Can you just minus INTERVAL '5' HOUR to the timestamp, and name it with "AS"

 

Now you can use the new name to format in the WHERE clause. it will be in EST

sk1
Enthusiast

Re: how to convert UTC time zone to EST Timezone.

but after day light saving , the time interval is 4 hours.  we need to  include that also ..

UTC  time will vary.......

sk1
Enthusiast

Re: how to convert UTC time zone to EST Timezone.

but after the day light  timing  , the time interval is 4 hours.  we need to  include that also ..

UTC  time will vary.......

Tags (1)
Teradata Employee

Re: how to convert UTC time zone to EST Timezone.

Why not cast the comparison value to timestamp instead? (Internal comparison will be based on UTC.) That's a one-time conversion vs converting every row in the large table. For example, if the session time zone is EST then

 

WHERE ENT.LOG_TMSTMP BETWEEN CAST(CURRENT_DATE - 37 as TIMESTAMP(0)) AND CAST(CURRENT_DATE - 37 AS TIMESTAMP(0)) + INTERVAL '23:59:59' HOUR TO SECOND

Enthusiast

Re: how to convert UTC time zone to EST Timezone.

Using dnoeth explanation:

SELECT
 ENT.LOG_TMSTMP AT 'America Eastern' as "EST"
, cast(cast(EST as format 'yyyy-mm-dd') as char(10)) as "Date"
, cast(cast(EST as format 'HH:MI') as char(5)) as "Time" FROM DDWP_V.CDN_RQST_EVT ENT inner JOIN DDWP_V.PGM_CNTNT_DIM DIM ON DIM.MATRL_ID = ENT.DTV_MATRL_ID LEFT join ddwp_v.CUST_ACCT_DIM C on ENT.cust_acct_key = C.cust_acct_key where cast((EST) as format 'yyyy-mm-dd') in date -(37)

 

It works for me using CURRENT_TIMESTAMP instead  ENT.LOG_TMSTM for get a timestamp . 

If you cannot allow that column in the final return of this select, use a WITH clause to get that value into a temporal table. 

I'm assuming the internal Teradata conversion of time zones, can handle your daylight hour change.

https://www.info.teradata.com/HTMLPubs/DB_TTU_16_00/index.html#page/SQL_Reference/B035-1145-160K/axu...