Re: BUG #2062: Timezone unrecognised

On Mon, 21 Nov 2005, Jim C. Nasby wrote:

> On Mon, Nov 21, 2005 at 03:59:39PM +0000, David Begley wrote:
>
> > PostgreSQL documentation (and basic testing with "psql") indicates that for
> > the Australian eastern coast, the timezones "AEST" (UTC+10) and "AESST"
> > (UTC+11) are recognised;  unfortunately the far more popular "AEDT" (UTC+11,
> > "Australian Eastern Daylight-Saving Time") is not recognised.
>
> The issue is that PostgreSQL gets it's timezone data from somewhere
> else; see http://lnk.nu/developer.postgresql.org/69i. Now, why it's
> comming from a machine at the National Cancer Institute I don't know...
> presumably it's a project for someone who works there. You should take a
> look at the comments at the end of
> http://lnk.nu/developer.postgresql.org/69j.

Thanks - but neither of those references mention "AESST" at all (but do
include discussion of "AEDT");  if anything it's further support for my
request to change/update PostgreSQL!  :-)

To be clear we're talking about the same feature, see:

http://www.postgresql.org/docs/8.1/interactive/datetime-keywords.html#DATETIME-TIMEZONE-INPUT-TABLE

At "+11:00" is clearly written:

  AESST  Australian Eastern Summer Standard Time

Quite distinct from what appears in any external timezone database.  One
(messy?) way to test this is with:

SELECT TO_CHAR( TO_NUMBER(
  EXTRACT( TIMEZONE FROM TIME WITH TIME ZONE '07:15:00 xxx' ),
  'S99999' ) / 60 / 60, 'S99' );

Replace "xxx" with "AEST" and you get "+10";  with "AESST" you get "+11", but
with "AEDT" you're left with an error (instead of "+11").

Does this make things clearer?

As for the timezone database and its use of abbreviations - I wouldn't place
too much authority in its contents (the start/stop times may be okay, but the
abbreviations are purely subjective).  Those outside Australia (or any country
for that matter) have no real experience with what terms/abbreviations are
used whilst the few from within a country will all have vested interests (I
remember Elz arguing a particular line of reasoning based on his own local
legislation, completely ignoring other states/territories or common practice).

Thanks..