Re: [SQL] Bug with Daylight Savings Time & Interval

> > I've opined several times that interval should account for three
> > separate units: months, days, and seconds. But our time-meister
> > Tom Lockhart doesn't seem to have taken any interest in the idea.

I have taken an interest in the idea. But have not implemented it and
have not concluded that this is the best option. I expect that you will
continue to opine and will continue to take me to task for not following
your advice.

> I beg to differ with Tom L. Even if there were justification for the
> addition of an hour to a calculation involving only days, which there
> is not, there are two bugs with the existing behavior:
> 1. You do not lose an hour with the end of DST, you just gain one with
> the beginning of it (until you wraparound a whole year, which is really
> confusing), which is inconsistent;

Not actually true (probably due to a cut and paste error in your test
suite). Your example specified '2001-07-31 10:00:00 PST' which is
actually within the PDT time of year. PostgreSQL took you at your word
on this one and evaluated the time as though it were in PST. So you
didn't see the 1 hour offset when adding days to another time zone.

> 2. Even if you justify gaining or losing an hour through DST in a
> '+days' operation, changing the TIMEZONE is a bizarre and confusing way
> to do it. I don't fly to Colorado on April 7th!

I'm not sure what you mean here.

> While this needs to be fixed eventually, I need a quick workaround; is
> there a way to "turn off" DST behavior in PostgreSQL?

Consider using TIMESTAMP WITHOUT TIME ZONE.

> Further, it seems that the whole "Interval" section of Postgres,
> possibly one of our greatest strengths as a database, has languished in
> the realm of inconsistent behavior due to lack of interest. Is there
> anything I can do without learning C?

You can continue to explore the current behavior and to form an opinion
on what correct behavior should be. I've resisted adding fields to the
internal interval type for performance and design reasons. As previously
mentioned, blind verbatim compliance with SQL9x may suggest breaking our
INTERVAL type into a bunch of pieces corresponding to the different
interval ranges specified in the standard. However, the SQL standard is
choosing to cover a small subset of common usage to avoid dealing with
the implementation complexities and usage patterns which are uncovered
when trying to do more.

- Thomas