postgresql - PostgreSQL 时间戳无法正确转换为 Unix 时间戳

Question

我刚开始使用 PostgreSQL。由于我们使用了很多 Unixtimestamp，我认为最好只尝试一个小例子。因此，我使用 phpPgAdmin制作了一个test带有字段ID和timestamp类型字段的表。timestamp without time zone有有效数据。

现在我只想使用 Unixtimestamp 检索数据。我遇到了非常奇怪的行为。

我做了这个小测试，它通过将第一个最佳时间戳转换为 unixtimestamp 再转换回时间戳来选择数据：

这将返回第一个条目的原始和 unix 值中的时间戳

SELECT timestamp, extract(epoch FROM timestamp) 
FROM test 
WHERE id IN (SELECT id FROM test LIMIT 1);

好的，这按预期工作。
这只会转换，因此它应该返回一个条目

SELECT timestamp, extract(epoch FROM timestamp) 
FROM test 
WHERE timestamp IN (SELECT TO_TIMESTAMP(extract(epoch FROM timestamp)) 
                    FROM test LIMIT 1); 

什么都不退！

我只是无法理解这种行为。如果我将时间戳转换为其他内容，然后将其转换回来，它应该是相同的时间戳，对吗？

我只是想要一种将时间戳转换为 unixtime 并返回而不会丢失信息的方法。

请注意：这只是一个测试！我不想使用此代码。我只想检查 SQL 代码是否按预期运行！此外，我对 unixtimestamps 无能为力！我只是想要一种正确投射它们的方法！

标签： postgresqlunix-timestamp

Answer 1

不使用没有时区的时间戳类型与使用的区别：

 select '09/12/2020 11:16 PDT'::timestamp, to_timestamp(extract(epoch from '09/12/2020 11:16 PDT'::timestamp));
      timestamp      |      to_timestamp       
---------------------+-------------------------
 09/12/2020 11:16:00 | 09/12/2020 04:16:00 PDT

select '09/12/2020 11:16 PDT'::timestamptz, to_timestamp(extract(epoch from '09/12/2020 11:16 PDT'::timestamptz));
       timestamptz       |      to_timestamp       
-------------------------+-------------------------
 09/12/2020 11:16:00 PDT | 09/12/2020 11:16:00 PDT

更新。有关详细信息，请参阅。重要的部分是：

在已确定为无时区时间戳的文字中，PostgreSQL 将默默地忽略任何时区指示。也就是说，结果值源自输入值中的日期/时间字段，并且未针对时区进行调整。

对于带时区的时间戳，内部存储的值始终采用 UTC（通用协调时间，传统上称为格林威治标准时间，GMT）。使用该时区的适当偏移量将具有指定明确时区的输入值转换为 UTC。如果输入字符串中没有说明时区，则假定它位于系统的 TimeZone 参数指示的时区中，并使用时区的偏移量转换为 UTC。

当输出带有时区值的时间戳时，它总是从 UTC 转换为当前时区，并显示为该时区的本地时间。要查看另一个时区的时间，要么更改时区，要么使用 AT TIME ZONE 构造（参见第 9.9.3 节）。

这表示：

select '09/12/2020 11:16 PDT'::timestamp;
      timestamp      
---------------------
 09/12/2020 11:16:00


select '09/12/2020 11:16'::timestamp at time zone 'UTC';
        timezone         
-------------------------
 09/12/2020 04:16:00 PDT

 select '09/12/2020 11:16 PDT'::timestamp at time zone 'UTC';
        timezone         
-------------------------
 09/12/2020 04:16:00 PDT

select '09/12/2020 11:16 PDT'::timestamptz;
       timestamptz       
-------------------------
 09/12/2020 11:16:00 PDT


select '09/12/2020 11:16 PDT '::timestamptz at time zone 'UTC';
      timezone       
---------------------
 09/12/2020 18:16:00

所以在第一种情况下，时区被忽略，时间被认为是09/12/2020 11:16 . 在第二种和第三种情况下，时间戳被视为在UTC并且显示的值被旋转到我的时区PDT。在第三种情况下，我正在使用timestamptz时间戳正确地标记了时区。这意味着当我要求显示它时，UTC它会做正确的事情。