PostgreSQL: 如何从 Unixepoch 转换到日期？

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 https://www.yodiw.com/convert-epoch-time-to-timestamp-in-postgresql/#more-214

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)