Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
1  
It is quite sure that 2012-10-28 01:30:00 is CEST, not CET. –  dezso Dec 20 '12 at 7:57
1  
As far as I know CET is not Europe/Berlin - at least not during DST times. –  a_horse_with_no_name Dec 20 '12 at 8:11
add comment

3 Answers

up vote 3 down vote accepted

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

share|improve this answer
1  
Thanks. Lesson for me: always read whole documentation chapter until you understand everything. –  sufleR Dec 20 '12 at 9:42
add comment

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.

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 PostgreSQL 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';

Same result on ->sqlfiddle.

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.

If that's not a loaded foot gun 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). 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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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