问题描述
我刚刚对这两个表达式的这些结果的差异感到绝望:
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显然，第二个表达式根据夏令时规则扣除了两个小时，其中第一个表达式只使用了标准偏移量.
我检查了这两个时区名称的目录.他们都在那里，看起来都一样:
db=# SELECT * FROM pg_timezone_names WHERE name IN ('CET', 'Europe/Vienna');姓名 |缩写 |utc_offset |is_dst---------------+--------+------------+--------欧洲/维也纳 |中科院 |02:00:00 |吨CET |中科院 |02:00:00 |吨我查阅了 关于时区的 PostgreSQL 手册::>
PostgreSQL 允许您以三种不同的形式指定时区:
完整的时区名称，例如 America/New_York.识别的时区名称列在 pg_timezone_names 视图中(见第 45.67 节).PostgreSQL 使用广泛使用的 zoneinfo 时间用于此目的的区域数据，因此相同的名称也被识别许多其他软件.
时区缩写，例如 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)
Local
timezone设置默认为系统区域设置de_AT.UTF-8，但应该与示例无关.
SELECT version();版本-------------------------------------------------------------------------------------------------------x86_64-unknown-linux-gnu 上的 PostgreSQL 9.1.4，由 gcc-4.4.real (Debian 4.4.5-8) 4.4.5 编译，64 位显示 timezone_abbreviations;timezone_abbreviations------------------------默认..(我假设)从这个文件加载缩写:/usr/share/postgresql/9.1/timezonesets/Default
我不知道时区名称 CET 的来源.但显然它存在于我的装置中.对 sqlfiddle 的快速测试显示了相同的结果.
我在两台具有类似设置的不同服务器上进行了测试.还有 PostgreSQL 8.4.在所有的 pg_timezone_names 中发现 'CET' 作为时区 name.
在我发布这篇文章之后，我又运行了另一个查询来检查一个怀疑:
SELECT * FROM pg_timezone_abbrevsWHERE 缩写 IN ('CEST', 'CET');缩写 |utc_offset |is_dst--------+------------+--------中科院 |02:00:00 |吨CET |01:00:00 |F事实证明，有一个也时区缩写名为CET(这是有道理的，CET"是缩写).似乎 PostgreSQL 选择了全名的缩写.所以，即使我在时区 names 中找到了 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);时间戳 |时间戳 |时间戳------------------------+------------------------+------------------------2012-01-18 00:00:00+01 |2012-01-18 01:00:00+01 |2012-01-18 01:00:00+01选择 '2012-08-18 1:0 CEST'::timestamptz(0),'2012-08-18 1:0 CET'::timestamptz(0),'2012-08-18 1:0 欧洲/维也纳'::timestamptz(0);时间戳 |时间戳 |时间戳------------------------+------------------------+------------------------2012-08-18 01:00:00+02 |2012-08-18 02:00:00+02 |2012-08-18 01:00:00+02我在时区名称中发现了 10 个时区缩写，但不明白为什么会出现这些缩写.目的是什么?
其中，由于夏令时设置，时间偏移(utc_offset)在四种情况下不一致:
SELECT n.*, a.*FROM pg_timezone_names nJOIN pg_timezone_abbrevs a ON a.abbrev = n.nameWHERE n.utc_offset <>a.utc_offset;姓名 |缩写 |utc_offset |is_dst |缩写 |utc_offset |is_dst------+--------+------------+--------+--------+------------+--------CET |中科院 |02:00:00 |吨|CET |01:00:00 |FEET |欧洲东部时间 |03:00:00 |吨|EET |02:00:00 |F遇见 |梅斯特 |02:00:00 |吨|遇见 |01:00:00 |F湿 |西 |01:00:00 |吨|湿 |00:00:00 |F在这些情况下，人们可能会被愚弄(就像我一样)，查找 tz name 并找到实际未应用的时间偏移.这是一个不幸的设计 - 如果不是错误，至少是一个文档错误.
我没有在手册中找到关于如何解决时区名称和缩写之间的歧义的任何内容.显然缩写优先.
附录 B.1.日期/时间输入解释提到了时区缩写的查找，但它仍然不清楚时区名称是如何识别的，以及在以下情况下哪个优先一个模棱两可的标记.
如果token是一个文本字符串，匹配可能的字符串:
对作为时区缩写的令牌进行二分查找表查找.
嗯，这句话有一个轻微的暗示，缩写在前，但没有确定性.此外，在两个表中都有一列 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:
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.
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 ...
这篇关于具有相同属性的时区名称在应用于时间戳时会产生不同的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！