Convert between epoch and datetime in Redshift
Redshift and PostgreSQL do not support native functions like MySQL’s
FROM_UNIXTIME() to convert Epoch time to a human-readable datetime.
So we need to work around it with an inelegant solution that involves adding the number of seconds since 01/01/1970 to the Epoch timestamp:
-- Convert Epoch time to datetime SELECT DATEADD(second, 1624654823, '1970-01-01') AS epoch_datetime
Conversely, to get the Epoch time from a proper datetime, we can use the following solution:
-- Convert datetime to Epoch timestamp SELECT DATE_PART(epoch, '2021-06-25 21:00:23') as datetime_epoch