I need to do some timezone manipulation, and I was wondering about this
difference:

australia=# select version();
                           version
--------------------------------------------------------------
 PostgreSQL 7.1.3 on i386--freebsd4.4, compiled by GCC 2.95.3
(1 row)
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
(1 row)


Why can't I use 'australia/sydney' as a time zone in 'at time zone'
notation?  Has it been fixed in 7.2?

Now, say I do this:

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.

7.2 seems to have the same behaviour...

Chris


Mon, 18 Mar 2002 13:51:19 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Time zone questions
> 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

Mon, 18 Mar 2002 19:49:29 -0800
Thomas Lockhart <lockhart@fourpalms.org>
Re: Time zone questions