Tom Lane wrote:
>
> Chris Bitmead <chris.bitmead@bigfoot.com> writes:
> > In their words, the way Postgres works is broken :-(
>
> Thomas will have to provide the detailed response to this, but as far
> as I've seen there is nothing "broken" about Postgres' handling of
> timezones. You're assuming that portability of dump scripts across
> locales
not across locales, within the same locale!
> is more important than showing dates in the style(s) people
> want to read ... in the real world that isn't so.
Well I'm not assuming it, it is the timezone database which assumes it.
Also the problem is not "across locales", but rather within a single
locale. Like if someone installs a standard RedHat system with Postgres
and starts using it, depending on where they are in the world it may not
function correctly.
As far as people seeing dates in the "style they want to read", the
timezone people made the not-unreasonable observation that if you just
want to see local-time, you shouldn't show any zone at all. Only when
you are not talking about the current zone should you show something
specific. Given that zone ids are not unique that sounds reasonable. As
I said, I think they should be unique, but they're not.
Ok, you have the AUSTRALIAN_RULES compilation option, so people over
here have to rebuild the whole of postgres from scratch. Doesn't worry
me, but a lot of people don't want to have to bother with that.
Also there are probably some other locales in the world with the same
problem that you havn't considered yet.
>
> > 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.
>
> SET DATESTYLE = 'ISO'.
>
> (It might be a worthwhile idea for pg_dump to use this datestyle always,
> since indeed some of the other ones are locale-dependent. Comments?)
>
> regards, tom lane