PostgreSQL: how to convert from Unix epoch to date?
You use to_timestamp function and then cast the timestamp to date
select to_timestamp(epoch_column)::date;More details:
/* Current time */ select now(); -- returns timestamp/* Epoch from current time; Epoch is number of seconds since 1970-01-01 00:00:00+00 */ select extract(epoch from now()); /* Get back time from epoch */ -- Option 1 - use to_timestamp function select to_timestamp( extract(epoch from now())); -- Option 2 - add seconds to 'epoch' select timestamp with time zone 'epoch' + extract(epoch from now()) * interval '1 second';/* Cast timestamp to date */ -- Based on Option 1 select to_timestamp(extract(epoch from now()))::date; -- Based on Option 2 select (timestamp with time zone 'epoch' + extract(epoch from now()) * interval '1 second')::date; In your case:
select to_timestamp(epoch_ms / 1000)::date;
select to_timestamp(cast(epoch_ms/1000 as bigint))::dateworked for me
On Postgres 10:
SELECT to_timestamp(CAST(epoch_ms as bigint)/1000)