Time zone names with identical properties yield different result when applied to timestamp

Viewed 2669

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 timezone setting defaults to the system locale de_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.

2 Answers
Related