我应该在 PostgreSQL 数据库中选择哪种时间戳类型？

Question

我想定义在多时区项目的上下文中在 Postgres 数据库中存储时间戳的最佳实践。

我可以

1. 选择 TIMESTAMP WITHOUT TIME ZONE 并记住在插入该字段时使用的时区
2. 选择 TIMESTAMP WITHOUT TIME ZONE 并添加另一个包含时区名称的字段在插入时使用的
3. 选择TIMESTAMP WITH TIME ZONE并相应地插入时间戳

我对选项3（带时区的时间戳）略有偏好，但希望对此事有一个有根据的意见。

Answer 1

首先，PostgreSQL 的时间处理和算术非常棒，选项 3 在一般情况下也很好。然而，它是时间和时区的不完整视图，可以补充：

1. 将用户时区的名称存储为用户首选项（例如 America/Los_Angeles，而不是 -0700代码>）。
2. 将用户事件/时间数据提交到其参考系本地（很可能是相对于 UTC 的偏移量，例如 -0700）。
3. 在应用程序中，将时间转换为 UTC 并使用 TIMESTAMP WITH TIME ZONE 列进行存储。
4. 返回用户所在时区的本地时间请求（即从 UTC 转换为 America/Los_Angeles）。
5. 将数据库的时区设置为UTC。

此选项并不总是有效，因为获取用户的时区可能很困难，因此对冲建议对轻量级应用程序使用TIMESTAMP WITH TIME ZONE。也就是说，让我更详细地解释一下选项 4 的一些背景知识。

与选项 3 一样，使用WITH TIME ZONE 的原因是因为某件事发生的时间是一个绝对时间点。 WITHOUT TIME ZONE 产生相对时区。永远、永远、永远不要混合使用绝对时间戳和相对时间戳。

从编程和一致性的角度来看，确保所有计算均使用 UTC 作为时区进行。这不是 PostgreSQL 的要求，但它在与其他编程语言或环境集成时很有帮助。在列上设置 CHECK 以确保写入时间戳列的时区偏移量为 0 是一种防御位置，可以防止几类错误（例如脚本将数据转储到文件，并使用其他方法对时间数据进行词法排序）。同样，PostgreSQL 不需要它来正确执行日期计算或在时区之间进行转换（即 PostgreSQL 非常擅长在任意两个任意时区之间转换时间）。确保进入数据库的数据以零偏移量存储：

CREATE TABLE my_tbl (
  my_timestamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  CHECK(EXTRACT(TIMEZONE FROM my_timestamp) = '0')
);
test=> SET timezone = 'America/Los_Angeles';
SET
test=> INSERT INTO my_tbl (my_timestamp) VALUES (NOW());
ERROR:  new row for relation "my_tbl" violates check constraint "my_tbl_my_timestamp_check"
test=> SET timezone = 'UTC';
SET
test=> INSERT INTO my_tbl (my_timestamp) VALUES (NOW());
INSERT 0 1

它不是 100% 完美，但它提供了足够强大的防偷拍措施，确保数据已转换为 UTC。关于如何做到这一点有很多意见，但根据我的经验，这似乎是实践中最好的。

对数据库时区处理的批评在很大程度上是有道理的（有很多数据库在处理这个问题上非常无能），但是 PostgreSQL 对时间戳和时区的处理非常棒（尽管到处都有一些“功能”）。例如，其中一项功能：

-- Make sure we're all working off of the same local time zone
test=> SET timezone = 'America/Los_Angeles';
SET
test=> SELECT NOW();
              now              
-------------------------------
 2011-05-27 15:47:58.138995-07
(1 row)

test=> SELECT NOW() AT TIME ZONE 'UTC';
          timezone          
----------------------------
 2011-05-27 22:48:02.235541
(1 row)

请注意，AT TIME ZONE 'UTC' 会删除时区信息，并使用目标的参考系创建相对的 TIMESTAMP NOT TIME ZONE (>UTC）。

从不完整的 TIMESTAMP WITHOUT TIME ZONE 转换为 TIMESTAMP WITH TIME ZONE 时，缺失的时区将从您的连接继承：

test=> SET timezone = 'America/Los_Angeles';
SET
test=> SELECT EXTRACT(TIMEZONE_HOUR FROM NOW());
 date_part 
-----------
        -7
(1 row)
test=> SELECT EXTRACT(TIMEZONE_HOUR FROM TIMESTAMP WITH TIME ZONE '2011-05-27 22:48:02.235541');
 date_part 
-----------
        -7
(1 row)

-- Now change to UTC    
test=> SET timezone = 'UTC';
SET
-- Create an absolute time with timezone offset:
test=> SELECT NOW();
              now              
-------------------------------
 2011-05-27 22:48:40.540119+00
(1 row)

-- Creates a relative time in a given frame of reference (i.e. no offset)
test=> SELECT NOW() AT TIME ZONE 'UTC';
          timezone          
----------------------------
 2011-05-27 22:48:49.444446
(1 row)

test=> SELECT EXTRACT(TIMEZONE_HOUR FROM NOW());
 date_part 
-----------
         0
(1 row)

test=> SELECT EXTRACT(TIMEZONE_HOUR FROM TIMESTAMP WITH TIME ZONE '2011-05-27 22:48:02.235541');
 date_part 
-----------
         0
(1 row)

底线：

• 将用户的时区存储为命名标签（例如 America/Los_Angeles），而不是 UTC 偏移量（例如 -0700），
• 所有内容均使用 UTC，除非有令人信服的理由来存储非零偏移量
• 将所有非零 UTC 时间视为输入错误
• 从不混合和匹配相对和绝对时间戳
• 如果可能的话还使用 UTC 作为数据库中的时区

随机编程语言注释：Python 的 datetime 数据类型非常擅长维护之间的区别绝对时间与相对时间（尽管一开始令人沮丧，直到您补充它与像 PyTZ 这样的库）。

---

编辑

让我再解释一下相对与绝对之间的区别。

绝对时间用于记录事件。示例：“用户 123 已登录”或“毕业典礼于 2011 年 5 月 28 日下午 2 点（太平洋标准时间）开始”。无论您所在的当地时区如何，如果您可以传送到事件发生的地点，您就可以目睹事件的发生。数据库中的大多数时间数据都是绝对的（因此应该是TIMESTAMP WITH TIME ZONE，理想情况下带有+0偏移量和表示管理特定时区的规则的文本标签 - 而不是偏移量）。

相对事件是从尚未确定的时区的角度记录或安排某事的时间。例如：“我们的公司早上 8 点开门，晚上 9 点关门”、“我们每周一早上 7 点召开每周早餐会”或“每个万圣节晚上 8 点”。一般来说，相对时间用于事件的模板或工厂，而绝对时间用于几乎所有其他内容。有一个罕见的例外值得指出，它应该说明相对时间的价值。对于距离足够远的未来事件，某些事情可能发生的绝对时间可能存在不确定性，请使用相对时间戳。这是一个现实世界的示例：

假设是 2004 年，您需要安排 2008 年 10 月 31 日下午 1 点在美国西海岸（即 America/Los_Angeles/PST8PDT< /代码>）。如果您使用 '2008-10-31 21:00:00.000000+00'::TIMESTAMP WITH TIME ZONE 使用绝对时间存储该信息，则递送会在下午 2 点显示，因为美国政府通过了<一href="https://en.wikipedia.org/wiki/Energy_Policy_Act_of_2005#Change_to_daylight_ saving_time">2005 年能源政策法案更改了管理夏令时的规则。在 2004 年计划交付时，日期 10-31-2008 应该是太平洋标准时间 (+8000)，但从 2005 年以上时区数据库开始认识到10-31-2008 应该是太平洋夏令时 (+0700)。存储与时区的相对时间戳将导致正确的交付时间表，因为相对时间戳不受国会不知情的篡改的影响。使用相对时间与绝对时间来安排事情之间的界限是一条模糊线，但我的经验法则是，未来超过 3-6 个月的任何事情的安排都应该使用相对时间戳（已安排 = 绝对与计划 =相对的 ？？？）。

另一种/最后一种相对时间是INTERVAL。示例：“会话将在用户登录后 20 分钟超时”。 INTERVAL 可以与绝对时间戳 (TIMESTAMP WITH TIME ZONE) 或相对时间戳 (TIMESTAMP NOT TIME ZONE) 一起正确使用。同样正确的是，“用户会话在成功登录后 20 分钟到期 (login_utc + session_duration)”或“我们的早餐会议只能持续 60 分钟 (recurring_start_time + meet_length)”。

最后一点混乱：DATE、TIME、TIME NOT TIME ZONE 和 TIME with TIME ZONE 都是相对数据类型。例如：'2011-05-28'::DATE 表示相对日期，因为您没有可用于识别午夜的时区信息。同样，'23:23:59'::TIME 是相对的，因为您不知道时区或时间表示的 DATE。即使使用 '23:59:59-07'::TIME WITH TIME ZONE，您也不知道 DATE 是什么。最后，带有时区的 DATE 实际上不是 DATE，它是一个 TIMESTAMP WITH TIME ZONE：

test=> SET timezone = 'America/Los_Angeles';
SET
test=> SELECT '2011-05-11'::DATE AT TIME ZONE 'UTC';
      timezone       
---------------------
 2011-05-11 07:00:00
(1 row)

test=> SET timezone = 'UTC';
SET
test=> SELECT '2011-05-11'::DATE AT TIME ZONE 'UTC';
      timezone       
---------------------
 2011-05-11 00:00:00
(1 row)

将日期和时区放入数据库是个好东​​西，但是很容易得到稍微不正确的结果。只需最少的额外努力即可正确、完整地存储时间信息，但这并不意味着总是需要额外的努力。

Answer 2

肖恩的回答过于复杂且具有误导性。

事实上，“WITH TIME ZONE”和“WITHOUT TIME ZONE”都将值存储为类似 UNIX 的绝对 UTC 时间戳。区别在于时间戳的显示方式。当“WITH time zone”时，显示的值是转换为用户所在区域的 UTC 存储值。当“WITHOUT time zone”时，UTC 存储的值会被扭曲，以便无论用户设置哪个区域都显示相同的钟面。

“WITHOUT time zone”唯一可用的情况是当钟面值适用时例如，当时间戳表明投票站可能关闭时（即，无论某人所在的时区如何，投票站都会在 20:00 关闭）。

除非有非常具体的规定，否则始终使用“带时区”。原因不。

Answer 3

我更喜欢选项 3，因为 Postgres 可以为您完成大量重新计算相对于时区的时间戳的工作，而对于其他两个，您必须自己完成。存储带有时区的时间戳的额外存储开销实际上可以忽略不计，除非您正在谈论数百万条记录，在这种情况下您可能已经有相当大的存储需求。