Can't extract date from milliseconds epoch postgresql

Question

I'm querying the database (RedShift) and I have a piece of information stored in epoch MS format. Envision a table along the lines of:

Purchase, date
1, 1620140227019
2, 1620140227045
3, 1620140226573

I need to convert the timestamp to a readable date but I can't make it work with to_timestamp() or extract(). The problem is first with the size of the value (13 digits are not supported). The closest solution I have is

select  to_timestamp(1620140226573/1000, 'SS')

But the result is 0051-05-04 14:57:06. In other words month, date and seconds are correct but the year is wrong.

user_14871018 · Answer

You can run this query

select to_timestamp(round(1620140227254/1000))

user_13411032 · Answer

The solution was in the documentation: https://docs.aws.amazon.com/redshift/latest/dg/r_Dateparts_for_datetime_functions.html

SELECT timestamp with time zone 'epoch' + 1620140227019/1000 * interval '1 second' AS converted_timestamp

or

select '1970-01-01'::date + 1620140227019/1000 * interval '1 second'

Can't extract date from milliseconds epoch postgresql

2 Answers