PostgreSQL: how to convert from Unix epoch to date?

The statement gives me the date and time.

How could I modify the statement so that it returns only the date (and not the time)?

SELECT to_timestamp( TRUNC( CAST( epoch_ms AS bigint ) / 1000 ) ); 

6 Answers

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; 

PostgreSQL Docs

select to_timestamp(cast(epoch_ms/1000 as bigint))::date 

worked for me

The solution above not working for the latest version on PostgreSQL. I found this way to convert epoch time being stored in number and int column type is on PostgreSQL 13:

SELECT TIMESTAMP 'epoch' + (<table>.field::int) * INTERVAL '1 second' as started_on from <table>; 

For more detail explanation, you can see here

On Postgres 10:

SELECT to_timestamp(CAST(epoch_ms as bigint)/1000)

This works for me fine:

SELECT t.*, to_timestamp(cast(t.prev_fire_time/1000 as bigint)) as prev_fire_time, to_timestamp(cast(t.next_fire_time/1000 as bigint)) as next_fire_time, to_timestamp(cast(t.start_time/1000 as bigint)) as start_time FROM public.qrtz_triggers t; 

Seconds since epoch with GNU date:

$ date +%s.%N 1627059870.945134901 

This works with PostgreSQL 11:

# select to_timestamp (1627059870.945134901); to_timestamp ------------------------------- 2021-07-23 19:04:30.945135+02 (1 row) # select to_timestamp (1627059870.945134901)::date; to_timestamp -------------- 2021-07-23 (1 row)