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

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

... as is the rest of the world :)

> 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.

Use the ISO format setting, and you'll be a happy camper:

postgres=> set datestyle='iso';
SET VARIABLE
postgres=> select datetime 'now';
?column?              
----------------------
1999-05-11 07:20:30-07
(1 row)

postgres=> show time zone;
NOTICE:  Time zone is PST8PDT
SHOW VARIABLE


> 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.

We rely on the OS to provide timezone offsets for *output*, so we
don't have to figure out how to do daylight savings time (and for
other reasons). There is no standard interface to do the same thing
for input outside of Unix system time, so we do it ourself for input.
And there is no standard interface to get direct access to the
timezone database itself. If'n you don't like the output conventions
for your system, do your own timezone database or learn to like it ;)

> 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.

? Since you would be relying on a timezone database for interpretation
of the abbrevs, you might run the risk of dissimilar systems doing
things inconsistantly. And we've seen lots of differences on Unix
boxes once you start dealing with times before 1960 or so (those damn
kids doing development nowadays :) Sun does a great job (you can learn
a bit of history looking at their timezone database) while some other
systems don't bother trying. The zic utilities used by Linux and some
other systems do a pretty good job, but are not as rigorous as Sun's
database.

> 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?

Uh, sure!

Anyway, your observations are correct, but we are trying to work in
the real world, which doesn't seem much interested in going
exclusively toward the ISO-8601 date/time representation. But we do
support it, and I've toyed with making it the default format. Maybe
for postgres-7.0. In the meantime you can build your server to use it
by default, you can fire up your server with PGDATESTYLE defined, or
you can set PGDATESTYLE for any client using libpq.
                            - Tom

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California