Thread: BUG #2062: Timezone unrecognised
The following bug has been logged online: Bug reference: 2062 Logged by: David Begley Email address: d.begley@uws.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:KMjjE-jc8MQJ:www.postgresql.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/averages/tables/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.cfm?event=object.showContent&objectID=9B D77455-BCD6-81AC-1BF1C5D980A48E4D Could we please have "AEDT" finally added in the next release? Thanks... :-)
On Mon, Nov 21, 2005 at 03:59:39PM +0000, David Begley wrote: > > The following bug has been logged online: > > Bug reference: 2062 > Logged by: David Begley > Email address: d.begley@uws.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). 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@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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..