Michael Fuhr <mike@fuhr.org> writes:
> Apparently the two intervals don't cancel each other out (i.e.,
> they're not optimized to zero),
Well, no, because + and - associate left-to-right.
> so effectively we get this:
> test=> select '2005-08-31'::date + '1 month'::interval;
> ?column?
> ---------------------
> 2005-09-30 00:00:00
> (1 row)
This seems to be the crux of the issue: is the above expression valid
and if so what should it yield?
I think you are right that the SQL spec wants it to raise an error,
but the spec's rules about datetime behavior are uselessly narrow
minded (last I checked, they still had not heard of daylight savings
time ... so they're obviously not trying very hard in this area).
The behavior that's in our code now is to round back to the last real
day of the month. This might not be the best choice, but raising an
error doesn't seem better to me offhand. Date and Darwen seem to
think rounding forward to the first day of the next month would be
more natural. I'm not sure why; it certainly wouldn't fix the
complainant's issue, only surprise him in a different way. Also,
even if you like round-forward for the above case, what about
subtraction --- what should '2005-10-31' - '1 month' give? Rounding
down definitely feels more natural in that case, at least to me.
Any comments out there?
regards, tom lane