UNIX EPOCH to AEST conversion

General

UNIX EPOCH to AEST conversion

Hi ,

 

I need to convert UNIX EPOCH to Australian Eastern Standrd time and using the below SQl to do

 

,CAST(((CAST(DATE '1970-01-01' + (CAST(EPOCH_TIME AS INTEGER) / 86400) AS TIMESTAMP (0) AT 0)) AT 0) + ((CAST(EPOCH_TIME AS INTEGER) MOD 86400) * INTERVAL '00:00:01' HOUR TO SECOND) AS TIMESTAMP (0))

The above gives proper results prior to Day Time savings conversion ( Prior to October first). POst October 1, values are higher by 1 hour.

 

Even tried AT TIME ZONE 'Australia Eastern'. This gives proper values post October 1 but prior to October 1, values are distorted.

 

Please help.

 

Thanks

Santhosh

1 REPLY
Junior Contributor

Re: UNIX EPOCH to AEST conversion

Your calculation works fine for me:

SELECT 1506786900 AS EPOCH_TIME,
   Cast(((Cast(DATE '1970-01-01' + (Cast(EPOCH_TIME AS INTEGER) / 86400) AS TIMESTAMP (0) AT 0)) AT 0) 
   + ((Cast(EPOCH_TIME AS INTEGER) MOD 86400) * INTERVAL '00:00:01' HOUR TO SECOND) AS TIMESTAMP (0)) 
   AT 'Australia Eastern'

2017-10-01 01:55:00+10:00


SELECT 1506786900+600 AS EPOCH_TIME,
   Cast(((Cast(DATE '1970-01-01' + (Cast(EPOCH_TIME AS INTEGER) / 86400) AS TIMESTAMP (0) AT 0)) AT 0)
   + ((Cast(EPOCH_TIME AS INTEGER) MOD 86400) * INTERVAL '00:00:01' HOUR TO SECOND) AS TIMESTAMP (0)) 
   AT 'Australia Eastern'

2017-10-01 03:05:00+11:00

What's your exact Teradata release?