postgresql - 为什么 postgresql 用时间戳来愚弄我？

Question

我在一个系统上工作，我试图尽量减少时区可能引入的错误，所以我在 (postgresql) 数据库上使用时间戳字段，但我在创建记录时使用 UNIX 纪元的秒数​​，并且当我读取记录时(使用EXTRACT(EPOCH)读取，插入时使用TO_TIMESTAMP())。

这样我就可以摆脱时区问题。我还是这样。在深入挖掘之后，我发现 postgresql 在从表中读取值时变得有点困惑。考虑这个查询:

select current_timestamp, extract(EPOCH from current_timestamp), id, last_gps_read, 
    extract(EPOCH from current_timestamp) - extract(EPOCH 
from last_gps_read) from sometable where id=1

哪个给

              now              |    date_part     | id |      last_gps_read       |    ?column?     
-------------------------------+------------------+----+--------------------------+-----------------
 2018-03-21 23:26:07.263931-06 | 1521696367.26393 |  1 | 2018-03-21 23:26:00.5273 | 21606.736631155

请注意日期彼此非常接近(仅相差大约 7 秒？)。

因此，当我使用 extract(EPOCH from x) 技巧时，我认为差异会给我大约 7 秒....相反，我得到 ~21607(我在GMT-6，这解释了为什么相差大约 21600 秒)。这绝对不是很酷，因为这意味着当报告两个日期自 UNIX 纪元以来的秒数时，它在报告来自表的数据的秒数时以某种方式引入了时区(我刚刚检查过，自 UNIX 纪元以来的秒数 current_timestamp 是正确的).

这样做的理由是什么？因为对我来说这听起来很像一个错误。

PS 我可以考虑更改 DB 上的字段类型以使用整数来保存自 UNIX 纪元以来的实际秒数，因此我肯定会摆脱它，但它听起来有点矫枉过正。

Answer 1

区别在于 EXTRACT(EPOCH FROM ...) 总是计算相对于 GMT 时间的时间(著名的 01.01.1970 00:00:00+00 )。如果您将您的当前时间转换为 GMT，您将获得时间戳 2018-03-21 17:26:00.5273+00与 GPS 时间戳相差 6 小时 7 秒左右，或大约 6 * 3600 + 7 = 21607。

您可以将您的 GPS 时间转换为本地时间，或从时间戳差异中减去 21600 以获得所需的结果。