Re: BUG #1871: operations with data types

Michael Fuhr <mike(at)fuhr(dot)org> writes:
> [ Mike's unhappy that ] 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;

This is certainly an arena in which you can't expect the commutative
or associative laws to hold. For instance:

('2005-02-28' + '1 day') + '1 month'

You can't possibly argue that that produces anything but
'2005-03-01' + '1 month' and thence '2005-04-01'. On the other
hand, you can't possibly argue that

('2005-02-28' + '1 month') + '1 day'

produces anything but '2005-03-28' + '1 day' and thence '2005-03-29'.
So the original complaint can at best be described as lacking thought.

> Wait a minute, here's proof that it *must* be wrong ;-)
> mysql> select date_add('2005-08-31', interval 1 month);
> | 2005-09-30 |

rotfl ;-)

> What do other DBMSs do?

This is a fair question. Given that the SQL committee hasn't provided
any useful leadership, what are other groups doing?

regards, tom lane