parsear - timestamp parse postgresql

Postgres: ¿cómo convertir de la época de Unix a la fecha?

Question

La declaración me da la fecha y la hora.

¿Cómo podría modificar la declaración para que devuelva solo la fecha?

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

Answer 1

/* Current time */
select now();
/* 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;
/* For column epoch_ms */
select to_timestamp(extract(epoch epoch_ms))::date;

PostgreSQL Docs