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

Josh Berkus <josh(at)agliodbs(dot)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