Integer (with Unix epoch time) convert to timestamp?

Database
Junior Supporter

Integer (with Unix epoch time) convert to timestamp?

This gets the date, how can I get the full timestamp?

SELECT (CAST('1970/01/01' AS DATE FORMAT 'yyyy/mm/dd')) + CAST(my_time/86400 AS INTEGER) AS CONVERTED_TIME
FROM myDatabase.myTable

I have a table with a column that is an integer and has Unix epoch time data.

Here is a sample of the data:
1298260810
1298202056
1298247682

Using this converter:
http://www.epochconverter.com/

I get the following:
Epoch Timestamps: 1298260810
GMT: Mon, 21 Feb 2011 04:00:10 GMT
Your timezone: Sunday, February 20, 2011 9:00:10 PM
2 REPLIES
Junior Contributor

Re: Integer (with Unix epoch time) convert to timestamp?

See the second last comment at:
http://developer.teradata.com/blog/robg/2011/02/using-interval-to-add-or-subtract-fractional-seconds-to-or-from-timestamp-values

Dieter
Junior Supporter

Re: Integer (with Unix epoch time) convert to timestamp?

Thanks! This what I ended up with:

SELECT CAST(DATE '1970-01-01' + (my_time / 86400) AS TIMESTAMP(6))
+ ((my_time MOD 86400) * INTERVAL '00:00:01.000000' HOUR TO SECOND) AS myTIMESTAMP
FROM myDatabase.myTable