在Postgres中转换时区

Question

我正在尝试理解Postgre中的时间戳和时区。我认为我已经理解了，直到我阅读了这篇文章。
请关注“在时区之间转换”部分。它有两个示例。

（考虑默认时区配置为UTC。）

示例1

db=# SELECT timezone('US/Pacific', '2016-01-01 00:00'); outputs 2015-12-31 16:00:00

根据文章和我的理解，由于timezone函数中的'2016-01-01 00:00'部分只是一个字符串，所以它会被静默转换为默认的UTC。因此，从'2016-01-01 00:00' UTC开始，它被转换为US/Pacific，作为timezone函数的要求，即2015-12-31 16:00:00。

示例2

db=# SELECT timezone('US/Pacific', '2016-01-01 00:00'::timestamp); outputs 2016-01-01 08:00:00+00

抱歉，我不明白为什么，那里的解释也没有帮助。好的，{{'2016-01-01 00:00'::timestamp}} 部分已经不再是字符串，而是实际的时间戳。在何时区？如果是UTC，则输出应与示例1相同。所以它会自动转换为 {{'US/Pacific'}}？那么输出是UTC吗？但是为什么？我要求在我的 {{'timezone'}} 中得到一个 {{'US/Pacific'}}，而不是UTC。

请解释当 {{'timezone'}} 获得一个时间戳并要求转换它时，{{'timezone'}} 如何运作。谢谢。

- slevin

---

如果你还没有阅读过的话，文档内容相当详细。摘录如下：对于带有时区时间戳，内部存储值始终为UTC。具有明确时区的输入值使用该时区的适当偏移转换为UTC。如果输入字符串中未指定时区，则认为它处于系统TimeZone参数指示的时区中，并使用时区的偏移将其转换为UTC。 - bma

@bma 没有冒犯之意，但您并没有帮到我。我已经看过文档了。两个示例的默认时区都是UTC。示例2具有时区数据。因此，在内部它被存储为UTC。在这种情况下，默认时区也恰好是UTC。所以这应该与示例1相同。它不是吗？除非字符串转换为UTC和时间戳转换为UTC会产生不同的结果。谢谢。 - slevin

你说得对，重新阅读你的问题后我并没有提供很有帮助的答案（对此我深感抱歉）。在 时区 里有一个详细的回答，但它似乎没有解决你所展示的隐式字符串 -> 时间戳 -> 带时区时间戳的转换。如果你显式地将字符串转换为时间戳并定义时间戳，那么在 #2 中你将得到与 #1 相同的结果。SELECT timezone('US/Pacific', '2016-01-01 00:00'::timestamp at time zone 'UTC'); - bma

@bma我终于弄明白了。我觉得。检查我的答案。谢谢。 - slevin

Answer 1

让我解释一下这两个例子：

在这两个例子中，我们假设一个UTC时区（即SET timezone TO UTC）。

db=# SELECT timezone('US/Pacific', '2016-01-01 00:00');
      timezone
---------------------
 2015-12-31 16:00:00
(1 row)

这相当于SELECT timezone('US/Pacific'，'2016-01-01 00:00' :: timestamptz)，即Postgres将字符串隐式转换为timestamptz。

我们知道timezone函数在timestamp和timestamptz之间进行转换：

由于我们将其作为输入给出了一个timestamptz，它将输出一个timestamp。换句话说，它将绝对时间点2016-01-01 00: 00Z转换为US/Pacific墙上时间，即洛杉矶时钟在该绝对时间点所显示的时间。

在示例2中，我们正在执行相反的操作，即将timestamp转换为timestamptz。换句话说，我们正在问：当洛杉矶时钟显示2016-01-01 00:00时，绝对时间是什么？

您提到：

好的，时区函数的'2016-01-01 00:00':: timestamp 部分不再是字符串，而是实际的时间戳。在哪个时区？

'2016-01-01 00:00':: timestamp 是一个timestamp，即墙上时间。它没有时区概念。

我认为您可能没有完全理解timestamp和timestamptz之间的区别，这是关键。只需将它们视为墙上时间，即世界上某个地方显示的时间，和绝对时间，即我们宇宙中的绝对时间。

您自己回答中的例子并不完全准确。

SELECT ts FROM  (VALUES
(timestamptz '2012-03-05 17:00:00+0') -- outputs 2012-03-05 17:00:00+00 --1
,(timestamptz '2012-03-05 18:00:00+1') -- outputs 2012-03-05 17:00:00+00 --2
,(timestamp   '2012-03-05 18:00:00+1') -- outputs 2012-03-05 18:00:00+00 --3
,(timestamp   '2012-03-05 11:00:00'  AT TIME ZONE '+6') -- outputs 2012-03-05 17:00:00+00 --4
,(timestamp   '2012-03-05 17:00:00'  AT TIME ZONE 'UTC') -- outputs 2012-03-05 17:00:00+00 --5
,(timestamp   '2012-03-05 17:00:00'::timestamp) -- outputs 2012-03-05 17:00:00+00 --6
,(timestamp   '2012-03-05 17:00:00'::timestamptz) -- outputs 2012-03-05 17:00:00+00 --7
    ) t(ts);

你的例子存在问题，因为你构建了一个只有一列的数据集。由于一列只能有一种类型，每一行（或单个值，在这种情况下）都被转换为相同的类型，即timestamptz，即使某些值被计算为timestamp（例如值3）。因此，这里存在额外的隐式转换。

让我们将示例分成单独的查询并查看发生了什么： 示例1

db=# SELECT timestamptz '2012-03-05 17:00:00+0';
      timestamptz
------------------------
 2012-03-05 17:00:00+00

你可能已经知道，timestamptz '2012-03-05 17:00:00+0'和'2012-03-05 17:00:00+0'::timestamptz是等价的（我更喜欢后者）。因此，为了使用与文章中相同的语法，我将重写：

db=# SELECT '2012-03-05 17:00:00+0'::timestamptz;
      timestamptz
------------------------
 2012-03-05 17:00:00+00

现在，这里发生了什么？比你原来的解释少得多。该字符串仅被解析为。当结果被打印时，它使用当前设置的配置将其转换回底层数据结构的可读表示，即2012-03-05 17:00:00 +00。让我们更改配置并查看会发生什么：

db=# SET timezone TO 'Europe/Berlin';
SET
db=# SELECT '2012-03-05 17:00:00+0'::timestamptz;
      timestamptz
------------------------
 2012-03-05 18:00:00+01

唯一改变的是如何在屏幕上打印timestamptz，即使用Europe/Berlin时区。

示例2

db=# SELECT timestamptz '2012-03-05 18:00:00+1';
      timestamptz
------------------------
 2012-03-05 17:00:00+00
(1 row)

再次，只需解析日期。

示例3

db=# SELECT timestamp '2012-03-05 18:00:00+1';
      timestamp
---------------------
 2012-03-05 18:00:00
(1 row)

这与'2012-03-05 18:00:00+1'::timestamp是一样的。这里发生的是，时区偏移被忽略了，因为你要求一个时间戳。示例4。

db=# SELECT timestamp '2012-03-05 11:00:00' AT TIME ZONE '+6';
        timezone
------------------------
 2012-03-05 17:00:00+00
(1 row)

让我们重新编写为更简单的形式:

db=# SELECT timezone('+6', '2012-03-05 11:00:00'::timestamp);
        timezone
------------------------
 2012-03-05 17:00:00+00
(1 row)

这是在询问：当具有+6小时偏移量的时区墙上的时钟显示2012-03-05 11:00:00时，绝对时间是什么？

示例5

db=# SELECT timestamp '2012-03-05 17:00:00' AT TIME ZONE 'UTC';
        timezone
------------------------
 2012-03-05 17:00:00+00
(1 row)

让我们重写：

db=# SELECT timezone('UTC', '2012-03-05 17:00:00'::timestamp);
        timezone
------------------------
 2012-03-05 17:00:00+00
(1 row)

这个问题是在询问：当UTC时区的墙上时钟显示2012-03-05 17:00:00时，绝对时间是什么？

例子6

db=# SELECT timestamp '2012-03-05 17:00:00'::timestamp;
      timestamp
---------------------
 2012-03-05 17:00:00
(1 row)

在这里，你将timestamp转换了两次，这没有任何区别。让我们简化一下：

db=# SELECT '2012-03-05 17:00:00'::timestamp;
      timestamp
---------------------
 2012-03-05 17:00:00
(1 row)

我认为这很清楚。

例子7

db=# SELECT timestamp '2012-03-05 17:00:00'::timestamptz;
      timestamptz
------------------------
 2012-03-05 17:00:00+00
(1 row)

让我们重写：

db=# SELECT ('2012-03-05 17:00:00'::timestamp)::timestamptz;
      timestamptz
------------------------
 2012-03-05 17:00:00+00
(1 row)

您首先将字符串解析为 timestamp，然后使用当前设置的 timezone 将其转换为 timestamptz。如果我们更改了 timezone，那么会得到其他结果，因为Postgres在将 timestamp（或缺少时区信息的字符串）转换为 timestamptz 时假定了该时区：

db=# SET timezone TO 'Europe/Berlin';
SET
db=# SELECT ('2012-03-05 17:00:00'::timestamp)::timestamptz;
      timestamptz
------------------------
 2012-03-05 17:00:00+01
(1 row)

这个绝对时间以UTC表示，是2012-03-05 16:00:00+00，因此与原始示例不同。

---

我希望这能澄清事情。再次强调，理解timestamp和timestamptz之间的区别非常重要。可以将其视为墙上时间与绝对时间的区别。

- fphilipe

---

timestamptz可以解析任何偏移量，因此日期可读。 timestamp忽略它，因为没有这样的概念。任何实际的转换（不是解析）都是在指定特定时区时进行的。 timezone从timezone（时钟）转换为timezonetz（全局），反之亦然。如果使用SET或在postgresql.conf中定义了不同的时区，则进行最终的附加转换。这是“黄金法则”。我现在明白了。非常感谢您的时间和努力。 - slevin

Answer 2

我理解的是：请耐心听我说。
我的默认时区，定义在postgresql.conf中，是UTC。检查一下这段代码。

SELECT ts FROM  (VALUES
(timestamptz '2012-03-05 17:00:00+0') -- outputs 2012-03-05 17:00:00+00 --1
,(timestamptz '2012-03-05 18:00:00+1') -- outputs 2012-03-05 17:00:00+00 --2
,(timestamp   '2012-03-05 18:00:00+1') -- outputs 2012-03-05 18:00:00+00 --3
,(timestamp   '2012-03-05 11:00:00'  AT TIME ZONE '+6') -- outputs 2012-03-05 17:00:00+00 --4
,(timestamp   '2012-03-05 17:00:00'  AT TIME ZONE 'UTC') -- outputs 2012-03-05 17:00:00+00 --5
,(timestamp   '2012-03-05 17:00:00'::timestamp) -- outputs 2012-03-05 17:00:00+00 --6
,(timestamp   '2012-03-05 17:00:00'::timestamptz) -- outputs 2012-03-05 17:00:00+00 --7
    ) t(ts);

现在，假设这是Postgre在说话：
输出有特殊时区定义。因此，我将以默认的UTC输出所有内容。让我们开始吧。

1 (timestamptz '2012-03-05 17:00:00+0')
这是时间感知数据，偏移量为0，因此它是UTC。默认值也是UTC。我将原样保存它（不需要转换），并输出2012-03-05 17:00:00+00，因为UTC输入到UTC保存到UTC输出。

2 (timestamptz '2012-03-05 18:00:00+1')
同样是时间感知数据，偏移量为+1，因此它不是UTC。通过减去1来抵消偏移量，以将其转换为UTC，因此我可以将其保存为UTC，那是默认值。输出2012-03-05 17:00:00+00，因为非UTC输入到UTC保存为UTC输出。

3 (timestamp '2012-03-05 18:00:00+1')
时间不带时区信息。忽略偏移量，假设它是默认的UTC并保存原样。输出2012-03-05 18:00:00+00，因为我不知道，我不关心，我会假装这是我的默认UTC输入到UTC保存到UTC输出。

4 (timestamp '2012-03-05 11:00:00' AT TIME ZONE '+6')
同样是时间不带时区信息。忽略偏移量（如果有的话），然后将其转换为给定的AT TIME ZONE '+6'偏移量，以便我可以将其视为完整的时间不带时区数据。所以我的最终数据是2012-03-05 17:00:00+00。但这仍然不是带时区信息的数据。因此，我将假定这是我的默认UTC并将其保存为原样。输出2012-03-05 17:00:00+00，因为我不知道，我不关心，我会假装这是我的默认UTC输入到UTC保存到UTC输出。

5 (时间戳 '2012-03-05 17:00:00' 在时区 'UTC' 下)
和之前的数据一样，这是一个没有时区信息的数据。如果有偏移量，我会忽略它。然后我将其转换为给定的在时区 'UTC'，因此没有实际转换，因为没有实际偏移量（UTC偏移量为0）。所以我的最终数据是2012-03-05 17:00:00。但这仍然不是带有时区信息的数据。因此，我将假设这是我的默认UTC并按原样保存。输出2012-03-05 17:00:00，因为我不知道、我不关心，我会假装这是我的默认UTC输入并保存为UTC输出。

6 (timestamp '2012-03-05 17:00:00'::timestamp)
这是时间无关数据，再次转换为时间无关数据。因此，像4一样，如果有偏移量，我将忽略它。也没有AT TIME ZONE，因此不需要进行转换。我的最终时间无关数据是'2012-03-05 17:00:00'。我将假定这是我的默认UTC并按原样保存。输出2012-03-05 17:00:00+00，因为我不知道，我不在意，我会假装这是我的默认UTC输入到UTC保存到UTC输出。

7 (timestamp '2012-03-05 17:00:00'::timestamptz)
这是时间无关数据，转换为时间相关数据。但是没有偏移量，转换等操作。因此，这是UTC。所以，我将按原样保存它。输出2012-03-05 17:00:00+00，因为UTC输入到UTC保存到UTC输出。

（希望上述内容对任何人有所帮助，总的来说）

现在！关于这篇文章
示例1
SELECT timezone('US/Pacific', '2016-01-01 00:00');
数据没有时间信息，但我可以将其转换为时间感知。根据文章，由于没有时区信息，它可以在默认的UTC时区中解析。 因此，时间感知，UTC数据，按原样保存，但在输出之前将其转换为US/Pacific。这就是为什么文章说“我们得到了加利福尼亚州的墙上时间，用于2016年1月1日00:00 UTC。” 输出是2015-12-31 16:00:00，这是加利福尼亚州的墙上时间，对于'2016-01-01 00:00' UTC输入。

文章也提到：“请注意，我们将时间戳作为字符串传递，它会被隐式转换为timestamptz”。这可以写成SELECT timezone('US/Pacific', '2016-01-01 00:00'::timestamptz);，仍然输出2015-12-31 16:00:00。时区感知数据，没有偏移量，因此其偏移量为0，因此是UTC。 UTC也是默认值，因此只需保存即可。在输出之前将其转换为US/Pacific。这就是为什么它再次输出2015-12-31 16:00:00的原因。

由于“timezone（zone，timestamp）等同于符合SQL的构造timestamp AT TIME ZONE zone”，根据文章，则

SELECT timezone('US/Pacific', '2016-01-01 00:00');
SELECT timezone('US/Pacific', '2016-01-01 00:00'::timestamptz);
timestamptz '2016-01-01 00:00' at time zone 'US/Pacific'
timestamptz '2016-01-01 00:00+00' at time zone 'US/Pacific'

都是一样的

时间感知数据（或使其成为时间感知），没有偏移量，将其保存为UTC，输出转换后的结果，作为US/Pacific。

示例2
SELECT timezone('US/Pacific', '2016-01-01 00:00'::timestamp);
不带时间信息的数据。我能像示例1中那样将其转换为UTC默认值吗？不行，因为它被转换为不带时间信息的数据（::timestamp部分）。我无能为力。这是时间感知数据。

我将忽略任何偏移量。与上述第4种情况不同，没有定义偏移量，也没有AT TIME ZONE '+ or -X'。因此，为了获取UTC时间，我将根据US/Pacific将'2016-01-01 00:00'转换回UTC时间。从太平洋时间加8小时以转换为UTC时间。我的UTC时间现在是2016-01-01 08:00:00+00。按原样保存。输出2016-01-01 08:00:00+00，因为我不知道、不关心，我会假装这是我的默认UTC输入并保存到UTC输出。

再次根据article中所述，“timezone(zone, timestamp)等价于符合SQL标准的构造timestamp AT TIME ZONE zone”，因此
。

SELECT timezone('US/Pacific', '2016-01-01 00:00'::timestamp);
timestamp '2016-01-01 00:00' at time zone 'US/Pacific'
timestamp '2016-01-01 00:00+00' at time zone 'US/Pacific'

都是一样的

不考虑时间，忽略偏移量，转换回UTC时间，这就是UTC时间，将其保存为UTC输出。

谢谢

- slevin

---

你的问题让我想起了一本最近出版的关于这个主题的书，你可能会感兴趣：http://korban.net/postgres/book/。我与该网站没有任何关联，但是经过查看，它似乎相当全面地涵盖了这个主题。 - bma