sql - 如何从 postgres 获取时间戳作为 Unix 纪元？

Question

我想从 Postgres 获取数据。有一个类型为 timestamp without timezone 的列。简单的 SELECT 返回我格式化的日期，但我只想要以毫秒为单位的时间戳。我如何在 SQL 中做到这一点？

Answer 1

select extract(epoch from my_timestamp)

这将以秒为单位返回时间。但是，它是一个十进制数，小数点后有秒的分数。在下面阅读有关精度的更多信息。

对于毫秒，使用:

select extract(epoch from my_timestamp)*1000

测试:

select my_date, 
extract(epoch from my_date)*interval'1s'+'epoch'::timestamp at time zone 'GMT'

精度注意事项

extract 返回一个“ double ”数字，在 Postgres 中适合 15 位数字。这意味着大约 2016 年(我写这篇文章时)，实际精度为 10 微秒。请注意它是如何四舍五入最后一位数字的:

select extract('epoch' from '2016-09-20T11:22:33.456789Z'::timestamp);
    date_part     
------------------
 1474370553.45679
(1 row)

对于 future 270 年这样的日期，此数据类型将只能表示 100 微秒的精度。从 2016 年的角度来看，这似乎是一个不错的精度，我想在我们到那一年之前情况可能会有所改变。

select extract('epoch' from '2290-09-20T11:22:33.456789Z'::timestamp);
    date_part     
------------------
 10120936953.4568
(1 row)

无论哪种方式，它都会在一段时间内继续以 毫秒 精度正常工作。