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

Tom L,

Thanks for answering my pushy opinions!

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

Aha. I understand. That's consistent, even if it doesn't work the way
I want it (life is difficult that way). However, I would assert that
it is not at all intuitive, and we need to have it documented
somewhere.

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

My confusion because of the default way of displaying time zones. It
looked to me like Postgres was changing to CST on April 7th. Once
again, consistent but not intuitive.

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

Damn. Doesn't work for me either. I do need to cast stuff into
several time zones, as this is a New York/San Francisco calendar.
Isn't there a version of GMT -8:00 I can use that doesn't involve
DST? What does Postgresql do for Arizona (Arizona does not have DST)?

> You can continue to explore the current behavior and to form an
> opinion
> on what correct behavior should be.

Oliver and I are having a lively discussion regarding Interval math on
PGSQL-SQL. I would love to have you enter the discussion.

> I've resisted adding fields to
> the
> internal interval type for performance and design reasons.

I don't blame you. Data Subtypes is a huge can o' crawdads.

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

Ok, so how should things work, then? While I agree that SQL92's spec
is awkward and limited, we'd need a pretty good argument for breaking
standards. Oliver is already wearing me down in this regard.

-Josh Berkus