Teradata: Epoch Time To Timestamp

Pradeep

Teradata: Epoch Time To Timestamp

➩ Epoch Time is defined as the number of seconds that have elapsed since 00:00:00 time of 1 January, 1970 (1970-01-01 00:00:00).
➩ Every day is treated as if it contains exactly 86400 seconds

➠ Convert Epoch Time to TIMESTAMP(0): Below conditions should met to apply logic as explained in examples.

Epoch time should be in seconds & milliseconds should not be present at the end.
This means that maximum length of epoch time can be 10 digits.


Example 1: SELECT CAST((date '1970-01-01' +1541001734/86400) AS TIMESTAMP(0)) 
           + 
           (1541001734 MOD 86400) * INTERVAL '00:00:01' HOUR TO SECOND;

Output: 2018-10-31 16:02:14



Example 2: SELECT CAST((date '1970-01-01' + epcho_time_column/86400) AS TIMESTAMP(0)) 
           + 
           (epcho_time_column MOD 86400) * INTERVAL '00:00:01' HOUR TO SECOND
           FROM sometable;

Output: 2018-10-31 16:02:14

Result Explanation:

There are 2 part of the query in the above examples and can be differentiate by plus(+) sign.
First part is used to finds the date of epoch time by adding '1541001734/86400' number of days to 1970-01-01 and converted to timestamp.
86400 denotes seconds in 1 day, therefore '1541001734/86400' will return total number of days to be added to determine the date of epoch.
Second part of query is used to determine the time of epoch by multiply '1541001734 MOD 86400' time part with the interval of 1 second.
'MOD' is used to determine remainder in Teradata, therefore 1541001734 MOD 86400 will return total time after removing total complete days.
Finally 2 parts are added to make the complete timestamp.

➠ Convert Epoch Time to TIMESTAMP(6): Below conditions should met to apply logic as explained in examples.

Epoch time should be in seconds with milliseconds appended at the end.
This means that length of epoch time should be 13 digits and last 3 digits of epoch time will represent milliseconds.


Example 1: SELECT CAST((date '1970-01-01' + CAST(1541001734342/1000 AS INTEGER)/86400) AS TIMESTAMP(6)) 
           + 
           (CAST(1541001734342/1000 AS INTEGER) MOD 86400) * INTERVAL '00:00:01' HOUR TO SECOND 
           + 
           (1541001734342 MOD 1000) * INTERVAL '00:00:00.001' HOUR TO SECOND;

Output: 2018-10-31 16:02:14.342000



Example 2: SELECT CAST((date '1970-01-01' + CAST(epcho_time_column_with_milliseconds/1000 AS INTEGER)/86400) AS TIMESTAMP(6)) 
           + 
           (CAST(epcho_time_column_with_milliseconds/1000 AS INTEGER) MOD 86400) * INTERVAL '00:00:01' HOUR TO SECOND 
           + 
           (epcho_time_column_with_milliseconds MOD 1000) * INTERVAL '00:00:00.001' HOUR TO SECOND
           FROM sometable;

Output: 2018-10-31 16:02:14.342000

Result Explanation:

There are 3 part of the query in the above examples and can be differentiate by plus(+) sign.
First part is used to finds the date of epoch time by adding 'CAST(1541001734342/1000 AS INTEGER)/86400)' number of days to 1970-01-01 and converted to timestamp.
86400 denotes seconds in 1 day and last 3 digit of epoch time is millisecond, therefore 'CAST(1541001734342/1000 AS INTEGER)/86400)' will return total number of days to be added to determine the date of epoch.
Second part of query is used to determine the time of epoch by multiply 'CAST(1541001734342/1000 AS INTEGER) MOD 86400' time part with the interval of 1 second.
'MOD' is used to determine remainder in Teradata and last 3 digit of epoch time is millisecond, therefore CAST(1541001734342/1000 AS INTEGER) MOD 86400 will return total time after removing total complete days.
Third part of query is used to determine milliseconds of timestamp by multiply '(1541001734342 MOD 1000)' millisecond part of epoch with the interval of 0.001 seconds to make return value as time.
(1541001734342 MOD 1000) will return last 3 digits of epoch time which is actually milliseconds
Finally 3 parts are added to make the complete timestamp.