Convert Unix epoch time into Redshift timestamps

Many relational databases such as Snowflake, PostgreSQL support functions to convert Unix epoch time into timestamp value. But, Redshift does not support these unix to timestamp functions. You will have to use an alternate approach to convert epoch format. In this article, we will check how to convert unix epoch time into Redshift timestamps with some examples.

Convert Unix epoch time into Redshift timestamps

The Unix epoch (or Unix time or POSIX time or Unix timestamp) is the number of seconds that have elapsed since January 1, 1970.

Amazon Redshift does not provide specific functions to convert epoch to timestamp. But, as a workaround, you can just add the number of seconds to epoch and return a timestamp.

Methods to Convert Unix epoch to Timestamp in Redshift

There are many methods that you can use to convert the Unix EPOCH format into Redshift timestamp.

Following are the some of commonly used methods.

Convert epoch using interval
Convert epoch using date_add function
Create User Defined Function to Convert epoch to Timestamp

Converting Unix epoch to Redshift Timestamp using interval

The following example demonstrates use of Redshift interval type to convert epoch format.

SELECT timestamp 'epoch' + cast(timestamp_col AS bigint)/1000 * interval '1 second' AS epoch_to_timestamp
FROM (SELECT 1464800406459 AS timestamp_col) AS a;

 epoch_to_timestamp
---------------------
 2016-06-01 17:00:06
(1 row)

This method is one of the fastest methods compared to any other method to convert epoch to timestamp. Use of user defined function is slowest of all methods.

You can read more about Redshift interval data types in my other article, Redshift Interval Data Types and Conversion Examples

Convert Unix epoch using date_add function

You can use date_add function to convert Unix epoch to timestamp. This is simple and one of the easiest method.

Following example uses date_add function to convert unix epoch to timestamp values.

SELECT date_add('ms',timestamp_col,'1970-01-01')
from ( select 1464800406459 as timestamp_col) as a;

        date_add
-------------------------
 2016-06-01 17:00:06.459
(1 row)

Note that, epoch type conversion using date_add is slow compared to other methods.

Create User Defined Function to Convert epoch to Timestamp

Another simple solution is to create Redshift user defined function to convert unix epoch to timestamp. You can create Python UDF.

For example,

Consider following from_unixtime UDF in Redshift example

CREATE OR REPLACE FUNCTION from_unixtime(epoch BIGINT)
  RETURNS TIMESTAMP  AS
'import datetime

return datetime.datetime.fromtimestamp(epoch)
'
LANGUAGE plpythonu IMMUTABLE;