unix timestamp with timezone to teradata timestamp

Database
Junior Supporter

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

3 REPLIES
Junior Supporter

Re: unix timestamp with timezone to teradata timestamp

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

Junior Contributor

Re: unix timestamp with timezone to teradata timestamp

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
Junior Supporter

Re: unix timestamp with timezone to teradata timestamp

Thanks Dieter