"AT TIME ZONE" with zone name PostgreSQL bug?

Question

I was answering this stackoverflow question and found strange result:

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

and next query

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

I'm using PostgreSQL 9.1.2 and ubuntu 12.04.
Just checked that on 8.2.11 result is the same.

According to documentation it doesn't matter if I use name or abbreviation.

Is this a bug?
Am I doing something wrong?
Can someone explain this result?

EDIT For the comment that CET is not Europe/Berlin.

I'm just selecting values from pg_timezone_names.

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

and

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
 .....

During winter Europe/Berlin is +01. During summer it is +02.

EDIT2 In 2012-10-28 timezone has change from summer time to winter time at 2:00.
This two records have the same value in Europe/Berlin:

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

This suggest that if I use one of abbreviations (CET or CEST) for big data range (summer time and winter time) result will be wrong for some of records. Will be good if I use 'Europe/Berlin'.

I changed the system time to '2012-01-17' and pg_timezone_names has changed also.

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

Answer 1

Actually, the documentation says clearly that the time zone name and abbreviation will behave differently.

In short, this is the difference between abbreviations and full names: abbreviations always represent a fixed offset from UTC, whereas most of the full names imply a local daylight-savings time rule, and so have two possible UTC offsets. Reference

FWIW, that same reference also says

We do not recommend using the type time with time zone (though it is supported by PostgreSQL for legacy applications and for compliance with the SQL standard).

Answer 2

I ran into a very similar problem some time ago.

The major con of time zone abbreviations has been presented here already: they do not take DST (daylight saving time) into account. The major pro: simplicity resulting in superior performance. Taking DST rules into account makes time zone names slow in comparison. Time zone abbreviations are simple, symbolic time offsets, time zone names are subject to a constantly changing set of rules. I ran benchmarks in this related answer on SO, the difference is remarkable. But when applied to a set, it's typically necessary to use time zone names to cover possibly different DST status per row (including historic differences).

We are talking about CET. The really tricky part is that "CET" is not only (obviously) a time zone abbreviation, it is also a time zone name in my installation (PostgreSQL 9.1.6 on Debian Squeeze with locale "de_AT.UTF-8") and all others I have seen so far. I mention these details, because Postgres uses the locale information of the underlying OS if available.

See for yourself:

SELECT * FROM pg_timezone_names WHERE name = 'CET';
SELECT * FROM pg_timezone_abbrevs WHERE abbrev = 'CET';

test=> SELECT 'winter time' AS season
test->      , '2012-01-18 01:00 Europe/Vienna'::timestamptz AT TIME ZONE 'UTC' AS proper
test->      , '2012-01-18 01:00 CET'::timestamptz AT TIME ZONE 'UTC' AS fails_half_of_the_year
test-> UNION ALL
test-> SELECT 'summer time'
test->      , '2012-07-18 02:00 Europe/Vienna'::timestamptz AT TIME ZONE 'UTC'
test->      , '2012-07-18 02:00 CET'::timestamptz AT TIME ZONE 'UTC'
test-> ;
   season    |       proper        | fails_half_of_the_year 
-------------+---------------------+------------------------
 winter time | 2012-01-18 00:00:00 | 2012-01-18 00:00:00
 summer time | 2012-07-18 00:00:00 | 2012-07-18 01:00:00

fiddle

Postgres picks the abbreviation over the full name. So, even though I found CET in the time zone names, the expression '2012-01-18 01:00 CET'::timestamptz is interpreted according to the subtly different rules for time zone abbreviations.

If that's not a loaded footgun I don't know what is.

To avoid ambiguities, go with the time zone name 'Europe/Berlin' (or 'Europe/Vienna' in my case - which is effectively the same, except for historic differences)

In closing, I would like to voice my deeply felt contempt for the moronic, dishonest concept of DST. It should be removed from existence and never spoken of again.

Answer 3

Check this:

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 is CEST in Berlin, not CET.