> 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