如何独立于时区获取所有相同的日期和时间

Question

如何将“日期+时间”存储在PostgreSQL数据库中？

这就是我想要达到的目标：

1. 我如何拥有所有发生在(例如)2012年1月1日当地时间00:00:00在世界上任何地方发生的条目？
2. 根据UTC时间显示按日期排序的所有条目。(2012年新年前夕，纽约比伦敦的新年更近)。

我该如何存储数据？我读过PostgreSQL在UTC内部存储所有时间(PostgreSQL文档)，因此我的用户时区实际上丢失了。

我认为我应该使用一列类型为“时间戳没有时区”：

• 第一点很简单。
• 使用另一列类型" string“，我将存储时区字符串(例如:America/New York)，但是，第2点似乎仍然很难做到.

我希望我能说清楚。

编辑新想法:我认为存储两个时间戳:一个没有时区(1.ok)，一个带有时区(2.ok)。

Answer 1

是的，PostgreSQL在内部将所有时间戳存储为UTC。对于timestamp with time zone，时区偏移量仅用于将时间调整到UTC，而不是显式存储。

我不会存储时区字符串，甚至更少的时区缩写(那些不是精确的)。这可能需要昂贵的计算，因为您必须考虑夏时制和其他奇怪的国际时间制度。

您可以将时区偏移量存储为间隔(取12字节)，也可以存储数字秒数(以4字节作为整数)，就像我在相关答案中演示的那样。

或者，就像您已经提出的那样:在UTC时间戳之外存储本地时间戳(取8字节)。这样你的任务就容易多了。考虑以下演示：

-- DROP TABLE tbl;
CREATE TEMP TABLE tbl (id int, ts_tz timestamp with time zone, ts timestamp);
INSERT INTO tbl VALUES
 (1,'2012-1-1 00:00+01','2012-1-1 00:00+01')
,(2,'2012-1-1 00:00+02','2012-1-1 00:00+02')
,(3,'2012-1-1 00:01+03','2012-1-1 00:01+03')
,(4,'2012-1-1 00:02+04','2012-1-1 00:02+04');

对问题1的询问：

SELECT *
FROM   tbl
WHERE  ts = '2012-1-1 00:00'::timestamp;

 id |         ts_tz          |         ts
----+------------------------+---------------------
  1 | 2012-01-01 00:00:00+01 | 2012-01-01 00:00:00
  2 | 2011-12-31 23:00:00+01 | 2012-01-01 00:00:00

对问题2的询问：

SELECT *
FROM   tbl
ORDER  BY ts_tz;

 id |         ts_tz          |         ts
----+------------------------+---------------------
  4 | 2011-12-31 21:02:00+01 | 2012-01-01 00:02:00
  3 | 2011-12-31 22:01:00+01 | 2012-01-01 00:01:00
  2 | 2011-12-31 23:00:00+01 | 2012-01-01 00:00:00
  1 | 2012-01-01 00:00:00+01 | 2012-01-01 00:00:00

使用此解决方案的棘手部分可能是输入本地时间戳。只要所有数据都是本地输入，这就很容易了。但如果你在洛杉矶输入数据，比如纽约的数据，那就需要考虑了。为此，请使用构造：

SELECT ('2012-1-1 00:00+00' AT TIME ZONE 'America/New_York')::timestamp
     , ('2012-1-1 00:00+00' AT TIME ZONE 'America/Los_Angeles')::timestamp

      timezone       |      timezone
---------------------+---------------------
 2011-12-31 19:00:00 | 2011-12-31 16:00:00

请注意，我如何使用时间戳，以时区作为输入。AT TIME ZONE对带有时区或不带时区的时间戳给出了不同的结果。

Answer 2

我想您可以将日期存储为字符串，并使用ISO 8601或RFC 2822，因此您也可以存储时区。实际上，对于更多的比较选项，可以将时间戳和时区存储在两个不同的列中。