Converting Unix/EPOCH time to PST

General

Converting Unix/EPOCH time to PST

I have a column in my teradata table which stores timestamp in UNIX/EPOCH form. I want to run a query and get the corresponding Date in PST and number of records against that date. I got the following query to convert the EPOCH time to Date, but not able to figure out how to get the corresponding PST date.

SELECT CAST (CAST(DATE '1970-01-01' + (CAST (TIME_CREATED AS INTEGER)/86400 ) AS TIMESTAMP(0) ) + ((CAST (TIME_CREATED AS INTEGER) MOD 86400 ) * INTERVAL '00:00:01' HOUR TO SECOND) AS DATE), COUNT(ID) FROM MY_TABLE GROUP BY 1

1 REPLY
Teradata Employee

Re: Converting Unix/EPOCH time to PST

You said that you wanted to get the "PST date", but time zones are not associated with DATE values. In other words, the Teradata Database does not offer a DATE WITH TIME ZONE data type.

Your outermost CAST must cast to TIMESTAMP rather than cast to DATE. With the CAST to TIMESTAMP, then you can use the AT 'America Pacific' clause to convert the TIMESTAMP to your desired time zone.

Here is the modified query:

SELECT CAST (CAST(DATE '1970-01-01' + (CAST (TIME_CREATED AS INTEGER)/86400 ) AS TIMESTAMP(0) ) + ((CAST (TIME_CREATED AS INTEGER) MOD 86400 ) * INTERVAL '00:00:01' HOUR TO SECOND) AS TIMESTAMP) AT 'America Pacific', COUNT(ID) FROM MY_TABLE GROUP BY 1