How come I default to Antartica in CVS?

test=# show time zone;
      TimeZone
------------------
  Antarctica/Casey
(1 row)

test=#

Chris


Fri, 09 Jul 2004 11:49:08 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Weird new time zone
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> How come I default to Antartica in CVS?

You tell us ... what's your real timezone, and what do you get from
pushing up the log level to DEBUG4 during postmaster start?

			regards, tom lane

Sat, 10 Jul 2004 01:41:04 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Weird new time zone 
> You tell us ... what's your real timezone, and what do you get from
> pushing up the log level to DEBUG4 during postmaster start?

OK, I have log level set to debug4 and australian_timezones set to true. 
   The system time zone is set to WST.

Attached is the startup log.  I should point out that the Casey 
Antarctic base is in the Australian Antarctic Territory and it is in the 
same time zone as Perth, Western Australia for me:

http://times.clari.net.au/location.php3/Antarctica/Casey
http://times.clari.net.au/location.php3/Australia/Perth

So I guess in many ways, PostgreSQL is correct - just...weird...

I should also point out that with australian_timezones set to false, it 
still picks Casey Station in Antarctica.

Chris
Sat, 10 Jul 2004 16:08:19 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Weird new time zone
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> Attached is the startup log.  I should point out that the Casey 
> Antarctic base is in the Australian Antarctic Territory and it is in the 
> same time zone as Perth, Western Australia for me:

Looking in the data files,

Zone Antarctica/Casey	0	-	zzz	1969
			8:00	-	WST	# Western (Aus) Standard Time

Zone Australia/Perth	 7:43:24 -	LMT	1895 Dec
			 8:00	Aus	WST	1943 Jul
			 8:00	-	WST	1974 Oct lastSun 2:00s
			 8:00	1:00	WST	1975 Mar Sun>=1 2:00s
			 8:00	-	WST	1983 Oct lastSun 2:00s
			 8:00	1:00	WST	1984 Mar Sun>=1 2:00s
			 8:00	-	WST	1991 Nov 17 2:00s
			 8:00	1:00	WST	1992 Mar Sun>=1 2:00s
			 8:00	-	WST

which if I recall the notation right says that Casey has never observed
DST (it's always GMT+8 == WST) while Perth has observed DST in just
three years since WWII.  So unless the timezone probing code happens to
check those particular years, we have no chance of distinguishing the
two zones.

Perhaps, rather than just probing a few selected years, we had better
check every year since 1970 ...

			regards, tom lane

Sat, 10 Jul 2004 12:40:21 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Weird new time zone 
On Sat, Jul 10, 2004 at 12:40:21PM -0400, Tom Lane wrote:

> Perhaps, rather than just probing a few selected years, we had better
> check every year since 1970 ...

What if we tell the user what the detected timezone is at some point,
and tell them that it's only a heuristic?  So if somebody gets a wrong
timezone, they can select the correct one in postgresql.conf.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Tulio: oh, para quï¿½ servirï¿½ este boton, Juan Carlos?
Policarpo: No, alï¿½jense, no toquen la consola!
Juan Carlos: Lo apretarï¿½ una y otra vez.


Sat, 10 Jul 2004 15:03:45 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Weird new time zone
Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> On Sat, Jul 10, 2004 at 12:40:21PM -0400, Tom Lane wrote:
>> Perhaps, rather than just probing a few selected years, we had better
>> check every year since 1970 ...

> What if we tell the user what the detected timezone is at some point,
> and tell them that it's only a heuristic?  So if somebody gets a wrong
> timezone, they can select the correct one in postgresql.conf.

That's the ultimate fallback in any case.  But it would be nice if it
"just worked" in as many cases as possible.  (Or should I revert the
hack that made it work for *your* timezone? ;-))

I was initially thinking that probing a large number of test times
would be expensive, but on second thought I don't see that it would
be a problem.  On nearly all the entries in the TZ database, we'd
reject on the first or second probe time anyway; only very near
matches such as in Chris' example would need multiple checks.  We
could probably check every Sunday from 1970-1-1 to current time
without making any visible difference in the speed of postmaster
launch ... and I think we might *have* to do that, if there are any
cases where similar zones differ only in the specific dates of
DST start/end.

			regards, tom lane

Sat, 10 Jul 2004 15:23:31 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Weird new time zone 
I wrote:
> I was initially thinking that probing a large number of test times
> would be expensive, but on second thought I don't see that it would
> be a problem.  On nearly all the entries in the TZ database, we'd
> reject on the first or second probe time anyway; only very near
> matches such as in Chris' example would need multiple checks.

I've committed changes that replace the original "spot check" approach
with consistently checking every week from 1970 to 2004.  This is indeed
not much slower than before (the worst difference I saw was from 7 to 14
milliseconds required for identify_system_timezone to process the same
case, and I think some of that was measurement noise).  This fixes Chris'
Australia/Perth problem at least.

It occurs to me that with a check this thorough, we might be able to
finesse the problem on Windows with the system returning very
nonstandard timezone abbreviations.  That is, we might simply
"#ifndef WIN32" the matching of zone names in try_timezone().
However I do not know whether this would yield reasonable results for
all the zones supported by Windows.  Does anyone want to try it?

			regards, tom lane

Sat, 10 Jul 2004 19:53:06 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Weird new time zone 