AT TIME ZONE and DST in UTC<->CET conversion

First, if I do conversion from UTC to CET and back when a timestamp is
OUTSIDE of daylight saving period, it's correct:

postgres# select '2008-01-01 10:10:10 UTC' AT TIME ZONE 'CET';
timezone
---------------------
2008-01-01 11:10:10

postgres# select '2008-01-01 11:10:10 CET' AT TIME ZONE 'UTC';
timezone
---------------------
2008-01-01 10:10:10

When I do the same INSIDE this period just the UCT->CET direction is
correct but back direction will fail subtracting only 1 hour instead of
2 hours.

postgres# select '2008-06-01 10:10:10 UTC' AT TIME ZONE 'CET';
timezone
---------------------
2008-06-01 12:10:10

postgres# select '2008-06-01 12:10:10 CET' AT TIME ZONE 'UTC';
timezone
---------------------
2008-06-01 11:10:10

If it supports automatic discovery of DST in conversion from UTC to CET
(and this is great), why it doesn't support this discovery in opposit
direction?

Regards,
Jaromir

Jaromír Talíř <jaromir(dot)talir(at)nic(dot)cz> writes:

> postgres# select '2008-06-01 10:10:10 UTC' AT TIME ZONE 'CET';
> timezone
> ---------------------
> 2008-06-01 12:10:10

ISTM this is the one that's wrong. "CET" is standard time, it, GMT+1.

If you want a timezone which switches between CET and CST automatically you
should use something like Europe/Paris.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> ISTM this is the one that's wrong. "CET" is standard time, it, GMT+1.

> If you want a timezone which switches between CET and CST automatically you
> should use something like Europe/Paris.

Well, actually he *is* using such a zone:

regression=# select * from pg_timezone_names where name = 'CET';
name | abbrev | utc_offset | is_dst
------+--------+------------+--------
CET | CEST | 02:00:00 | t
(1 row)

But

regression=# select * from pg_timezone_abbrevs where abbrev = 'CET';
abbrev | utc_offset | is_dst
--------+------------+--------
CET | 01:00:00 | f
(1 row)

The problem is that one of these two statements is using the abbrev
meaning and the other is using the timezone meaning.

We don't have much control over the zone definition, so I'm thinking
maybe the abbrev should be removed from the tznames lists. But that
seems a bit sucky too. Does anyone have any idea if the zic folk would
be responsive to a complaint that defining a timezone with the same
name as an abbreviation is a bad idea?

regards, tom lane

Tom Lane wrote:
> Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> > ISTM this is the one that's wrong. "CET" is standard time, it, GMT+1.
>
> > If you want a timezone which switches between CET and CST automatically you
> > should use something like Europe/Paris.
>
> Well, actually he *is* using such a zone:
>
> regression=# select * from pg_timezone_names where name = 'CET';
> name | abbrev | utc_offset | is_dst
> ------+--------+------------+--------
> CET | CEST | 02:00:00 | t
> (1 row)
>
> But
>
> regression=# select * from pg_timezone_abbrevs where abbrev = 'CET';
> abbrev | utc_offset | is_dst
> --------+------------+--------
> CET | 01:00:00 | f
> (1 row)
>
> The problem is that one of these two statements is using the abbrev
> meaning and the other is using the timezone meaning.
>
> We don't have much control over the zone definition, so I'm thinking
> maybe the abbrev should be removed from the tznames lists. But that
> seems a bit sucky too. Does anyone have any idea if the zic folk would
> be responsive to a complaint that defining a timezone with the same
> name as an abbreviation is a bad idea?

Is this a TODO?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Tom Lane wrote:
>> The problem is that one of these two statements is using the abbrev
>> meaning and the other is using the timezone meaning.

> Is this a TODO?

We already fixed it:

2008-07-07 14:09 tgl

* src/backend/utils/adt/: date.c, timestamp.c (REL8_1_STABLE),
date.c, timestamp.c (REL8_3_STABLE), date.c, timestamp.c
(REL8_2_STABLE), date.c, timestamp.c: Fix AT TIME ZONE (in all
three variants) so that we first try to interpret the timezone
argument as a timezone abbreviation, and only try it as a full
timezone name if that fails. The zic database has four zones (CET,
EET, MET, WET) that are full daylight-savings zones and yet have
names that are the same as their abbreviations for standard time,
resulting in ambiguity. In the timestamp input functions we
resolve the ambiguity by preferring the abbreviation, and AT TIME
ZONE should work the same way. (No functionality is lost because
the zic database also has other names for these zones, eg
Europe/Zurich.) Per gripe from Jaromir Talir.

Backpatch to 8.1. Older releases did not have the issue because AT
TIME ZONE only accepted abbreviations not zone names. (Thus, this
patch also arguably fixes a compatibility botch introduced at 8.1:
in ambiguous cases we now behave the same as 8.0 did.)

regards, tom lane