The following bug has been logged online:

Bug reference: 2062
Logged by: David Begley
Email address: d.begley (AT) uws (DOT) edu.au
PostgreSQL version: 8.1
Operating system: Windows
Description: Timezone unrecognised
Details:

Sorry this is probably going to be a "PITA" issue.

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.

I don't know where the idea that "AESST" is popular in Australia originated
- a Google for "aesst +site:.au" versus "aedt +site:.au" shows 432 hits for
"AESST" and over 1.3 million for "AEDT". Even limiting the search to
".gov.au" is strongly in favour of "AEDT" (93 versus over 38,000).

Again, a Google on past versions of PostgreSQL (you'll need to use Google's
cache for this):

http://66.102.7.104/search?q=cache:K....dk/docs/7.2/i
nteractive/timezones.html+postgresql+aedt&hl=en

shows that the request for "AEDT" has existed since at least 2002 and
PostgreSQL 7.2.

I know it's not a major issue for anyone else but it sure makes things
easier for those of us who actually _use_ and _live_ within those timezones
(especially given that I'd never seen/heard of "AESST" 'til PostgreSQL) - if
it didn't matter, then recognition of the various timezone strings wouldn't
exist in PostgreSQL at all.

Before you ask, there exists no formal legislated standard for timezone
names or short-names within Australia (at least, not for use outside the
legislation itself defining when daylight savings starts/ends), it's all
governed by "local practice" (hence, look at the Google numbers above); the
politicians are too busy screwing around with whether or not to have
daylight savings at all and when to start or finish daylight savings.

One last pitch in favour of "AEDT" (for that manner, any/all corrected
Australian timezones):

http://www.australia.gov.au/about-australia-13time
http://www.bom.gov.au/climate/averag...daysavtm.shtml

Even the people operating Australia's contribution to UTC use the term,
"Australian Eastern Daylight Time" (see the note about the upcoming leap
second):

http://www.measurement.gov.au/index....nt&objectID=9B
D77455-BCD6-81AC-1BF1C5D980A48E4D

Could we please have "AEDT" finally added in the next release?

Thanks... :-)

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Mon, Nov 21, 2005 at 03:59:39PM +0000, David Begley wrote:
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.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby (AT) pervasive (DOT) com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

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/i...NE-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..

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org