From: Thomas Lockhart <lockhart@fourpalms.org>
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Cc: Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Time zone questions
Date: Mon, 18 Mar 2002 19:49:29 -0800
Message-ID: <3C96B549.4A03707B@fourpalms.org> (raw)
References: <GNELIHDDFBOCMGBFGEFOGEMICBAA.chriskl@familyhealth.com.au>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: lockhart@fourpalms.org, chriskl@familyhealth.com.au
Subject: Re: Time zone questions
In-Reply-To: <3C96B549.4A03707B@fourpalms.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox