Re: [HACKERS] Date/Time Flaw in pg_dump ? - Mailing list pgsql-hackers

Hi guys,

I've had a long discussion with the timezone people about this time zone
abbreviation issue.

In their words, the way Postgres works is broken :-(

While to us mere mortals it may appear sensible that zone designations
are unique, this is apparently not the case, and this is not unique to
Australia. Any code which relies on them being unique is designated
"broken".

I argued strongly that timezones abbreviations should be changed to be
unique, but without a great deal of success, partly because (a) that's
just the way it is (b) it's based on official government of local areas
and (c) there's no reason to change them.

I personally disagree, but I wouldn't be holding my breath for anything
to change on that front.

So according to them, the way postgres should work is that it should
dump times with a time and a specific UT offset, as in 10:00am UT-10 for
example.

I'm not 100% sure why Postgres has a lot of code for timezone issues
currently. I'm guessing that Postgres is trying to work around this
zoneinfo ``problem'' by recognising say "AEST" in lieu of australia's
EST zone. But unless you're going to do a proper job of it and also
output "AEST" on postgres dumps, it seems like a futile thing.

The other option would be to dump the full locale name, like instead of
outputing "EST", output "Australia/Sydney" which is the full name for
that locale. Unfortunately I don't think there's a portable way of
getting that information on different systems, and also it's rather
wordy output.

So basicly the timezone experts are saying that the time zone abbrevs
are useless and this problem is not just limited to Australia. It looks
to me then like Postgres should stop outputting timezone abbrevs and
start outputting UT offsets. The argument is that without any timezone -
well that just means local time. If you do specify a timezone it should
be the full locale name - as in Australia/Sydney.

There are several other arguments. For example some areas sometimes
change their zone. Apparently the state of Georgia (?) once changed the
zone they are in. In such a case Georgia would need their own locale
file. To output dates using the generic abbreviation could be incorrect.

The other thing that occurs to me is that I don't know what would happen
in that phantom hour once a year when you change over to summer time (or
was it when you change back). UT offsets solve this, I'm not sure if
anybody has solved it for abbrevs.

Timezones are a lot more complex than they look, and I'd like to
understand more about how Postgres regards them. Does anybody else have
any thoughts on this?



Thomas Lockhart wrote:
> 
> > Any datetime fields are different. I think it's a timezone problem.
> > The dump includes the timezone as part of the dump, so I'm guessing that
> > the problem is on the part of psql not noticing that. I'm using the
> > Australian "EST" zone if that's useful.
> > Is there an immediate work-around?
> 
> Yeah, move to the east coast of the US :)
> 
> EST is the US-standard designation for "Eastern Standard Time" (5
> hours off of GMT). If you compile your backend with the flag
> -DUSE_AUSTRALIAN_RULES=1 you will instead get this to match the
> Australian convention, but will no longer handle the US timezone of
> course.
> 
> This is used in backend/utils/adt/dt.c, and is done with an #if rather
> than an #ifdef. Perhaps I should change that...
> 
> btw, Australia has by far the largest "timezone space" I've ever seen!
> There are 17 Australia-specific timezones supported by the Postgres
> backend. I know it's a big place, but the "timezone per capita" leads
> the world ;)
> 
>                         - Tom
> 
> --
> Thomas Lockhart                         lockhart@alumni.caltech.edu
> South Pasadena, California

-- 
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:chris.bitmead@bigfoot.com