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