SQL PostgreSQL：如何从 Unix 纪元转换为日期？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16609722/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL: how to convert from Unix epoch to date?
提问by sid_com
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 ) );
回答by Tomas Greif
/* 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;
回答by Sinu
select to_timestamp(cast(epoch_ms/1000 as bigint))::date
worked for me
对我来说有效
回答by yodi
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:
上述解决方案不适用于 PostgreSQL 的最新版本。我发现这种方法可以转换存储在数字和 int 列类型中的纪元时间在 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
有关更详细的解释，您可以在这里查看https://www.yodiw.com/convert-epoch-time-to-timestamp-in-postgresql/#more-214
