> australia=# select '2002-03-18 00:00:00' at time zone 'Australia/Sydney';
> ERROR: Time zone 'australia/sydney' not recognized
> australia=# set time zone 'Australia/Sydney';
> SET VARIABLE
> australia=# select '2002-03-18 00:00:00';
> ?column?
> ---------------------
> 2002-03-18 00:00:00
> Why can't I use 'australia/sydney' as a time zone in 'at time zone'
> notation? Has it been fixed in 7.2?
Not fixed, because not broken ;)
PostgreSQL recognizes specific time zones such as GMT, PST, or, in your
case, EST (is that right? My zinc database on my Linux box seems to
identify both daylight and standard times as "EST").
But for input it only uses the zoneinfo database (or equivalent) if no
time zone is specified. Then it uses the system to obtain the local time
zone.
> select '2002-03-18 00:00:00' at time zone 'AEST';
> That will give me aussie eastern time quite happily, but what if I don't
> know when summer time starts? I don't want to have to manually choose
> between 'AEST' and 'AESST'??? To me, the way to do this would be to use
> 'Australia/Sydney' as the time zone, but this doesn't work.
Right. To do what you suggest is probably *very* expensive, but I
actually haven't tried it to confirm. It could require changing the
default time zone every time a timestamp is evaluated, which would
require file opens/closes, environment variable setting, etc etc.
afaik there is no direct API to access time zone info; if there was we
could more easily think about supporting this.
Presumably you are interested in this for an application where you want
to support multiple time zones. But why is a combination of
SET TIME ZONE 'Australia/Sydney';
and
SELECT '2002-03-18 00:00:00' not adequate for this kind of thing? btw,
SQL9x only specifies numeric time zones, which of course have no concept
of time zone rules at all :(
- Tom
