latest

[Amazon Redshift] Converting unix epoch time into timestamps

Normally in PostgreSQL you could convert to_timestamp(myunixtime) however according to the Redshift documentation this particular function is not available for Amazon Redshift.

As an alternative if you would like to convert a unix timestamp (seconds from epoch) into a more useable timestamp you can use the following code:

SELECT 
    TIMESTAMP 'epoch' + myunixtime * INTERVAL '1 Second ' as mytimestamp, 
    timestamptz 'epoch' + myunixtime * interval '1 second' as anotherform
FROM 
    example_table

For example this could be used to convert the types of unix timestamps that accompany google play transaction data into other forms of dates.

Credit where credit is due:

Discovered this trick via a blog of someone who seems to work with Redshift regularly, the blog is in Japanese but with a little help from Google Translate there was some helpful information.

Author image
Washington, USA