Thread: More time zones
I've added a fairly large number of time zones to the date/time parser.
They were originally mentioned in my zinc time zone database but without
sufficient information to actually enable them. Ruslan gave me a
reference to a web site with more time zone details so I was able to
fill in quite a few of them.
I've committed the changes to the development tree, and to the stable
tree also.
Details below. Comments and complaints welcome ;)
- Thomas
There are newly recognized time zones:
AMST AMT ANAST ANAT ART AZOST AZOT AZST AZT BNT BORT BOT BRA BTT
CHADT CHAST CKT CLST CLT COT CVT DAVT DDUT EEST EGST EGT
FJST FJT FKST FKT GALT GAMT GEST GET GFT GILT GYT
HKT ICT IRKST IRKT IRT JAVT JAYT KDT KGST KGT KOST KRAST KRAT
LHDT LHST LINT LKT MAGST MAGT MART MMT MPT MSD MSK MYT NCT
NOVST NOVT NUT OMSST OMST
PET PETST PETT PGT PHOT PHT PKT PMDT PONT PWT PYST PYT
THAT TJT TKT TMT TOT TRUK TVT ULAST ULAT UYST UYT UZST UZT
VET VLAST VLAT VUT WAKT WEST WFT WGST WGT WST
YAKST YAKT YAPT YEKST YEKT
There are a few with multiple definitions for which I chose one (the
alternates are in parens):
BST British (Brazil, Bering)
CCT China Coast (Cocos)
EAST Easter Island (Indian Antananarivo Savings Time)
EAT East Africa (Indian Antananarivo Time)
And a few others with multiple definitions for which I did not make a
choice and left all unrecognized:
AST Atlantic, Arabia, Acre
ECT
IDT Israeli, Iran, Indian Daylight Time
This is related to the Australian timezone duplication where we have a GUC parameter. Could we generalize the GUC stuff to allow arbitrary timezone mappings? Just an idea. Not sure it is worth it. The Australians complained but I have not hear complaints from other countries. --------------------------------------------------------------------------- Thomas Lockhart wrote: > I've added a fairly large number of time zones to the date/time parser. > They were originally mentioned in my zinc time zone database but without > sufficient information to actually enable them. Ruslan gave me a > reference to a web site with more time zone details so I was able to > fill in quite a few of them. > > I've committed the changes to the development tree, and to the stable > tree also. > > Details below. Comments and complaints welcome ;) > > - Thomas > > There are newly recognized time zones: > > AMST AMT ANAST ANAT ART AZOST AZOT AZST AZT BNT BORT BOT BRA BTT > CHADT CHAST CKT CLST CLT COT CVT DAVT DDUT EEST EGST EGT > FJST FJT FKST FKT GALT GAMT GEST GET GFT GILT GYT > HKT ICT IRKST IRKT IRT JAVT JAYT KDT KGST KGT KOST KRAST KRAT > LHDT LHST LINT LKT MAGST MAGT MART MMT MPT MSD MSK MYT NCT > NOVST NOVT NUT OMSST OMST > PET PETST PETT PGT PHOT PHT PKT PMDT PONT PWT PYST PYT > THAT TJT TKT TMT TOT TRUK TVT ULAST ULAT UYST UYT UZST UZT > VET VLAST VLAT VUT WAKT WEST WFT WGST WGT WST > YAKST YAKT YAPT YEKST YEKT > > There are a few with multiple definitions for which I chose one (the > alternates are in parens): > > BST British (Brazil, Bering) > CCT China Coast (Cocos) > EAST Easter Island (Indian Antananarivo Savings Time) > EAT East Africa (Indian Antananarivo Time) > > And a few others with multiple definitions for which I did not make a > choice and left all unrecognized: > > AST Atlantic, Arabia, Acre > ECT > IDT Israeli, Iran, Indian Daylight Time > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian writes: > This is related to the Australian timezone duplication where we have a > GUC parameter. Could we generalize the GUC stuff to allow arbitrary > timezone mappings? Just an idea. Not sure it is worth it. The > Australians complained but I have not hear complaints from other > countries. I claim that most of the time zone abbreviations that we have now are purely theoretical fabrications. What we need to think about is a system that is * customizable * internationalized * consistent * real The current system is none of these things. -- Peter Eisentraut peter_e@gmx.net
> This is related to the Australian timezone duplication where we have a
> GUC parameter. Could we generalize the GUC stuff to allow arbitrary
> timezone mappings? Just an idea. Not sure it is worth it. The
> Australians complained but I have not hear complaints from other
> countries.
I don't recall that "the Australians complained". Did their consulate
call you? ;)
What was the issue? Was the previous compile-time behavior preferable to
some?
In any case, we do have to understand how to support areas which have
conflicting definitions for character time zones, so if there are
problems we need to be specific or be satisfied with the result.
- Thomas
Thomas Lockhart wrote: > > This is related to the Australian timezone duplication where we have a > > GUC parameter. Could we generalize the GUC stuff to allow arbitrary > > timezone mappings? Just an idea. Not sure it is worth it. The > > Australians complained but I have not hear complaints from other > > countries. > > I don't recall that "the Australians complained". Did their consulate > call you? ;) > > What was the issue? Was the previous compile-time behavior preferable to > some? No, the Australians complained about the compile-time flag, and were very happy when it became a GUC parameter. I only mentioned to say that conflicting timezone issues are obviously a problem for some, and we did resolve it with GUC. > In any case, we do have to understand how to support areas which have > conflicting definitions for character time zones, so if there are > problems we need to be specific or be satisfied with the result. Yes, Australia is the only group to have complained, and I think they are happy with GUC. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> I claim that most of the time zone abbreviations that we have now are
> purely theoretical fabrications.
Hmm. How would you arrive at that conclusion? Most of the existing
entries appear in the zinc database and in other references, and I'll
assume that most of the places which these correspond to actually exist.
I've only been to some of them ;)
> What we need to think about is a system that is
> * customizable
> * internationalized
> * consistent
> * real
> The current system is none of these things.
Please give a comparison of "theoretical" vs the above. I'll agree that
extensible support would be nice, but would we be willing to look this
stuff up from system tables? If so, I'm happy to implement it. Anyone
have an opinion on what kind of hit we would see?
The same would be true, to some extent, for the internationalization
aspects of this, with of course the usual performance concerns about
cache misses and multiple lookups required to match tokens.
- Thomas
> No, the Australians complained about the compile-time flag, and were > very happy when it became a GUC parameter. I only mentioned to say that > conflicting timezone issues are obviously a problem for some, and we did > resolve it with GUC. OK, I've just realised I haven't set our server to use Australian timezones - how do you do this with GUC? Can I put something in postgresql.conf? I don't want to issue an extra query for every connection to set it. I had a look at the timezone section of the manual and didn't see anything about it...? Thanks, Chris
Christopher Kings-Lynne wrote: > > No, the Australians complained about the compile-time flag, and were > > very happy when it became a GUC parameter. I only mentioned to say that > > conflicting timezone issues are obviously a problem for some, and we did > > resolve it with GUC. > > OK, I've just realised I haven't set our server to use Australian > timezones - how do you do this with GUC? Can I put something in > postgresql.conf? I don't want to issue an extra query for every connection > to set it. I had a look at the timezone section of the manual and didn't > see anything about it...? Hmm, let's see... Hmm, I don't see anything. Maybe it isn't there. Oh, there it is: #australian_timezones = false Is that it? I think so. I am not 100% sure. Yes, I think that is it. :-) Smiley's all around. :-) -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> Hmm, let's see... Hmm, I don't see anything. Maybe it isn't there. Oh, > there it is: > > #australian_timezones = false > > Is that it? I think so. I am not 100% sure. Yes, I think that is it. > > :-) > > Smiley's all around. :-) OK, enough sarcasm :) It's not there in the 7.1.3 postgresql.conf - is it 7.2 only? Chris
Bruce Momjian wrote: > Christopher Kings-Lynne wrote: > > > No, the Australians complained about the compile-time flag, and were > > > very happy when it became a GUC parameter. I only mentioned to say that > > > conflicting timezone issues are obviously a problem for some, and we did > > > resolve it with GUC. > > > > OK, I've just realised I haven't set our server to use Australian > > timezones - how do you do this with GUC? Can I put something in > > postgresql.conf? I don't want to issue an extra query for every connection > > to set it. I had a look at the timezone section of the manual and didn't > > see anything about it...? > > Hmm, let's see... Hmm, I don't see anything. Maybe it isn't there. Oh, > there it is: > > #australian_timezones = false > > Is that it? I think so. I am not 100% sure. Yes, I think that is it. > > :-) > > Smiley's all around. :-) I am sorry Christopher. I just couldn't help myself. Yes, it is australian_timezones in postgresql.conf. No need to set it for every client. Make sure you remove the # comment character and set it to true. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Christopher Kings-Lynne wrote: > > Hmm, let's see... Hmm, I don't see anything. Maybe it isn't there. Oh, > > there it is: > > > > #australian_timezones = false > > > > Is that it? I think so. I am not 100% sure. Yes, I think that is it. > > > > :-) > > > > Smiley's all around. :-) > > OK, enough sarcasm :) It's not there in the 7.1.3 postgresql.conf - is it > 7.2 only? Oh, now that you mention it, I see it in the 7.2 HISTORY so I must have done it only in 7.2. (Seems like so long ago.) So, yes, you were right, it wasn't even in there for 7.1.3. Oh, bad attempt at a joke. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Thomas Lockhart writes: > > I claim that most of the time zone abbreviations that we have now are > > purely theoretical fabrications. > > Hmm. How would you arrive at that conclusion? Some of the entries are obviously bogus, for example BRA "Brazil Time", although Brazil has three time zones. Also, there are only half a dozen or so countries in the world that have more than one time zone, and quite a few countries don't speak English, so it seems unlikely that in a place like that a common-usage English-language time zone designation would evolve. So for instance, FWT "French Winter Time" is highly unlikely to be in actual use. > Most of the existing entries appear in the zinc database and in other > references, and I'll assume that most of the places which these > correspond to actually exist. The tz database contains this note: """ Alphabetic time zone abbreviations should not be used as unique identifiers for UTC offsets as they are ambiguous in practice. For example, "EST" denotes 5 hours behind UTC in English-speaking North America, but it denotes 10 or 11 hours ahead of UTC in Australia; and French-speaking North Americans prefer "HNE" to "EST". For compatibility with POSIX the tz database contains English abbreviations for all time stamps but in many cases these are merely inventions of the database maintainers. """ So what this means is that the tz database (which I assume is the same as the zinc you speak of) merely assigns some string to each distinct set of rules so that POSIX systems have something to put into their tzname variable. One concern I have about the time zone abbreviation database is that the inflation of possible choices makes any kind of error checking mostly non-existant. And who is going to remember all the abbreviations anyway, even if they were documented? I think we should scrap about 90% of the time zone names we have in there unless there's evidence of actual use. Instead, it would be preferrable to use numerical offsets, or if some geographical association is desired, the Continent/City syntax. -- Peter Eisentraut peter_e@gmx.net
Couldn't find these particular inconsistancy in the archives. If this has already appeared, sorry. 1) Casting with and without timezones: =# SELECT time 'now'; now ------------------ 21:31:33.9210-08 (1 row) Works. =# SELECT CURRENT_TIME::time; now ------------------ 21:31:33.9210-08 (1 row) Same. =# SELECT now()::time; ERROR: Cannot cast type 'timestamp with time zone' to 'time without time zone' Hrmm... Inconsistant =# SELECT now()::timetz; now ------------------ 21:31:33.9210-08 (1 row) That of course works. SELECT now()::date; now ------------ 2002-02-25 (1 row) Again works. I understand why it's not implicitly casting, but if tz can be ignored in one it should exhibit the same behavoir for all. 2) Another inconsistancy is timestamp default to having a timezone, while time does not, and dates not even having a date with timezone option: ::time != ::timetz ::timestamp == ::timestamptz ::date ?? Dates are just as effected by time zones as time is. The only way to cast to timestamp without tz is CAST( ... without time zone) as far as I can tell; as ::timestamp(p) doesn't work nor the w/ w/o tz in that style of casting. Among other fun new timestamp changes ;)
Peter Eisentraut <peter_e@gmx.net> writes:
> One concern I have about the time zone abbreviation database is that the
> inflation of possible choices makes any kind of error checking mostly
> non-existant.
That's overstated, but it's a good point nonetheless. We had a report
of one missing timezone entry (or was it two?). Not sure that that
should prompt us to add a hundred entries of dubious validity to the
keyword list.
Perhaps we could find some middle ground between a hard-wired list and
looking up TZ entries in a fully dynamic system table. The list of
known timezones seems like something we could read from a flat text
configuration file at startup --- wouldn't that be enough
configurability?
regards, tom lane
> That's overstated, but it's a good point nonetheless. We had a report
> of one missing timezone entry (or was it two?). Not sure that that
> should prompt us to add a hundred entries of dubious validity to the
> keyword list.
No one is asking you to add those entries; someone is doing that for
you. Dubious? I've been to some of the places I just updated, and know
for a fact that others exist. And entering something for Easter Island
may get us a lock on the entire Easter Island database community. Same
for all of those other South Pacific Islands. Maybe they'll be so happy
that they'll send plane tickets ;)
> Perhaps we could find some middle ground between a hard-wired list and
> looking up TZ entries in a fully dynamic system table. The list of
> known timezones seems like something we could read from a flat text
> configuration file at startup --- wouldn't that be enough
> configurability?
Certainly possible. At the moment the algorithm uses a binary search
through a fixed size array of choices; reading from a table would
require changes to a linked list or something else which is not quite as
fast.
I'm not sure I fully understand the objections to supporting the current
system until something else demonstrably superior is available. istm
that a database table lookup is the way to internationalize and extend
this area, rather than moving to the "middle ground" of static tables
read at startup. btw, the binary search currently used means that the
cost of having new data is low; I did not double the size of the list
from before.
- Thomas
Thomas Lockhart <lockhart@fourpalms.org> writes:
> Certainly possible. At the moment the algorithm uses a binary search
> through a fixed size array of choices; reading from a table would
> require changes to a linked list or something else which is not quite as
> fast.
Binary search doesn't depend on a fixed size table. How about:
1. At startup, read & parse TZ config file; build array and sort it.
2. During keyword lookup, first binary-search the array of fixed
keywords. If no match, binary-search the TZ array.
(This assumes that TZ names are not allowed to pre-empt other names,
such as month names. In some situations the parser might know that
a TZ name is expected, in which case it could go to the TZ array only,
thus allowing conflicting names to be resolved.)
> istm that a database table lookup is the way to internationalize and
> extend this area,
Um ... in the previous paragraph you were complaining about the
performance hit of doing a linear search. Which are you more concerned
about, speed or instant configurability?
This is by no means meant as an attack on the current implementation,
just a thought that we might be reaching its limits. If the Australians
want a configurable set of timezone names, why won't other areas?
regards, tom lane
...
> I understand why it's not implicitly casting, but if tz can be ignored in
> one it should exhibit the same behavoir for all.
Yes, the explicit cast is missing; thanks for pointing that out. You can
define one as follows:
thomas=# create function "time"(timestamp)
thomas-# returns time as
thomas-# 'select cast(cast($1 as timestamp without time zone) as time);'
thomas-# language 'sql';
thomas=# select cast(now() as time); now
-----------------19:43:48.736863
> 2) Another inconsistancy is timestamp default to having a timezone, while
> time does not, and dates not even having a date with timezone option:
> ::time != ::timetz
> ::timestamp == ::timestamptz
> ::date ??
The SQL99 standard says that timestamp should default to having no time
zone. But historically PgSQL had a "zoneful timestamp", and only in the
latest version do we have both. So the default was kept as-is for this
release to help with upgrades from previous versions. The default will
likely change for 7.3.
SQL9x calls for timestamp with and without timezone, time with and
without time zone, and date with no time zone. istm that time with time
zone is useless, but we offer it to provide compatibility with
standards. date with time zone would likely be pretty useless also. We
try to conform to the standard in most cases, with some exceptions due
to standards brain damage. I usually recommend using timestamp with time
zone for most things.
> Dates are just as effected by time zones as time is. The only way to cast to
> timestamp without tz is CAST( ... without time zone) as far as I can tell;
> as ::timestamp(p) doesn't work nor the w/ w/o tz in that style of casting.
Right. The double-colon syntax is an ancient PgSQL-ism. Use cast()
instead to get the greatest variety of syntax in coersions.
> Among other fun new timestamp changes ;)
Enjoy ;)
- Thomas
Thomas Lockhart <lockhart@fourpalms.org> writes:
>> ... The only way to cast to
>> timestamp without tz is CAST( ... without time zone) as far as I can tell;
>> as ::timestamp(p) doesn't work nor the w/ w/o tz in that style of casting.
> Right. The double-colon syntax is an ancient PgSQL-ism. Use cast()
> instead to get the greatest variety of syntax in coersions.
AFAICT, double-colon accepts exactly the same range of type names as CAST
does. For example:
regression=# select 'now'::timestamp(3); timestamptz
----------------------------2002-02-25 22:58:44.796-05
(1 row)
regression=# select 'now'::timestamp(3) with time zone; timestamptz
----------------------------2002-02-25 23:00:04.235-05
(1 row)
regression=# select 'now'::timestamp(3) without time zone; timestamp
-------------------------2002-02-25 23:00:08.075
(1 row)
regression=# select 'now'::"timestamp"; timestamp
----------------------------2002-02-25 23:00:17.104784
(1 row)
regression=# select 'now'::"timestamptz"; timestamptz
-------------------------------2002-02-25 23:00:19.644908-05
(1 row)
>> Among other fun new timestamp changes ;)
> Enjoy ;)
Yup, we've got some. But hey, SQL99 compliance is good for you ;-)
regards, tom lane
> Binary search doesn't depend on a fixed size table. How about:
> 1. At startup, read & parse TZ config file; build array and sort it.
OK, we could read into a linked list, then transform to a fixed-width
table if nothing else. We'd still get the fast binary search
implementation.
> 2. During keyword lookup, first binary-search the array of fixed
> keywords. If no match, binary-search the TZ array.
> (This assumes that TZ names are not allowed to pre-empt other names,
> such as month names. In some situations the parser might know that
> a TZ name is expected, in which case it could go to the TZ array only,
> thus allowing conflicting names to be resolved.)
Hmm. We'd like to internationalize at the same time, so we may as well
figure out how to hold the same kinds of tokens we have now. And we'd
want to override the built-in entries, so we'd have to do the match on
the external info first, not second.
> > istm that a database table lookup is the way to internationalize and
> > extend this area,
> Um ... in the previous paragraph you were complaining about the
> performance hit of doing a linear search. Which are you more concerned
> about, speed or instant configurability?
Whose complaining? I'm just bringing up the issues, since *someone* is
going to complain no matter what is done. We are going for a consensus
here, even if it is a long road :)
Database table lookups are cached (or can be) to some extent afaik, so
maybe in practice even full database lookups would be relatively cheap
for most applications since they would *tend* to sit on one or a few
time zones.
> This is by no means meant as an attack on the current implementation,
> just a thought that we might be reaching its limits. If the Australians
> want a configurable set of timezone names, why won't other areas?
Australians are the most noticable contingent, having afaik the largest
number of time zones per country in the world. Tuvalu probably wins the
prize for highest number of time zones per-capita; afaicr they have
~6000 residents.
Some other countries have quite a few time zones; Russia had several new
entries in this last update. And the update uncovered one problem zone,
for Lord Howe Island afair. *Their* time zone has an offset of some
hours plus 45 minutes from UTC! So our offset in 10 minute increments
doesn't quite accomodate it. If we moved to quarter-hour offsets or to a
fatter field we would be OK.
Oh, another interesting factoid from the most recent updates: some
places have offsets of 14 hours from UTC! They must be trying to match
up with nearby countries or areas on the same side of the date line...
- Thomas