On Sun, Sep 11, 2005 at 12:43:58AM -0400, Tom Lane wrote:
> 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.
Sure, I wasn't expecting any different -- I was just mentioning it
for the mathematically inclined who might think +1 and -1 should
cancel each other out.
> > 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?
Dunno. It does seem inconsistent that these expressions give the
same answer:
select '2005-08-30'::date + '1 month'::interval,
'2005-08-30'::date + '1 day'::interval + '1 month'::interval;
?column? | ?column?
---------------------+---------------------
2005-09-30 00:00:00 | 2005-09-30 00:00:00
(1 row)
and these give different answers:
select '2005-08-30'::date + '1 day'::interval + '1 month'::interval,
'2005-08-30'::date + '1 month'::interval + '1 day'::interval;
?column? | ?column?
---------------------+---------------------
2005-09-30 00:00:00 | 2005-10-01 00:00:00
(1 row)
but I doubt I could make an argument for an alternative that was
any better than its counterargument.
Wait a minute, here's proof that it *must* be wrong ;-)
mysql> select date_add('2005-08-31', interval 1 month);
+------------------------------------------+
| date_add('2005-08-31', interval 1 month) |
+------------------------------------------+
| 2005-09-30 |
+------------------------------------------+
1 row in set (0.11 sec)
What do other DBMSs do?
--
Michael Fuhr
