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

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

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

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.

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

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

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?

I tried this, basically changing to:
#ifndef WIN32
if (strcmp(TZABBREV(cbuf), pgtm->tm_zone) != 0)
{
elog(DEBUG4, "Reject TZ \"%s\": at %ld
\"%s\" versus \"%s\"",
tzname, (long) pgtt,
pgtm->tm_zone, cbuf);
return false;
}
#endif

This is what you meant, rihgt?

It does *not* pick up my timezone. The following possibilities are
rejected per:
DEBUG: Reject TZ "Europe/Stockholm": at 16844400 1970-07-15 00:00:00
std versus 1970-07-15 01:00:00 dst
DEBUG: Reject TZ "CET": at 16844400 1970-07-15 00:00:00 std versus
1970-07-15 01:00:00 dst
DEBUG: Reject TZ "MET": at 16844400 1970-07-15 00:00:00 std versus
1970-07-15 01:00:00 dst
DEBUG: Reject TZ "Etc/GMT-1": at 16844400 1970-07-15 00:00:00 std
versus 1970-07-15 01:00:00 dst

It didn't pick it up before either, but it didn't get better.

Would probably be good if someone where it actally picks up the correct
timezone could test this as well. Or if someone knows a TZ that matches
and I can change my syste mtemporarily. Just don't want to have to test
through each and every timezone..

//Magnus

"Magnus Hagander" <mha@sollentuna.net> writes:

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.

It does *not* pick up my timezone.

Drat. I assume from your domain name that Europe/Stockholm would
actually be the best choice for you? What Windows timezone setting
are you using for this test?

The following possibilities are rejected per:

DEBUG: Reject TZ "Europe/Stockholm": at 16844400 1970-07-15 00:00:00
std versus 1970-07-15 01:00:00 dst

If you look in src/timezone/data/europe you will see that the zic
database thinks Sweden was on strict GMT+1 (no daylight savings) between
1916 and 1980, and since 1980 they were on EU daylight-savings rules.
Does that square with your ideas of reality? (If it does not then we
should just punt the problem upstream to the zic people, but I will
assume here that their research is good.)

What I suspect given the above is that Windows has no clue about
historical reality and is retroactively applying the current DST rules
back to 1970, thus deciding that 1970-07-15 was on DST when it was
really not.

I have seen related problems on my own machine. HPUX 10.20 seems to not
have its facts entirely straight concerning US daylight-savings laws
that were in effect in the 1970s; our current code fails to match
against the system behavior because of this.

I thought about restricting the scope of the TZ testing to start in 1990
or so to avoid this, but that seems certain to fall foul of the other
problem, which is distinguishing closely-related timezones (cf Chris
K-L discovering that he lives in Antarctica, a few days back...)

Maybe the whole match-on-behavior approach is wrong and we need to do
something else, but I'm really unsure what. Ideas?

regards, tom lane

Tom Lane wrote:

I thought about restricting the scope of the TZ testing to start in 1990
or so to avoid this, but that seems certain to fall foul of the other
problem, which is distinguishing closely-related timezones (cf Chris
K-L discovering that he lives in Antarctica, a few days back...)

Maybe the whole match-on-behavior approach is wrong and we need to do
something else, but I'm really unsure what. Ideas?

I threw out the matching idea to Magnus as a crazy idea, and I am
surprised we ended up actually using it, but I can't think of a less
crazy idea either.

If we can't find a timezone match should we start looking only from
+1990?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tom Lane wrote:

I thought about restricting the scope of the TZ testing to start in 1990
or so to avoid this, but that seems certain to fall foul of the other
problem, which is distinguishing closely-related timezones (cf Chris
K-L discovering that he lives in Antarctica, a few days back...)

How about scanning backwards until you have <= 1 choice or decide to
give up?

-O

Oliver Jowett <oliver@opencloud.com> writes:

How about scanning backwards until you have <= 1 choice or decide to
give up?

Hmm ... that really seems like not a bad idea. Scan all the available
timezones, score each on how far back it goes before a mismatch, take
the one that goes furthest back. I'm not sure what to do about ties,
nor what the minimum "passing score" ought to be, but seems like the
germ of an answer. Comments anyone?

regards, tom lane

-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Thursday, July 15, 2004 9:13 PM
To: Oliver Jowett
Cc: Magnus Hagander; Hackers; pgsql-hackers-win32@postgresql.org
Subject: Re: [pgsql-hackers-win32] [HACKERS] Weird new time zone

Oliver Jowett <oliver@opencloud.com> writes:

How about scanning backwards until you have <= 1 choice or decide to
give up?

Hmm ... that really seems like not a bad idea. Scan all the
available timezones, score each on how far back it goes
before a mismatch, take the one that goes furthest back. I'm
not sure what to do about ties, nor what the minimum "passing
score" ought to be, but seems like the germ of an answer.
Comments anyone?

Use the Windows time zone inquiry function:

DWORD GetTimeZoneInformation(LPTIME_ZONE_INFORMATION
lpTimeZoneInformation);

// Where LPTIME_ZONE_INFORMATION is defined as:

typedef struct _TIME_ZONE_INFORMATION
{
LONG Bias;
WCHAR StandardName[ 32 ];
SYSTEMTIME StandardDate;
LONG StandardBias;
WCHAR DaylightName[ 32 ];
SYSTEMTIME DaylightDate;
LONG DaylightBias
} TIME_ZONE_INFORMATION, *PTIME_ZONE_INFORMATION;

The Bias parameter of the TIME_ZONE_INFORMATION structure is the
difference, in minutes, between UTC time and local time.

All translations between UTC time and local time are based on the
following formula:

UTC = local time + bias

"Dann Corbit" <DCorbit@connx.com> writes:

All translations between UTC time and local time are based on the
following formula:

UTC = local time + bias

Surely not. Or has Windows not heard of daylight-savings time?
Or perhaps they have, but are not aware that the DST laws have
changed often in the past?

Over-simplistic answers are not what we need here. The data structure
you quote cannot even tell what DST transition dates Windows thinks
are in effect this year, let alone what it thinks the dates were in past
years.

regards, tom lane

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, July 15, 2004 10:03 PM
To: Dann Corbit
Cc: Oliver Jowett; Magnus Hagander; Hackers;
pgsql-hackers-win32@postgresql.org
Subject: Re: [pgsql-hackers-win32] [HACKERS] Weird new time zone

"Dann Corbit" <DCorbit@connx.com> writes:

All translations between UTC time and local time are based on the
following formula:

UTC = local time + bias

Surely not. Or has Windows not heard of daylight-savings
time? Or perhaps they have, but are not aware that the DST
laws have changed often in the past?

No problems. They even handle time zones with arbitrary minute
boundaries (not on the hour) with aplomb.

Over-simplistic answers are not what we need here. The data
structure you quote cannot even tell what DST transition
dates Windows thinks are in effect this year, let alone what
it thinks the dates were in past years.

Yes, there are other calls for that, obviously. I sent to Mr. Momjian a
complete implementation of time zone stuff that uses Windows calls.
It's also accurate to a fraction of a nanosecond millions of years into
the past and the future.

The call that I showed returns the NAME OF THE TIME ZONE and also what
it is called when you are in Daylight savings time. I thought the issue
under question was to find out what the time zone was.

This program:

#include <windows.h>
#include <iostream>
using namespace std;

int main(void)
{
TIME_ZONE_INFORMATION tz;
DWORD i = GetTimeZoneInformation(&tz);
for (i = 0; i < 32 && tz.StandardName[i]; i++)
cout << (TCHAR) tz.StandardName[i];
cout << endl;
for (i = 0; i < 32 && tz.DaylightName[i]; i++)
cout << (TCHAR) tz.DaylightName[i];
cout << endl;
return 0;
}

Prints this:
Pacific Standard Time
Pacific Daylight Time

There is also a global variable called _tzname that contains the name of
the time zone.
See:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vclib/h
tml/_crt__daylight.2c_._timezone.2c_.and__tzname.asp

On my machine:
cout << _tzname[0]; // -> PST
cout << _tzname[1]; // -> PDT

As far as doing the calculations for time values, do whatever you like
(it's not that difficult either, and the code I send does address all
that stuff, though it is in C++). Don't forget that things are inverted
in the southern hemisphere.

Have a good one.

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.

It does *not* pick up my timezone.

Drat. I assume from your domain name that Europe/Stockholm
would actually be the best choice for you? What Windows
timezone setting are you using for this test?

Yup, that would be the best. Either that or CET/CEST.

I'm using the timezone that in the GUI is called "(GMT +01:00)
Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna". srtftime with %z
calls it "W. Europe Daylight Time".

The following possibilities are rejected per:

DEBUG: Reject TZ "Europe/Stockholm": at 16844400

1970-07-15 00:00:00

std versus 1970-07-15 01:00:00 dst

If you look in src/timezone/data/europe you will see that the
zic database thinks Sweden was on strict GMT+1 (no daylight
savings) between
1916 and 1980, and since 1980 they were on EU daylight-savings rules.
Does that square with your ideas of reality? (If it does not
then we should just punt the problem upstream to the zic
people, but I will assume here that their research is good.)

Actually, I was sure that was wrong, but started looking it up, and it
seems it's about right. See
http://www.sp.se/metrology/timefreq/eng/standard_time.htm for an
official take on the summertime rules. I *think* that is the EU rules.

What I suspect given the above is that Windows has no clue
about historical reality and is retroactively applying the
current DST rules back to 1970, thus deciding that 1970-07-15
was on DST when it was really not.

That could definitly be. Since before 1970 they say there is no DST at
all (remember the old issues), it wouldn't surprise me a bit if they
took this kind of shortcut.

I thought about restricting the scope of the TZ testing to
start in 1990 or so to avoid this, but that seems certain to
fall foul of the other problem, which is distinguishing
closely-related timezones (cf Chris K-L discovering that he
lives in Antarctica, a few days back...)

Maybe the whole match-on-behavior approach is wrong and we
need to do something else, but I'm really unsure what. Ideas?

Well, Windows specific we could do a translate table. SInce there is a
finite (and not *huge*) number of timezones. But we'd have to figure out
for each one which to match. But that won't work for unix I think - the
lookup table would be just huge considwering all possible
combinatinos...

//Magnus

Over-simplistic answers are not what we need here. The

data structure

you quote cannot even tell what DST transition dates Windows thinks
are in effect this year, let alone what it thinks the dates were in
past years.

Yes, there are other calls for that, obviously. I sent to
Mr. Momjian a complete implementation of time zone stuff that
uses Windows calls.
It's also accurate to a fraction of a nanosecond millions of
years into the past and the future.

You wouldn't happen to know if Windows internally has knowledge of the
many different DST rules in force at different years? See the other mail
about how it apparantly deals with Swedish DST - if that's common or a
single case?
If the OS doesn't have that knowledge, we can give up trying to get it
to tell us about it :-)

The call that I showed returns the NAME OF THE TIME ZONE and
also what it is called when you are in Daylight savings time.
I thought the issue under question was to find out what the
time zone was.

Nope, we already had that. The issue is that the names are not the same
as the one used in zic/unix, so there is nothing to match on.

//Magnus

"Magnus Hagander" <mha@sollentuna.net> writes:

I thought the issue under question was to find out what the
time zone was.

Nope, we already had that. The issue is that the names are not the same
as the one used in zic/unix, so there is nothing to match on.

Right. The problem we are actually faced with is to identify which of
the zic timezones is the best match for the system's timezone setting.
One of the issues is that it's not clear what "best" means...

At the moment I like Oliver Jowett's idea of defining "best" as "the one
that matches furthest back".

regards, tom lane

I thought the issue under question was to find out what

the time zone

was.

Nope, we already had that. The issue is that the names are not the
same as the one used in zic/unix, so there is nothing to match on.

Right. The problem we are actually faced with is to identify
which of the zic timezones is the best match for the system's
timezone setting.
One of the issues is that it's not clear what "best" means...

At the moment I like Oliver Jowett's idea of defining "best"
as "the one that matches furthest back".

Sounds reasonable to me. As long as a clear warning is put in the log
whenever something is picked that is not a perfect match, so the admin
is directed at the potential problem and can fix it (by setting the GUC
timezone variable).

"Most apps" are probably going to deal with datetimes starting a couple
of years back and going into the future. In these cases it doesn't even
matter. Certainly not all, though.

//Magnus

"Magnus Hagander" <mha@sollentuna.net> writes:

At the moment I like Oliver Jowett's idea of defining "best"
as "the one that matches furthest back".

Sounds reasonable to me. As long as a clear warning is put in the log
whenever something is picked that is not a perfect match,

Define "perfect match". I do not think we can really tell if we have an
exact match or not; the libc timezone API is just too limited to be
sure. And on many platforms we can be sure we will never have an exact
match, especially if we look at years before 1970.

If you want something in the log I'd be inclined to just always make a
log entry when we infer a timezone setting.

regards, tom lane

"Magnus Hagander" <mha@sollentuna.net> writes:

Right. The problem we are actually faced with is to identify
which of the zic timezones is the best match for the system's
timezone setting.
One of the issues is that it's not clear what "best" means...

At the moment I like Oliver Jowett's idea of defining "best"
as "the one that matches furthest back".

Sounds reasonable to me. As long as a clear warning is put in the log
whenever something is picked that is not a perfect match, so the admin
is directed at the potential problem and can fix it (by setting the GUC
timezone variable).

I'm not sure that a log entry is needed --- SHOW TIMEZONE will make it
perfectly clear what zone was selected.

But in any case, I've committed code that implements Oliver's idea.
Could folks take another swipe at it and see if it works well in their
local zones? Also, it'd still be interesting to see if we could #ifdef
out the matching on zone names for Windows and still get reasonable
results.

regards, tom lane

Tom Lane wrote:

But in any case, I've committed code that implements Oliver's idea.
Could folks take another swipe at it and see if it works well in their
local zones? Also, it'd still be interesting to see if we could #ifdef
out the matching on zone names for Windows and still get reasonable
results.

Sadly, it now thinks I live a bit further south than I actually do :)

template1=# show timezone;
TimeZone
--------------------
Antarctica/McMurdo
(1 row)

The only timezones that get positive scores during startup are:

DEBUG: TZ "Antarctica/McMurdo" gets max score 2080
DEBUG: TZ "Antarctica/South_Pole" gets max score 2080
DEBUG: TZ "Pacific/Auckland" gets max score 2080
DEBUG: TZ "NZ" gets max score 2080

Either of "NZ" or "Pacific/Auckland" would be correct.

From memory it picked Pacific/Auckland with the slightly older code.

-O

Oliver Jowett wrote:

The only timezones that get positive scores during startup are:

DEBUG: TZ "Antarctica/McMurdo" gets max score 2080
DEBUG: TZ "Antarctica/South_Pole" gets max score 2080
DEBUG: TZ "Pacific/Auckland" gets max score 2080
DEBUG: TZ "NZ" gets max score 2080

Either of "NZ" or "Pacific/Auckland" would be correct.

Looking in the timezone data files, it appears that those two Antarctica
timezones are identical to the NZ ones back to 1956. The CVS code only
scans back to 1964.

Increasing MAX_TEST_TIMES to scan back 50 years produces this:

DEBUG: TZ "Antarctica/McMurdo" scores 2534: at -442152000 1955-12-28 12:00:00 std versus 1955-12-29 00:00:00 std
DEBUG: TZ "Antarctica/South_Pole" scores 2534: at -442152000 1955-12-28 12:00:00 std versus 1955-12-29 00:00:00 std
DEBUG: TZ "Pacific/Auckland" gets max score 2600
DEBUG: TZ "NZ" gets max score 2600

and it picks Pacific/Auckland.

Also I'm a bit nervous about that hardcoded 2004 start date for the scan
in pgtz.c -- that will presumably break if the timezone data files are
updated for post-2004 changes without a corresponding change to the scan
code. Would it make sense to scan backwards from the current system time
to a predetermined year?

-O

DEBUG: TZ "Antarctica/McMurdo" scores 2534: at -442152000

1955-12-28 12:00:00 std versus 1955-12-29 00:00:00 std

DEBUG: TZ "Antarctica/South_Pole" scores 2534: at

-442152000 1955-12-28 12:00:00 std versus 1955-12-29 00:00:00 std

DEBUG: TZ "Pacific/Auckland" gets max score 2600
DEBUG: TZ "NZ" gets max score 2600

and it picks Pacific/Auckland.

Also I'm a bit nervous about that hardcoded 2004 start date for the scan
in pgtz.c -- that will presumably break if the timezone data files are
updated for post-2004 changes without a corresponding change to the scan

Yes, how about scanning forward iff it is still a breakeven ?

Andreas

Oliver Jowett <oliver@opencloud.com> writes:

Also I'm a bit nervous about that hardcoded 2004 start date for the scan
in pgtz.c -- that will presumably break if the timezone data files are
updated for post-2004 changes without a corresponding change to the scan
code.

Actually, that was intentional. My thought was that if it picks the
right zone now, while we are testing it, it will continue to pick the
right zone in future. Let's suppose we do that, and then Congress
decides to fool with the US' DST laws again in 2009. The zic people
will update their database, we will propagate that upstream change, and
identify_system_timezone will immediately fail on any machine with an
un-updated local timezone database. Now I suppose the owners of such
machines would have good reason to update their libc databases soon
... but the point is that probing future time exposes us to risks from
unforeseeable future changes, and I don't see that it gives any
advantages.

The Antarctica/McMurdo business is more interesting. I am not sure
why it picks McMurdo today when it picked Auckland before, since the
previous code certainly didn't scan backwards far enough to distinguish
those zones either. I would have thought that you'd get the first
exact match with the old code, and if the scan order is consistent
that would be McMurdo. Possibly there's some phase-of-the-moon behavior
involved in the scan order. Have you reinstalled PG without wiping the
installation directories first? If the TZ files are installed by
overwriting an existing tree, I can believe that the live directory
entries would end up in a different physical order each time you do it.

In general though, the zic database has a lot of duplicate and
near-duplicate zone entries, and I'm not sure we can hope to pick one
that the user will think is his local zone when there are several
perfect matches. (This morning I was trying to think of a way of
at least not running the calculations over again for each of several
perfect duplicates, but AFAICS we'd have to rely on noticing multiple
hard links, which would be awfully platform-dependent not to say
fragile...)

regards, tom lane

Tom Lane wrote:

Oliver Jowett <oliver@opencloud.com> writes:

Also I'm a bit nervous about that hardcoded 2004 start date for the scan
in pgtz.c -- that will presumably break if the timezone data files are
updated for post-2004 changes without a corresponding change to the scan
code.

Actually, that was intentional. My thought was that if it picks the
right zone now, while we are testing it, it will continue to pick the
right zone in future. Let's suppose we do that, and then Congress
decides to fool with the US' DST laws again in 2009. The zic people
will update their database, we will propagate that upstream change, and
identify_system_timezone will immediately fail on any machine with an
un-updated local timezone database. Now I suppose the owners of such
machines would have good reason to update their libc databases soon
... but the point is that probing future time exposes us to risks from
unforeseeable future changes, and I don't see that it gives any
advantages.

It occurs to me that we should be able to automatically find a range of
dates to scan that will distinguish between all non-identical timezones
in our timezone database, and then plug the results into the scan code.

That's simplify the upgrading of timezone data anyway.. otherwise some
guesswork or hand inspection of the new data would be needed to work out
if we need to move the scan start point to be able to distinguish new
timezones.

The Antarctica/McMurdo business is more interesting. I am not sure
why it picks McMurdo today when it picked Auckland before, since the
previous code certainly didn't scan backwards far enough to distinguish
those zones either. I would have thought that you'd get the first
exact match with the old code, and if the scan order is consistent
that would be McMurdo. Possibly there's some phase-of-the-moon behavior
involved in the scan order. Have you reinstalled PG without wiping the
installation directories first? If the TZ files are installed by
overwriting an existing tree, I can believe that the live directory
entries would end up in a different physical order each time you do it.

The install I tested on was a freshly checked out CVS tree installing
into a clean directory.

The older install had been updated and reinstalled a few times, so it's
possible it just happened to find Auckland first because of a different
ordering in the directory.

It'd be nice to have a predictable timezone choice made when there's a
tie. Perhaps we should order on timezone name in this case?

-O

Oliver Jowett <oliver@opencloud.com> writes:

It'd be nice to have a predictable timezone choice made when there's a
tie. Perhaps we should order on timezone name in this case?

The Antarctica zones would tend to win with such a rule, which is
probably not what we want :-(. But certainly we could do something
with ties beyond "take the first one in scan order".

Would "shortest name wins" help any?

regards, tom lane

Tom Lane wrote:

Oliver Jowett <oliver@opencloud.com> writes:

It'd be nice to have a predictable timezone choice made when there's a
tie. Perhaps we should order on timezone name in this case?

The Antarctica zones would tend to win with such a rule, which is
probably not what we want :-(. But certainly we could do something
with ties beyond "take the first one in scan order".

Would "shortest name wins" help any?

Well, I was thinking about the NZ vs. Pacific/Auckland tie actually. We
already have a way to avoid picking Antarctica -- scan further back.

-O