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

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

我可以

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

我有一个轻微的选择3(时间戳与时区) ，但希望有一个受过教育的意见的问题。

77906 次浏览
小开

我倾向于选择3，因为 Postgres 可以为你重新计算相对于时区的时间戳，而另外两个你必须自己做。使用时区存储时间戳的额外存储开销实际上可以忽略不计，除非要存储数百万条记录，在这种情况下，您可能已经有相当多的存储需求。

小开
最佳答案

首先，PostgreSQL 的时间处理和算术非常棒，在一般情况下，选项3也不错。然而，它是对时间和时区的一种不完整的观点，可以补充如下:

  1. 将用户的时区名称存储为用户首选项(例如，America/Los_Angeles，而不是 -0700)。
  2. 将用户事件/时间数据提交到本地参考框架(最有可能的是与 UTC 的偏移量，例如 -0700)。
  3. 在应用程序中，将时间转换为 UTC并使用 TIMESTAMP WITH TIME ZONE列存储。
  4. 将时间请求本地返回到用户的时区(即从 UTC转换到 America/Los_Angeles)。
  5. 将数据库的 timezone设置为 UTC。

这个选项并不总是有效，因为很难获得用户的时区，因此对冲建议使用 TIMESTAMP WITH TIME ZONE进行轻量级应用程序。也就是说，让我更详细地解释这个选项4的一些背景方面。

和选项3一样，WITH TIME ZONE的原因是因为某些事情发生的时间是 绝对的时刻。WITHOUT TIME ZONE产生一个 亲戚时区。永远，永远，永远不要混淆绝对时间戳和相对时间戳。

从编程和一致性的角度来看，确保所有计算都使用 UTC 作为时区。这不是 PostgreSQL 需求，但是它在与其他编程语言或环境集成时很有帮助。在列上设置 CHECK，以确保对时间戳列的写操作具有 0的时区偏移量，这是一种防御性位置，可以防止一些类型的 bug (例如，脚本将数据转储到文件中，而其他东西使用词法排序对时间数据进行排序)。同样，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点开门”一般来说，相对时间用于事件模板或工厂，绝对时间用于几乎所有其他事件。有一个罕见的例外值得指出，它应该说明相对时间的价值。对于将来发生的事件，如果发生的时间可能不确定，那么使用相对时间戳。下面是一个现实生活中的例子:

假设现在是2004年，您需要安排在2008年10月31日下午1点在美国西海岸交货(即 America/Los_Angeles/PST8PDT)。如果您使用 ’2008-10-31 21:00:00.000000+00’::TIMESTAMP WITH TIME ZONE存储使用绝对时间，交付将显示在下午2时，因为美国政府通过了 2005年能源政策法，改变了规则的夏时制时间。在2004年交付时，10-31-2008将是太平洋标准时间(+8000) ，但从2005年开始时区数据库认识到，10-31-2008将是太平洋夏令时(+0700)。存储一个带有时区的相对时间戳可以产生一个正确的交付时间表，因为相对时间戳不会受到国会不知情的篡改。在使用相对时间和绝对时间来安排事情之间的分界线是一条模糊的线，但是我的经验法则是，未来任何超过3-6mo 的安排都应该使用相对时间戳(计划 = 绝对 vs 计划 = 相对? ? ?).

另一种/最后一种相对时间类型是 INTERVAL。示例: “会话将在用户登录后超时20分钟”。INTERVAL可以正确使用绝对时间戳(TIMESTAMP WITH TIME ZONE)或相对时间戳(TIMESTAMP WITHOUT TIME ZONE)。同样正确的说法是，“一个用户会话在成功登录后20分钟到期(login _ utc + session _ )”或者“我们的早餐会议只能持续60分钟(循环 _ start _ time + meet _ 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，而是 TIME1:

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)

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

小开

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

事实上，“ WITHTIME ZONE”和“ WITHOUTTIME ZONE”都将值存储为类 Unix 的绝对 UTC 时间戳。区别在于时间戳的显示方式。当“ WITHtime zone”时，显示的值是转换为用户区域的 UTC 存储值。当“没有时区”的 UTC 存储值扭曲，以显示相同的时钟面，无论用户设置的区域。

“ WITHOUT 时区”唯一可用的情况是，无论实际时区如何，时钟面值都适用。例如，当时间戳显示投票站何时可能关闭(即。他们在20:00关门，不管一个人的时区)。

使用选项3。除非有特别的原因，否则一定要使用“ WITHTime Zone”。


提交答案