主页 / dba / 问题 / 30787
Accepted
sufleR
Asked: 2012-12-20 15:50:45 +0800 CST

带有区域名称 PostgreSQL 错误的“AT TIME ZONE”？

  • 772

我正在回答这个stackoverflow问题，发现奇怪的结果：

 select * from  pg_timezone_names where name = 'Europe/Berlin' ;
     name      | abbrev | utc_offset | is_dst 
---------------+--------+------------+--------
 Europe/Berlin | CET    | 01:00:00   | f

和下一个查询

select id, 
  timestampwithtimezone, 
  timestampwithtimezone at time zone 'Europe/Berlin' as berlin, 
  timestampwithtimezone at time zone 'CET' as cet 
from data ;
 id  | timestampwithtimezone  |       berlin        |         cet         
 -----+------------------------+---------------------+---------------------
 205 | 2012-10-28 01:30:00+02 | 2012-10-28 01:30:00 | 2012-10-28 00:30:00
 204 | 2012-10-28 02:00:00+02 | 2012-10-28 02:00:00 | 2012-10-28 01:00:00
 203 | 2012-10-28 02:30:00+02 | 2012-10-28 02:30:00 | 2012-10-28 01:30:00
 202 | 2012-10-28 02:59:59+02 | 2012-10-28 02:59:59 | 2012-10-28 01:59:59
 106 | 2012-10-28 02:00:00+01 | 2012-10-28 02:00:00 | 2012-10-28 02:00:00

我正在使用 PostgreSQL 9.1.2 和 ubuntu 12.04。
刚刚检查了 8.2.11 结果是一样的。

根据文档，我使用名称或缩写并不重要。

这是一个错误吗？
难道我做错了什么？
有人可以解释这个结果吗？

编辑 对于 CET 不是欧洲/柏林的评论。

我只是从 pg_timezone_names 中选择值。

select * from  pg_timezone_names  where abbrev ='CEST';
 name | abbrev | utc_offset | is_dst 
------+--------+------------+--------

和

select * from  pg_timezone_names  where abbrev ='CET';
        name         | abbrev | utc_offset | is_dst 
---------------------+--------+------------+--------
 Africa/Tunis        | CET    | 01:00:00   | f
 Africa/Algiers      | CET    | 01:00:00   | f
 Africa/Ceuta        | CET    | 01:00:00   | f
 CET                 | CET    | 01:00:00   | f
 Atlantic/Jan_Mayen  | CET    | 01:00:00   | f
 Arctic/Longyearbyen | CET    | 01:00:00   | f
 Poland              | CET    | 01:00:00   | f
 .....

在冬季欧洲/柏林是+01。夏季是+02。

EDIT2 在 2012 年 10 月 28 日，时区在 2:00 从夏令时变为冬令时。
这两条记录在欧洲/柏林具有相同的价值：

204 | 2012-10-28 02:00:00+02 | 2012-10-28 02:00:00 | 2012-10-28 01:00:00
106 | 2012-10-28 02:00:00+01 | 2012-10-28 02:00:00 | 2012-10-28 02:00:00

这表明如果我对大数据范围（夏季时间和冬季时间）使用缩写之一（CET 或 CEST），则某些记录的结果将是错误的。如果我使用“欧洲/柏林”会很好。

我将系统时间更改为 '2012-01-17' 并且 pg_timezone_names 也更改了。

select * from  pg_timezone_names  where name ='Europe/Berlin';
     name      | abbrev | utc_offset | is_dst 
---------------+--------+------------+--------
 Europe/Berlin | CEST   | 02:00:00   | t
postgresql timestamp

3 个回答

  1. Best Answer

    实际上，文档清楚地表明时区名称和缩写的行为会有所不同。

    简而言之，这是缩写和全名之间的区别：缩写总是表示与 UTC 的固定偏移量，而大多数全名都暗示当地的夏令时规则，因此有两个可能的 UTC 偏移量。参考

    FWIW，同样的参考资料也说

    我们不建议使用 time with time zone 类型（尽管 PostgreSQL 支持旧应用程序并符合 SQL 标准）。

    • 9

  2. 这仍然不是它的要点！前段时间我遇到了一个非常相似的问题。

    时区缩写的主要缺点已经在这里介绍过：它们没有考虑 DST（夏令时）。主要优点：简单性带来卓越的性能。考虑 DST 规则会使时区名称相比之下变慢。时区缩写是简单的象征性时间偏移，时区名称受制于一组不断变化的规则。我在 SO 上的这个相关答案中运行了基准测试，差异非常显着。但是当应用于集合时，通常需要使用时区名称来覆盖每行可能不同的 DST 状态（以及历史差异）。

    我们正在谈论CET。真正棘手的部分是“CET”不仅（显然）是一个时区缩写，它也是一个时区名称，至少根据我的安装（PostgreSQL 9.1.6 on Debian Squeeze with locale "de_AT.UTF-8 ") 以及到目前为止我见过的所有其他人。我提到了这些细节，因为 Postgres 使用底层操作系统的区域信息（如果可用）。

    你自己看：

    SELECT * FROM pg_timezone_names WHERE name = 'CET';

SELECT * FROM pg_timezone_abbrevs WHERE abbrev = 'CET';

    SQL小提琴。

    Postgres 选择缩写而不是全名。因此，即使我在time zone names中找到了 CET ，该表达式'2012-01-18 01:00 CET'::timestamptz仍会根据time zone abbreviations的细微不同规则进行解释。

    如果那不是上膛的脚枪，我不知道是什么。

    为避免歧义，请使用时区名称“欧洲/柏林”（或在我的情况下为“欧洲/维也纳”-实际上是相同的，除了历史差异）。在我上面提到的密切相关的问题下找到有关该主题的更多详细信息。

    最后，我想表达我对 DST 的愚蠢概念深表鄙视。它应该从存在中消失，永远不再提及。

    • 6

  3. 检查这个：

    select  
    '2012-10-28 02:30:00+02'::timestamp with time zone at time zone 'Europe/Berlin' as berlin,
    '2012-10-28 02:30:00+02'::timestamp with time zone at time zone 'CET' as cet,
    '2012-10-28 02:30:00+02'::timestamp with time zone at time zone 'CEST' as cest

    +02是柏林的 CEST，而不是 CET。

    • 3

相关问题