Re: [HACKERS] Date/Time Flaw in pg_dump ?

Thomas Lockhart wrote:
>
> > In their words, the way Postgres works is broken :-(
>
> ... as is the rest of the world :)

Yep :-)

> Use the ISO format setting, and you'll be a happy camper:
>
> postgres=> set datestyle='iso';

Ok. I think though that you should consider making it the default,
simply because something that always works is a good default. Something
that only sometimes works is not a very good default.

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

That might be ok if what comes out of the database works when you stick
it back in. Like you accept AEST as australian eastern standard time as
input. But if you don't print AEST on output then it's inconsistent. I
think the output should be either no time zone info, the full locale
("Australia/Sydney") or UT offset since they will always work.

I'm not sure what you mean when you say there is no standard interface
to input times. Various combinations of setenv("TZ="), mktime() etc etc
seem to be able to do everything one would need in my experience.

> 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 ;)

I'm not sure why you would require any more interface than
mktime(),localtime() and friends. The only thing I can think of is to
have a list of the valid locales but that's a different problem.

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

What happens for those times that occur twice? Like if the clocks go
back 1 hour at 3:00am on a particular day, then that time happens twice.
In other words 3/3/1999 2:30am EST may be an ambigous time because that
time occurs twice. How is that handled?