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