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