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

Question

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

我可以

选择 TIMESTAMP WITHOUT TIME ZONE并记住在此字段插入时使用的时区

选择 TIMESTAMP WITHOUT TIME ZONE并添加另一个字段，该字段将包含插入时使用的时区名称

选择 TIMESTAMP WITH TIME ZONE并相应地插入时间戳

我对选项 3(带时区的时间戳)略有偏好，但想对此事发表有根据的意见。

Answer 1

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

将用户的时区名称存储为用户首选项(例如 America/Los_Angeles ，而不是 -0700 )。

将用户事件/时间数据提交到他们的引用框架本地(很可能是 UTC 的偏移量，例如 -0700 )。

在应用程序中，将时间转换为 UTC并使用 TIMESTAMP WITH TIME ZONE 存储列。

返回用户时区的本地时间请求(即从 UTC 转换为 America/Los_Angeles )。

设置数据库的 timezone至 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 WITHOUT 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如 timezone如果可能，在数据库中

随机编程语言笔记:Python 的 datetime 数据类型非常擅长保持绝对时间和相对时间之间的区别(尽管起初令人沮丧，直到您用像 PyTZ 这样的库来补充它)。

编辑

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

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

相对事件是从尚未确定的时区的角度记录或安排某事的时间。例如:“我们公司的大门在早上 8 点开门，晚上 9 点关门”、“让我们每周一早上 7 点见面，参加每周早餐会”或“每个万圣节晚上 8 点”。一般来说，相对时间用于事件的模板或工厂，而绝对时间用于几乎所有其他事情。有一个罕见的异常(exception)值得指出，它应该说明相对时间的值(value)。对于 future 足够远的 future 事件，在某些事情发生的绝对时间可能存在不确定性的情况下，请使用相对时间戳。这是一个真实世界的例子:

假设现在是 2004 年，您需要安排在 2008 年 10 月 31 日下午 1 点在美国西海岸交货(即 America/Los_Angeles/ PST8PDT)。如果您使用 ’2008-10-31 21:00:00.000000+00’::TIMESTAMP WITH TIME ZONE 使用绝对时间存储它，交货会在下午 2 点出现，因为美国政府通过了 Energy Policy Act of 2005这改变了管理夏令时的规则。 2004年预定交货时，日期 10-31-2008本来是太平洋标准时间( +8000 )，但从 2005 年开始，时区数据库认识到 10-31-2008本来是太平洋夏令时 ( +0700)。使用时区存储相对时间戳会导致正确的交付时间表，因为相对时间戳不受国会不知情篡改的影响。使用相对时间和绝对时间进行调度之间的界限是模糊的，但我的经验法则是， future 3-6 个月之后的任何事情的调度都应该使用相对时间戳(已调度 = 绝对 vs 计划 =亲戚？？？)。

另一种/最后一种类型的相对时间是 INTERVAL .示例:“ session 将在用户登录后 20 分钟超时”。安 INTERVAL可以与绝对时间戳 ( TIMESTAMP WITH TIME ZONE ) 或相对时间戳 ( TIMESTAMP WITHOUT TIME ZONE ) 一起正确使用。同样正确的说法是“用户 session 在成功登录后 20 分钟到期(login_utc + session_duration)”或“我们的早餐 session 只能持续 60 分钟(recurring_start_time + meeting_length)”。

最后一点困惑: DATE , TIME , TIME WITHOUT 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)

将日期和时区放入数据库是一件好事，但很容易得到微妙的错误结果。正确和完整地存储时间信息需要最少的额外工作，但这并不意味着总是需要额外的工作。