All Forums Database
nealvenna 9 posts Joined 10/12
24 Jun 2016
unix timestamp with timezone to teradata timestamp

Hi all, 
I have unix epcoh timestamp with timezone and it needs to be converted to teradata timestamp(6). I have tried to solve this by seeing old posts in teradata forums, but none of them are related to timezone. One of the example in the sample is 1466017810720. Kindly help.
Thanks
 
 

nealvenna 9 posts Joined 10/12
24 Jun 2016

Hi all
Below code worked for me.
 

sel 

TO_TIMESTAMP( CAST( ( 1466017810720 - 1466017810720 MOD 1000 ) / 1000 AS BIGINT)

 ) + ( 1466017810720 MOD 1000 * INTERVAL '00:00.001000' MINUTE TO SECOND ) AT 'GMT' AS gmt_time

dnoeth 4628 posts Joined 11/04
26 Jun 2016

A Unix epoch doesn't hava a timezone, it's based on UTC. 
Your timestamp simply includes milliseconds, your caluclation can be simplyfied to:

-- assuming your epoch column is a BIGINT, otherwise you have to use
-- (TO_TIMESTAMP(CAST(epoch AS BIGINT) / 1000) AT 0)

(TO_TIMESTAMP(epoch / 1000) AT 0)
 + ( epoch MOD 1000 * INTERVAL '0.001' SECOND )  AS gmt_time

 

Dieter

nealvenna 9 posts Joined 10/12
26 Jun 2016

Thanks Dieter

You must sign in to leave a comment.