Thread: Timezone discrepancies
Here are a few apparent discrepencies between pg7.0beta3 and the timezone documentation at http://www.postgresql.org/docs/postgres/datetime-appendix.htm#DATETIME-APPENDIX-TITLE http://www.postgresql.org/docs/postgres/datatype1134.htm on my system (Linux 2.2.12-20smp #1, i686, running in CDT timezone). 1) Unrecognized timezones claimed in the docs: ERROR: Bad timestamp external representation '1-1-2000 00:00:00 DST' ERROR: Bad timestamp external representation '1-1-2000 00:00:00 ZP4' ERROR: Bad timestamp external representation '1-1-2000 00:00:00 ZP5' ERROR: Bad timestamp external representation '1-1-2000 00:00:00 ZP6' 2) Timezone parsed without complaint but bogusly converted to local timezone: SAT (South Australian Std Time) Example: create table timezones (result timestamp, note varchar); insert into timezones values ('1-1-2000 00:00:00 SAT', 'SAT'); insert into timezones values ('1-1-2000 00:00:00', 'Local'); insert into timezones values ('1-1-2000 00:00:00 GMT','GMT'); select * from timezones; drop table timezones; Results: tzdb=# select * from timezones; result | note ------------------------+-------2000-01-01 00:00:00-06 | SAT2000-01-01 00:00:00-06 | Local1999-12-31 18:00:00-06 | GMT (3 rows) Regards, Ed Loehr
(Cleaning up mail, and I don't see a reply to this...)
> Here are a few apparent discrepencies between pg7.0beta3 and the timezone
> documentation...
> 1) Unrecognized timezones claimed in the docs:
> ERROR: Bad timestamp external representation '1-1-2000 00:00:00 DST'
> ERROR: Bad timestamp external representation '1-1-2000 00:00:00 ZP4'
> ERROR: Bad timestamp external representation '1-1-2000 00:00:00 ZP5'
> ERROR: Bad timestamp external representation '1-1-2000 00:00:00 ZP6'
DST - this is a docs error. DNT is the correct form for "Dansk Normal
Tid", and "DST" is a "Daylight Savings Time" qualifier for other time
zones. Will fix.
ZPx - this is a parser problem, in that currently time zones with
embedded digits are not allowed. Are these three time zones "official"
or used by anyone? I don't find them in my "zoneinfo" database on my
Linux boxes. I would propose that these three zones be eliminated from
the code, rather than putting a special case into the date/time parser.
afaik folks haven't noticed that this is a problem (except for Ed of
course ;). Comments?
> 2) Timezone parsed without complaint but bogusly converted to local
> timezone:
> SAT (South Australian Std Time)
Currently, this is mapped to be a noise word for "Saturday". Is this a
timezone form currently in use in Australia? If so, I can add it to the
"USE_AUSTRALIAN_RULES" variants.
Peter, will you be doing more work on configuration? If so, could we
implement --enable-australian-zones (or something similar) which sets
the internal USE_AUSTRALIAN_RULES parameter?
Thanks for catching these problems. I'm a bit puzzled why the docs and
code differ, since at least some of the things mentioned above go back
at least three years. Must have carried the doc info forward from
somewhere else??
- Thomas
Thomas Lockhart writes: > Peter, will you be doing more work on configuration? If so, could we > implement --enable-australian-zones (or something similar) which sets > the internal USE_AUSTRALIAN_RULES parameter? Will do. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Thomas Lockhart writes: > > ERROR: Bad timestamp external representation '1-1-2000 00:00:00 DST' > > ERROR: Bad timestamp external representation '1-1-2000 00:00:00 ZP4' > > ERROR: Bad timestamp external representation '1-1-2000 00:00:00 ZP5' > > ERROR: Bad timestamp external representation '1-1-2000 00:00:00 ZP6' > > DST - this is a docs error. DNT is the correct form for "Dansk Normal > Tid", and "DST" is a "Daylight Savings Time" qualifier for other time > zones. Will fix. Another comment: I just peeked and, to be blunt, the time zone database looks like a mess. For example, there's CET (central european time), but not CEST (central european summer time). Instead there's CETDST, which I've never heard used. Then there's MEST, MET, METDST, MEWT, MEZ, all of which are supposed to be "Middle Europe" variations, none of which I've ever heard of. (MEZ and MESZ are the German translations of CET and CEST, but as listed they claim to be English terms.) Also I've never heard of "Dansk Normal Tid" (DNT) or "Swedish Summer Time" (SST), both of these places use Central European Time. There are several other obscure candidates where I don't have direct geographic knowledge, such as "Moluccas Time" (MT, I though that would be Mountain Time), or "Seychelles Time" (SET). Probably sometime in the near future a discussion and some research ought to take place to sort out this list. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Peter Eisentraut <peter_e@gmx.net> writes: > Another comment: I just peeked and, to be blunt, the time zone database > looks like a mess. For example, there's CET (central european time), but > not CEST (central european summer time). Instead there's CETDST, which > I've never heard used. Then there's MEST, MET, METDST, MEWT, MEZ, all of > which are supposed to be "Middle Europe" variations, none of which I've MET is sometimes used as middle eastern time as well. Universal Time is a Good Thing ;-) Regards, Gunnar
> Another comment: I just peeked and, to be blunt, the time zone database
> looks like a mess.
Great! (Well, not great, but you know...). Let's clean it up. But we'll
need to get representation from the various regions covered to avoid
dropping useful fields.
> For example, there's CET (central european time), but
> not CEST (central european summer time). Instead there's CETDST, which
> I've never heard used.
I recall working on "MET DST" (note space) at the request of someone in
"MET". The "DST" qualifier works for every "standard timezone". Have you
seen this usage? "CETDST" is probably there for historical reasons, to
handle this case before we could manage the standalone "DST" qualifier.
> Then there's MEST, MET, METDST, MEWT, MEZ, all of
> which are supposed to be "Middle Europe" variations, none of which I've
> ever heard of. (MEZ and MESZ are the German translations of CET and CEST,
> but as listed they claim to be English terms.)
Ah, that may be. So I was talking with a German or Austrian or ??
earlier...
MET shows up in my zic timezone database. We should of course retain
entries for all corresponding entries in those databases, across the
various platforms we support.
> Also I've never heard of
> "Dansk Normal Tid" (DNT) or "Swedish Summer Time" (SST), both of these
> places use Central European Time.
The use of CET in those countries might be modern developments (since
1986??) or perhaps "DNT" and "SST" are much older. Most of the character
string representations for timezones came from Postgres' pre-history at
Berkeley, and I just carried them forward.
I've found that Sun seems to have more accurate timezone support than
other systems, at least for pre-1947 details. And afaik they do not use
zic so we should look at both of those to get a more complete story.
> There are several other obscure candidates where I don't have direct
> geographic knowledge, such as "Moluccas Time" (MT, I though that would be
> Mountain Time), or "Seychelles Time" (SET).
In the US, all time zones have three characters. So "Mountain Time" is
"MST" and "MDT" for "Mountain Standard Time" and "Mountain Daylight
savings Time".
I had thought that there was a "Seychelles time", but perhaps someone
who has been there can speak up?
- Thomas