具有相同属性的时区名称在应用于时间戳时产生不同的结果 [英] Time zone names with identical properties yield different result when applied to timestamp
问题描述
我刚刚花了一个小时在这两个表达式的这些结果中的差异：
db =＃SELECT'2012-01-18 1：0 CET ':: timestamptz AT TIME ZONE'UTC'
，'2012-01-18 1：0 欧洲/维也纳':: timestamptz AT TIME ZONE'UTC';
时区|时区
--------------------- + ---------------------
2012-08- 18 00:00:00 | 2012-08- 17 23:00:00
显然，第二表达式根据DST规则扣除两个小时，其中第一个仅使用标准偏移量。
我查看了这两个时区名称的目录。他们都在那里，看起来是一样的：
db =＃SELECT * FROM pg_timezone_names WHERE name IN（'CET'，'Europe / Vienna'）;
name |缩写utc_offset | is_dst
--------------- + -------- + ------------ + ------- -
欧洲/维也纳| CEST | 02:00:00 | t
CET | CEST | 02:00:00 | t
我查阅了关于时区的PostgreSQL手册：
PostgreSQL允许你以三种不同的形式指定时区：
全时区名称，例如America / New_York。识别的时区名称列在pg_timezone_names视图
中（参见第45.67节）。 PostgreSQL使用了广泛使用的zoneinfo time
区域数据，因此，同样的名称也被
认可为其他软件。
A时区缩写，例如PST。 这样的规范仅仅定义了与UTC的特定偏移量，与全时
区域名称相反，这可能意味着一组夏令时转换日期
规则。已识别缩写在
pg_timezone_abbrevs视图中列出（参见第45.66节）。您不能将
配置参数timezone或log_timezone设置为时区
缩写，但您可以在日期/时间输入值
中使用缩写词和AT TIME ZONE运算符。
粗体强调我的。
所以为什么差异？
我的设置（更多详细信息添加）
-
Debian Squeeze上的PostgreSQL 9.1.4（来自的标准挤压输出http://backports.debian.org/debian-backports ）
-
本地
时区设置默认为系统区域设置de_AT.UTF-8，但与示例无关。
SELECT version ;
版本
----------------------------------------- -------------------------------------------------- ------------
PostgreSQL 9.1.4 on x86_64-unknown-linux-gnu，由gcc-4.4.real（Debian 4.4.5-8）编译4.4.5,64- bit
SHOW timezone_abbreviations;
timezone_abbreviations
------------------------
默认
..（我假设）从此文件加载缩写：
/usr/share/postgresql/9.1/timezonesets/Default
我正在失去时区名称 CET 来自。但显然是在我的安装。 在sqlfiddle上的快速测试显示相同的结果。
我在两个不同的服务器上进行了相似的设置测试。还有PostgreSQL 8.4。在
pg_timezone_names 中找到CET作为时区名称。刚刚发布后，我再运行另一个查询来检查：
SELECT * FROM pg_timezone_abbrevs
WHERE abbrev IN（'CEST'，'CET'）;
abbrev | utc_offset | is_dst
-------- + ------------ + --------
CEST | 02:00:00 | t
CET | 01:00:00 | f
事实证明，也时区缩写命名为 CET （这是有意义的，CET是缩写）。看起来PostgreSQL选择了全名的缩写。所以，即使在时区名称中找到 CET ，表达式2012-01-18 1：0 CET:: timestamptz是根据时区缩写的微妙不同规则进行解释。
SELECT'2012-01-18 1：0 CEST':: timestamptz（0）
，'2012-01-18 1：0 CET':: timestamptz（0）
，'2012-01-18 1：0欧洲/维也纳':: timestamptz（0）;
timestamptz | timestamptz | timestamptz
------------------------ + -------------------- ---- + ------------------------
2012-01-18 00：00：00 + 01 | 2012-01-18 01：00：00 + 01 | 2012-01-18 01：00：00 + 01
SELECT'2012-08-18 1：0 CEST':: timestamptz（0）
，'2012- 08-18 1：0 CET':: timestamptz（0）
，'2012-08-18 1：0欧洲/维也纳':: timestamptz（0）;
timestamptz | timestamptz | timestamptz
------------------------ + -------------------- ---- + ------------------------
2012-08-18 01：00：00 + 02 | 2012-08-18 02：00：00 + 02 | 2012-08-18 01：00：00 + 02
我发现10个时区>时区中的缩写，不明白为什么这些。目的是什么？
其中，由于DST设置，时间偏移（ utc_offset ）在四种情况下不同意：
SELECT n。*，a。*
FROM pg_timezone_names n
JOIN pg_timezone_abbrevs a ON a。 abbrev = n.name
WHERE n.utc_offset<> a.utc_offset;
name |缩写| utc_offset | is_dst |缩写| utc_offset | is_dst
------ + -------- + ------------ + -------- + ------- - + ------------ + --------
CET | CEST | 02:00:00 | t | CET | 01:00:00 | f
EET | EEST | 03:00:00 | t | EET | 02:00:00 | f
MET | MEST | 02:00:00 | t | MET | 01:00:00 | f
WET | WEST | 01:00:00 | t | WET | 00:00:00 | f
在这些情况下，人们可能被愚弄（像我一样），查找tz >名称，并找到实际未应用的时间偏移量。这是一个不幸的设计 - 如果不是错误，至少有一个文档错误。
我没有在手册中找到任何关于如何歧义在时区名称和缩写之间解决。显然缩写缩写优先。
附录B.1。日期/时间输入解释提到查找时区缩写，但是仍然不清楚是如何识别时区名称，哪些在一个不明确的标记。
如果令牌是文本字符串，请匹配可能的字符串：
将令牌的二进制搜索表查找作为时区缩写。
嗯，有一点点在这句话中提到缩写首先出现，但没有确定性。另外，两个表格中都有一列 abbrev ， pg_timezone_names 和 pg_timezone_abbrevs ...
I've just spent an hour in despair with the discrepancy in these results of these two expressions:
db=# SELECT '2012-01-18 1:0 CET'::timestamptz AT TIME ZONE 'UTC'
,'2012-01-18 1:0 Europe/Vienna'::timestamptz AT TIME ZONE 'UTC';
timezone | timezone
---------------------+---------------------
2012-08-18 00:00:00 | 2012-08-17 23:00:00
Obviously, the second expression deducts two hours according to DST rules, where the first one only uses the standard offset.
I checked the catalogs for these two time zone names. They are both there and look just the same:
db=# SELECT * FROM pg_timezone_names WHERE name IN ('CET', 'Europe/Vienna');
name | abbrev | utc_offset | is_dst
---------------+--------+------------+--------
Europe/Vienna | CEST | 02:00:00 | t
CET | CEST | 02:00:00 | t
I consulted the PostgreSQL manual about time zones:
PostgreSQL allows you to specify time zones in three different forms:
A full time zone name, for example America/New_York. The recognized time zone names are listed in the pg_timezone_names view (see Section 45.67). PostgreSQL uses the widely-used zoneinfo time zone data for this purpose, so the same names are also recognized by much other software.
A time zone abbreviation, for example PST. Such a specification merely defines a particular offset from UTC, in contrast to full time zone names which can imply a set of daylight savings transition-date rules as well. The recognized abbreviations are listed in the pg_timezone_abbrevs view (see Section 45.66). You cannot set the configuration parameters timezone or log_timezone to a time zone abbreviation, but you can use abbreviations in date/time input values and with the AT TIME ZONE operator.
Bold Emphasis mine.
So why the difference?
My setup (more details added)
PostgreSQL 9.1.4 on Debian Squeeze (standard squeeze-backports from http://backports.debian.org/debian-backports)
Local
timezonesetting defaults to the system localede_AT.UTF-8, but should be irrelevant for the example.
SELECT version();
version
-------------------------------------------------------------------------------------------------------
PostgreSQL 9.1.4 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit
SHOW timezone_abbreviations;
timezone_abbreviations
------------------------
Default
.. which (I assume) loads abbreviations from this file: /usr/share/postgresql/9.1/timezonesets/Default
I am at a loss where the time zone name CET comes from. But obviously it is there in my installations. A quick test on sqlfiddle shows the same result.
I tested on two different servers with similar setup. Also with PostgreSQL 8.4. Found 'CET' as time zone name in pg_timezone_names in all of them.
Right after I posted this, I ran another query to check on a suspicion:
SELECT * FROM pg_timezone_abbrevs
WHERE abbrev IN ('CEST', 'CET');
abbrev | utc_offset | is_dst
--------+------------+--------
CEST | 02:00:00 | t
CET | 01:00:00 | f
As it turns out, there is also a time zone abbreviation named CET (which makes sense, "CET" being an abbreviation). And it seems that PostgreSQL picks the abbreviation over the full name. So, even though I found CET in the time zone names, the expression '2012-01-18 1:0 CET'::timestamptz is interpreted according to the subtly different rules for time zone abbreviations.
SELECT '2012-01-18 1:0 CEST'::timestamptz(0)
,'2012-01-18 1:0 CET'::timestamptz(0)
,'2012-01-18 1:0 Europe/Vienna'::timestamptz(0);
timestamptz | timestamptz | timestamptz
------------------------+------------------------+------------------------
2012-01-18 00:00:00+01 | 2012-01-18 01:00:00+01 | 2012-01-18 01:00:00+01
SELECT '2012-08-18 1:0 CEST'::timestamptz(0)
,'2012-08-18 1:0 CET'::timestamptz(0)
,'2012-08-18 1:0 Europe/Vienna'::timestamptz(0);
timestamptz | timestamptz | timestamptz
------------------------+------------------------+------------------------
2012-08-18 01:00:00+02 | 2012-08-18 02:00:00+02 | 2012-08-18 01:00:00+02
I find 10 cases of time zone abbreviations in the time zone names and fail to understand why those are there. What's the purpose?
Among those, the time offset (utc_offset) disagrees in four cases due to the DST setting:
SELECT n.*, a.*
FROM pg_timezone_names n
JOIN pg_timezone_abbrevs a ON a.abbrev = n.name
WHERE n.utc_offset <> a.utc_offset;
name | abbrev | utc_offset | is_dst | abbrev | utc_offset | is_dst
------+--------+------------+--------+--------+------------+--------
CET | CEST | 02:00:00 | t | CET | 01:00:00 | f
EET | EEST | 03:00:00 | t | EET | 02:00:00 | f
MET | MEST | 02:00:00 | t | MET | 01:00:00 | f
WET | WEST | 01:00:00 | t | WET | 00:00:00 | f
In these cases, people may be fooled (like I was), looking up the tz name and finding a time offset that is not actually applied. That is an unfortunate design - if not a bug, at least a documentation bug.
I fail to find anything in the manual about how ambiguities between time zone names and abbreviations are resolved. Obviously abbreviations take precedence.
Appendix B.1. Date/Time Input Interpretation mentions the lookup for time zone abbreviations, but it remains unclear how time zone names are identified and which of them has priority in case of an ambiguous token.
If the token is a text string, match up with possible strings:
Do a binary-search table lookup for the token as a time zone abbreviation.
Well, there is a slight hint in this sentence that abbreviations come first, but nothing definitive. Also, there is a column abbrev in both tables, pg_timezone_namesand pg_timezone_abbrevs ...
这篇关于具有相同属性的时区名称在应用于时间戳时产生不同的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
