Re: [SQL] 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(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

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

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

Switched to -hackers from -sql and -bugs.

On Tue, 2002-05-21 at 16:24, Thomas Lockhart wrote:
>
> 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.

It's worth pointing out that the same syntax is in SQL92, so I conclude
that no one could think how to improve it through a seven year period.

I don't want to dispose of the existing INTERVAL type, but I would like
the functionality offered by the SQL99 types. For example, I want to be
able to use INTERVAL HOUR(3) TO MINUTE to record the time taken by some
industrial process and I don't want '125 hours 15 minutes' converted
into '5 days 05:15'.

You talk of breaking interval into a number of pieces, but I don't see
the need. You have already implemented half of what is needed. The
other part needed is to record the leading field precision, which we can
surely do in typmod, where you already store the fractional precision.
At present you have in AdjustIntervalForTypmod():

int range = ((typmod >> 16) & 0x7FFF);
int precision = (typmod & 0xFFFF);

and since precision is limited to the range 0-6, we should certainly be
able to fit the leading field precision into the same space:

int frac_precision = (typmod & 0xFF); /* default is 6 */
int lead_precision = ((typmod >> 8) & 0xFF); /* default is 2 */

all that is left is a set of rules to validate input and to format
output according to the given precision, and to change the parser
slightly to get the SQL99 syntax right..

Now I'm sure I'm oversimplifying, but where?

As to other common usage, I can see benefits in extending the subtypes
to include WEEK, and this is conceptually merely an extension of the
existing SQL99 DAY TO SECOND type. What other usage do you see that can
reasonably be translated from fuzzy human talk into solid data? Years
and months are already handled and can be used meaningfully. What you
can't do in SQL99 is translate from exact INTERVAL DAY TO SECOND to
fuzzy INTERVAL YEAR TO MONTH. I can't see why one should want to, but
if you do, our existing type system would let us cast INTERVAL DAY TO
SECOND to INTERVAL, which already does this in a satisfactorily fuzzy
way. I can even conceive of doing the conversion using a configured
choice out of a set of fuzzy conversion options. For example: configure
year to be 360, 365 or 365.2425 days; configure month to be year/12 or
30 days or 4 weeks; and so on.

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C

"We are troubled on every side, yet not distressed; we
are perplexed, but not in despair; persecuted, but not
forsaken; cast down, but not destroyed; Always bearing
about in the body the dying of the Lord Jesus, that
the life also of Jesus might be made manifest in our
body." II Corinthians 4:8-10