> 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 ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly