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

首先，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一样，之所以 Select WITH TIME ZONE是因为某件事发生的时间是absolute分钟.WITHOUT TIME ZONE产生relative时区.永远不要把绝对时间戳和相对时间戳混在一起.

从程序和一致性的Angular 来看，确保所有计算都使用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'会go 除时区信息，并使用目标的参考系(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

让我再解释一下相对与绝对的区别.

绝对时间用于记录事件.例如:"用户123登录"或"毕业典礼于太平洋标准时间2011年5月28日下午2点开始."不管你所在的时区是什么，如果你能传送到事件发生的地方，你就能见证事件的发生.数据库中的大多数时间数据都是绝对的(因此应该是TIMESTAMP WITH TIME ZONE，最好是+0偏移量和表示特定时区规则的文本标签，而不是偏移量).

一个相对的事件是从一个尚未确定的时区的Angular 记录或安排某事的时间.例如:"我们公司的大门早上8点开门，晚上9点关门"，"让我们每周一早上7点见面，每周开一次早餐会"，或"每一个万圣节晚上8点"一般来说，相对时间用于模板或工厂中的事件，而绝对时间用于几乎所有其他事情.有一个罕见的例外值得指出，它应该说明相对时间的价值.对于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个月的任何事情的安排都应该使用相对时间戳(计划=绝对时间和计划=相对？？？).

另一种/最后一种相对时间是INTERVAL.示例:"用户登录后，会话将超时20分钟".INTERVAL可以与绝对时间戳(TIMESTAMP WITH TIME ZONE)或相对时间戳(TIMESTAMP WITHOUT TIME ZONE)一起正确使用.同样正确的说法是，"用户会话在成功登录后20分钟过期(登录时间+会话持续时间)"或"我们的早餐会议只能持续60分钟(重复的开始时间+会议长度)".

最后一点混淆: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)

在数据库中输入日期和时区是一件好事，但要正确、完整地存储时间信息，至少需要付出额外的努力，但这并不意味着总是需要额外的努力.