Обсуждение: Interval bug

Hi guys,

Firstly,
Postgres rocks!  Our live database is rapidly growing and
ever-reliable.  We're stupidly storing URLs visited by nearly a thousand
of our own machines, and are yet to miss a beat (apart from the obvious
speed problems)!

Secondly, we have a problem with interval math, detailed below.  Should
be reproducible on any platform(?)

Regards,
- Mitchell Brandsma (mitchell@pienetworks.com)

Version: 7.0.2 (is it fixed in the latest?)

To reproduce:
psql <nameyourdatabase>

=# select '2 years ago'::interval + '1 year 360 days 00:00'::interval;
       ?column?
----------------------
 1 year 360 00:00 ago        ### Wrong! (Should be 5 or 6 days depending
on interpretation?)
(1 row)

=# select '2 years ago'::interval - '1 year 360 days 00:00'::interval;
       ?column?
-----------------------
 3 years 360 00:00 ago        ### Correct

=# select '365 days'::interval - '1 year'::interval;        <-- should
be ~ 0 or 1 day(s)
       ?column?
----------------------
 1 year 365 00:00 ago    ### Wrong!
(1 row)

Another eg:

=# select 'today'::datetime;
        ?column?
------------------------
 2001-01-10 00:00:00+08    ###  Today
(1 row)

=# create temp table testdate_mkb (td interval);
CREATE
=# insert into testdate_mkb select ('2 years'::interval - '1 year 353
00:00 ago') ;
INSERT 246954387 1
=# select * from testdate_mkb;
        td
-------------------
 3 years 353 00:00            ### !!!!!!
(1 row)

pielive=# select 'today'::datetime + td from testdate_mkb;
        ?column?
------------------------
 2004-12-28 00:00:00+08
(1 row)

The correct answer is  of course about 12 days (2 years - 1 year 353
days) from now, not nearly 4 years

Platforms tried on:
PIII 500, uname: Linux dbserver 2.2.13 #21 Thu Dec 16 06:50:30 WST 1999
i686 unknown
Celeron, uname: Linux dbserver 2.2.12-20 #1 Mon Sep 27 10:40:35 EDT 1999
i686 unknown (RedHat)

> Secondly, we have a problem with interval math, detailed below.  Should
> be reproducible on any platform(?)
> Version: 7.0.2 (is it fixed in the latest?)

Yes, but may need a little more fixup...

> =# select '2 years ago'::interval + '1 year 360 days 00:00'::interval;
>        ?column?
> ----------------------
>  1 year 360 00:00 ago
> Wrong! (Should be 5 or 6 days depending on interpretation?)

I'd forgotten about this until your posting. Internally, the math is
being done correctly. But, the output representation for earlier
releases does not handle "mixed signs" at all well. In particular,
months and years are stored in one field (saved as months), and days,
hours, etc are stored in another (saved as seconds), so if there is a
sign flip between the two fields it needs to be explicitly mentioned in
the output.

In your test case, the result has a sign flip between the months and
seconds. So, the result above *should* be something like

  -1 year +360 days

The "ago" representation just adds to the confusion: "-360 days ago"
seems too easy to misread or misinterpret to be useful.

7.1 will represent this as

  -1 year +360 days 00:00

and the 7.1beta tarball represents this as

  -1 year 360 +00:00

(note lack of explicit plus sign in front of the days field, which means
that it still isn't quite right for least ambiguity).

I've done some initial testing on patches which result in signs on all
fields once a negative value is seen, and this seems to be the least
troublesome solution. The patches also put an explicit "day(s)" on the
days field, if any.

Fixes will appear in the next beta (beta4?) unless there are objections.
Let me know if you need the patches.

Thanks for the report!

                       - Thomas

Wow, this was interesting.  I never suspected that in:

    -1 year 360 days 00:00

the "360 days" is positive, while the "-1 year" was negative, but I can
now see how that can very easily happen.  Should we print the "days" +/-
sign only when it is not the same as the "year" sign?  Seems like the
way to go.


> > Secondly, we have a problem with interval math, detailed below.  Should
> > be reproducible on any platform(?)
> > Version: 7.0.2 (is it fixed in the latest?)
>
> Yes, but may need a little more fixup...
>
> > =# select '2 years ago'::interval + '1 year 360 days 00:00'::interval;
> >        ?column?
> > ----------------------
> >  1 year 360 00:00 ago
> > Wrong! (Should be 5 or 6 days depending on interpretation?)
>
> I'd forgotten about this until your posting. Internally, the math is
> being done correctly. But, the output representation for earlier
> releases does not handle "mixed signs" at all well. In particular,
> months and years are stored in one field (saved as months), and days,
> hours, etc are stored in another (saved as seconds), so if there is a
> sign flip between the two fields it needs to be explicitly mentioned in
> the output.
>
> In your test case, the result has a sign flip between the months and
> seconds. So, the result above *should* be something like
>
>   -1 year +360 days
>
> The "ago" representation just adds to the confusion: "-360 days ago"
> seems too easy to misread or misinterpret to be useful.
>
> 7.1 will represent this as
>
>   -1 year +360 days 00:00
>
> and the 7.1beta tarball represents this as
>
>   -1 year 360 +00:00
>
> (note lack of explicit plus sign in front of the days field, which means
> that it still isn't quite right for least ambiguity).
>
> I've done some initial testing on patches which result in signs on all
> fields once a negative value is seen, and this seems to be the least
> troublesome solution. The patches also put an explicit "day(s)" on the
> days field, if any.
>
> Fixes will appear in the next beta (beta4?) unless there are objections.
> Let me know if you need the patches.
>
> Thanks for the report!
>
>                        - Thomas
>


--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

> ... Should we print the "days" +/-
> sign only when it is not the same as the "year" sign?  Seems like the
> way to go.

It isn't quite that easy, but you have the right idea. My new code omits
signs unless there is a negative field value, then all subsequent fields
are signed.

That seems to be less ambiguous. fwiw, the traditional "Postgres format"
never was right in this regard (though internal calculations were done
correctly).

                    - Thomas