[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:

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

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