PostgreSQL 时区转换 'EST' ！= '-05'

Question

我在x86_64-pc-linux-gnu上运行PostgreSQL 9.6.6，我的时区设置为"UTC"。

有谁知道为什么以下SELECT语句的结果不同？

一)

SELECT timezone('EST', '2017-12-21');
timezone       
---------------------
2017-12-20 19:00:00

二)

SELECT timezone('-05', '2017-12-21');
timezone       
---------------------
2017-12-21 05:00:00

根据pg_timezone_names表，-05应具有与EST相同的偏移量...有什么想法吗？谢谢。

Answer 1

> https://www.postgresql.org/docs/current/static/view-pg-timezone-names.html

视图pg_timezone_names提供时区名称的列表，这些时区名称是 通过设置时区识别

并进一步：

utc_offset间隔 与 UTC 的偏移量(正表示格林威治以东)

当您set timezone to 'EST'- 您声明客户端处于 EST 时区时，因此返回的时间将根据您的 tz 进行调整：

t=# select '2017-12-21'::timestamptz;
timestamptz
------------------------
2017-12-21 00:00:00-05
(1 row)

区间匹配从pg_timezone_names和相等-05utc_offset，因此它按预期工作。(实际上在美国东部标准时间将比 UTC 少 5 小时)如果您set timezone to '-05'相同的结果.

-05和EST都给出了相同的结果，如文档中所述，SET TIMEZONE。

现在，您回答与文档的协调，以使用interval：https://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT

在这些表达式中，可以指定所需的时区 作为文本字符串(例如，"PST")或作为间隔(例如， 间隔 '-08：00')。

遵循这些规则，它也可以工作：

t=# select '2017-12-21'::timestamptz at time zone 'EST';
timezone
---------------------
2017-12-20 19:00:00
(1 row)
t=# select '2017-12-21'::timestamptz at time zone interval '-05:00';
timezone
---------------------
2017-12-20 19:00:00
(1 row)

但更进一步，文档说：

在文本情况下，可以通过任何方式指定时区名称 在第 8.5.3 节中描述。

这是 https://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-TIMEZONES

PostgreSQL允许您以三种不同的形式指定时区：

• pg_timezone_names中列出了可识别的时区名称
• 公认的缩写列在pg_timezone_abbrevs
• POSIX 样式的时区规范，格式为 STDoffset 或 STDoffsetDST

(格式化我的)

最后：

应该警惕的是，POSIX风格的时区功能可能会导致 默默地接受虚假输入...要记住的另一个问题是 在 POSIX 时区名称中，正偏移量用于格林威治以西的位置。在其他任何地方，PostgreSQL都遵循ISO-8601。 约定正时区偏移量位于格林威治以东。

TL;博士

简而言之- 当您将"-05"定义为timezone()函数或AT TIME ZONE指令的文本(不是间隔)输入(实际上相同)时，Postgres 认为这是尝试使用 POSIX 样式时区，从而反转符号，因此你得到"相反"的结果......

此记录的反转的简单演示：

t=# select '2017-12-21'::timestamptz at time zone '05';
timezone
---------------------
2017-12-20 19:00:00
(1 row)

Answer 2

好的，我想我找到了我自己问题的答案：

根据PostgreSQL文档，以下链接中的第9.9.3节 https://www.postgresql.org/docs/9.6/static/functions-datetime.html

在这些表达式中，可以将所需的时区指定为文本字符串(例如，"PST")或间隔(例如，INTERVAL '-08：00')。在文本情况下，可以通过第 8.5.3 节中描述的任何方式指定时区名称。

因此，使用INTERVAL语法，以下内容似乎有效：

SELECT timezone(INTERVAL '-05:00', '2017-12-21');
timezone       
---------------------
2017-12-20 19:00:00

我认为仍然很好奇，SELECT timezone('-05', '2017-12-21');到底是什么意思，因为以下内容还提供了预期的结果(添加了 TZ 偏移量)：

SELECT timezone('-05', '2017-12-21'::timestamp);
timezone        
------------------------
2017-12-20 19:00:00+00