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

3 回答

• 135

  肖恩的回答过于复杂和误导 .

  事实是“WITH TIME ZONE”和“WITHOUT TIME ZONE”都将该值存储为类似unix的绝对UTC时间戳 . 区别在于时间戳的显示方式 . 当“WITH time zone”时，显示的值是转换为用户区域的UTC存储值 . 当“没有时区”时，UTC存储的值被扭曲，以便显示相同的钟面，无论用户设置了什么区域“ .

  “没有时区”可用的唯一情况是，无论实际区域如何，时钟面值都适用 . 例如，当时间戳指示投票亭何时可能关闭时（即，它们在20:00关闭，而不管人的时区如何） .

  使用选择3.除非有特殊原因，否则始终使用“带时区” .

  回复于 2024-04-20T23:41:59+08:00
• 52

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

  • 将用户时区的名称存储为用户首选项（例如 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 的原因是因为事情发生的时间是 absolute 时刻 . WITHOUT TIME ZONE 产生一个 relative 时区 . 永远不要混用绝对和相对的TIMESTAMP .

  从编程和一致性的角度来看，确保使用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' 剥离时区信息，并使用目标的参照系（ UTC ）创建相对 TIMESTAMP WITHOUT TIME ZONE .

  从不完整的 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这样的库补充它之前，一开始会感到沮丧） .

  ---

  EDIT

  让我来解释相对与绝对之间的差异 .

  绝对时间用于记录事件 . 示例："User 123 logged in"或"a graduation ceremonies start at 2011-05-28 2pm PST."无论如何在当地时区，如果您可以传送到事件发生的地方，您可以目睹事件的发生 . 数据库中的大多数时间数据都是绝对的（因此应该是 TIMESTAMP WITH TIME ZONE ，理想情况下为0偏移量，文本标签代表管理特定时区的规则 - 而不是偏移量） .

  相对事件将是从尚未确定的时区的角度记录或安排某事物的时间 . 示例：“我们的业务门在上午8点开放，晚上9点关闭”，“让我们每周一早上7点见面，每周早餐会”，或“每个万圣节晚上8点” . 通常，相对时间在事件的模板或工厂中使用，绝对时间几乎用于其他所有事件 . 有一个罕见的例外值得指出哪些应该说明相对时间的 Value . 对于将来可能存在绝对时间不确定性的未来事件，请使用相对时间戳 . 这是一个真实世界的例子：

  假设是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 ） . 存储时区的相对时间戳会产生正确的交付时间表，因为相对时间戳不受国会不明智的篡改影响 . 使用相对于绝对时间来调度事物之间的截止值是一个模糊线，但我的经验法则是，对于未来3-6mo以外的任何事情的调度应该使用相对时间戳（schedule = absolute vs planned =亲戚???） .

  其他/最后一种相对时间是 INTERVAL . 示例："the session will time out 20 minutes after a user logs in" . INTERVAL 可以与绝对时间戳（ TIMESTAMP WITH TIME ZONE ）或相对时间戳（ TIMESTAMP WITHOUT TIME ZONE ）一起正确使用 . 同样正确的说法是"a user session expires 20min after a successful login (login_utc + session_duration)"或"our morning breakfast meeting can only last 60 minutes (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)
  

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

  回复于 2024-04-20T23:41:59+08:00
• 6

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

  回复于 2024-04-20T23:41:59+08:00