Convert bigint data type to timestamp (and subsequently to date) in redshift

Question

I need to convert the value stored in a bigint column to a date field. The first step of the conversion involves converting it to timestamp, and subsequently use the TRUNC method to convert this column to a date value. However, my query is failing while converting the bigint value to timestamp. The error that I'm getting is:-

Amazon Invalid operation: cannot cast type bigint to timestamp without time zone;

The query I'm trying for now is something like this:-

select ts::timestamp from events limit 1;

Answer 1

I was able to avoid the time zone error by using the method described in this thread: https://stackoverflow.com/a/36399361

My dates are based on epochs, and I was able to do the following:

SELECT 
  (TIMESTAMP 'epoch' + contract_start_date * INTERVAL '1 Second ')
FROM
  table_name

Answer 2

SELECT TIMESTAMP 'epoch' + {column of bigint}/1000 * INTERVAL '1 second' as adate FROM tbl