Thread: 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.talir@nic.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@enterprisedb.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@enterprisedb.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@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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

Bruce Momjian <bruce@momjian.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