使用to_timestamp函数，然后将时间戳转换为date

select to_timestamp(epoch_column)::date;

您可以使用更标准的cast，而不是::

select cast(to_timestamp(epoch_column) as date);

更多详情：

/* 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;

在您的情况下：

select to_timestamp(epoch_ms / 1000)::date;

PostgreSQL Docs

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

为我工作

在Postgres 10上：
SELECT to_timestamp(CAST(epoch_ms as bigint)/1000)

上面的解决方案不适用于PostgreSQL上的最新版本。我发现这种方法来转换以数字和整数列类型存储的纪元时间是在PostgreSQL 13：

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

有关详细说明，请访问https://www.yodiw.com/convert-epoch-time-to-timestamp-in-postgresql/#more-214

这对我很有效：

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;

GNU date时代以来的秒数：

$ date +%s.%N
1627059870.945134901

这适用于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)