postgresql - 对 TIMESTAMPTZ 内部转换感到困惑

Question

我花了很多时间阅读有关在 Postgres 中处理时间戳的“最佳实践”，并且得到了很多相互矛盾的答案。使用 TIMESTAMPTZ 数据类型时，我假设 NOW() 和 NOW() 在时区 'utc' 会产生相同的结果正在插入数据库的数据。许多在线评论表明，时区实际上并未在内部存储，而是转换为 UTC。

为什么当我运行以下命令时没有得到重复的结果？

CREATE TABLE testtime(
  mytime TIMESTAMPTZ,
  descr VARCHAR
);

INSERT INTO testtime VALUES
  (NOW(), 'Now at NZST'),
  (NOW() AT TIME ZONE 'utc', 'Now at UTC');
SELECT *
FROM testtime;

结果:

2016-02-17 02:08:30.845071  Now at NZST
2016-02-16 13:08:30.845071  Now at UTC

Answer 1

原因是从 timestamp 到 timestamptz 的隐式类型转换。

• now() 返回数据类型 timestamptz。
• now() AT TIME ZONE 'UTC' 返回数据类型 timestamp。
• (now() AT TIME ZONE 'UTC')::timestamptz 将 timestamp 转换为 timestamptz，假设您当前的时间进程中的区域。这就是引入差异的地方。

这就是当您将一个 timestamp 值INSERT 到 timestamptz 列时发生的情况。 Postgres 必须采用一些 时区。您似乎已经预料到会采用 UTC。不过，更合理的默认设置是当前时区设置。如果您在 session 中设置 UTC，您会得到预期的行为。

演示:

我的时区“欧洲/维也纳”，目前比 UTC 早 1 小时(冬季):

SET timezone = 'Europe/Vienna';
SELECT now() AS now1
     , now() AT TIME ZONE 'UTC' AS now2
     , (now() AT TIME ZONE 'UTC')::timestamptz AS now3;

              now1             |          now2              |           now3-------------------------------+----------------------------+------------------------------- 2016-02-16 14:30:07.243082+01 | 2016-02-16 13:30:07.243082 | 2016-02-16 13:30:07.243082+01

The same with 'UTC' as time zone setting for the session:

SET timezone = 'UTC';
SELECT now() AS now1
     , now() AT TIME ZONE 'UTC' AS now2
     , (now() AT TIME ZONE 'UTC')::timestamptz AS now3;

              now1             |          now2              |           now3-------------------------------+----------------------------+------------------------------- 2016-02-16 13:30:58.739772+00 | 2016-02-16 13:30:58.739772 | 2016-02-16 13:30:58.739772+00

请注意前两列如何具有相同的值 - 即使 now1 中的文本表示看起来不同，因为它已根据 session 的时区进行了调整，值是一样的。

第三列有一个不同的值，因为为类型转换假定了不同的时区。

基础知识:

• Ignoring timezones altogether in Rails and PostgreSQL