PostgreSQL: how to convert from Unix epoch to date?

Question

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 ) ); 

Answer 1

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