Обсуждение: Bug with Daylight Savings Time & Interval

Folks,

Found this interesting bug:

jwnet=> select version();                           version
---------------------------------------------------------------PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC
2.95.3
(1 row)

jwnet=> select ('2001-07-31 10:00:00 PST'::TIMESTAMP) + ('248 days'::INTERVAL)
;       ?column?
------------------------2002-04-05 10:00:00-08
(1 row)

jwnet=> select ('2001-07-31 10:00:00 PST'::TIMESTAMP) + ('249 days'::INTERVAL)
;       ?column?
------------------------2002-04-06 10:00:00-08
(1 row)

jwnet=> select ('2001-07-31 10:00:00 PST'::TIMESTAMP) + ('250 days'::INTERVAL)
;       ?column?
------------------------2002-04-07 11:00:00-07

jwnet=> select ('2001-04-01 10:00:00 PST'::TIMESTAMP) + ('100 days'::INTERVAL)
;       ?column?
------------------------2001-07-10 11:00:00-07


It appears that Spring Daylight Savings Time causes PostgreSQL to change my
time zone.  Only the spring, mind you, and not the fall.   This is
potentially catastrophic for the application I'm developing; what can I do to
see that it's fixed?  Or am I misunderstanding the behavior, here?

--
-Josh Berkus

P.S. I'm posting this here instead of the online bug form because I know that
Bruce is on vacation.

Josh Berkus <josh@agliodbs.com> writes:
> Found this interesting bug:
> jwnet=> select ('2001-07-31 10:00:00 PST'::TIMESTAMP) + ('249 days'::INTERVAL) 
> ;
>         ?column?
> ------------------------
>  2002-04-06 10:00:00-08
> (1 row)

> jwnet=> select ('2001-07-31 10:00:00 PST'::TIMESTAMP) + ('250 days'::INTERVAL) 
> ;
>         ?column?
> ------------------------
>  2002-04-07 11:00:00-07

This isn't a bug per the existing definition of INTERVAL.  '250 days' is
defined as '250*24 hours', exactly, no more no less.  When you move
across a DST boundary you get behavior like the above.

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.
        regards, tom lane

Tom and Tom,

> This isn't a bug per the existing definition of INTERVAL.  '250 days'
> is
> defined as '250*24 hours', exactly, no more no less.  When you move
> across a DST boundary you get behavior like the above.
> 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 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;

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!

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

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?  

-Josh Berkus

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'tinvolve
 
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

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

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

Well, the standard sucks ;)

My reference on this is Date and Darwen (I think that Date used another
word than "sucks", but his meaning is clear), who reinforced my
suspicion that the SQL9x date/time folks were in an altered state when
they formulated the standard. The standard is inconsistant, incomplete,
and does not match common and essential usage for dates and times. Other
than that, it does a great job with dates and times :))

I won't try to defend the current PostgreSQL implementation as the way
it should always be, but it does have hundreds of hours of work in it to
get where it is. Backing off to "if it isn't in the standard, then kill
it" is a step backwards. I see more than a few more hours of work coming
with the unbelievably short sighted glibc breakage recently introduced.

I'm not subscribed to -sql, and would think that if the discussions have
evolved from "how do I do this" to "how *should* we do this" then the
discussion should move to -hackers. I'm not subscribed to every list,
and really can not keep up with the ones I am on now. I recently
subscribed to -general because design discussions seem to erupt there,
but will likely unsubscribe soon. And expect that design happens on
-hackers where it is intended.
               - Thomas