12

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
2
  • 1
    It is quite sure that 2012-10-28 01:30:00 is CEST, not CET. Dec 20, 2012 at 7:57
  • 1
    As far as I know CET is not Europe/Berlin - at least not during DST times.
    – user1822
    Dec 20, 2012 at 8:11

3 Answers 3

9

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

0
6

And that's still not the gist of it! I ran into a very similar problem some time ago.

The major cons of time zone abbreviations have 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 (and also 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, at least according to 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';

SQL 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). Find more details on the topic under the closely related question I mentioned above.

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.