Is there any difference between DATE_SUB() and using arithmetic operators for datetime calculation?

Question

After I have seen a lot of questions here using the DATE_SUB() or DATE_ADD() functions instead of the arithmetic operators + or -, I was wondering if there was any difference:

Quote from the MySQL-manual:

Date arithmetic also can be performed using INTERVAL together with the + or - operator:

date + INTERVAL expr unit
date - INTERVAL expr unit

So basically, these two statements return the same result:

SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);

and

SELECT NOW() + INTERVAL 7 DAY;

Now my question:

Is there any difference between DATE_SUB() and using the - operator in MySQL? (besides readability?)

Answer 1

For me, it is a matter of experience. After dealing with multiple editors, libraries, and tools that manipulate databases, I've learned not to trust the "+" and "-" operators as much as I trust DATE_SUB(). It's a lot less likely that something is going to be accidentally broken as part of the next software upgrade with DATE_SUB(), when compared to using +/-

Answer 2

The MySQL documentation for DATE_ADD (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-add) explicitly states that you may do date arithmetic with the + and - operators.

Date arithmetic also can be performed using INTERVAL together with the + or - operator:

date + INTERVAL expr unit

date - INTERVAL expr unit

Given that it's endorsed by the docs, I think any difference is stylistic. I personally think the +/- is easier to read (after all, you don't use INT_ADD(...) or DOUBLE_ADD(...) to manipulate numeric values, so why dates?). Others might have their own reasons for liking DATE_ADD and DATE_SUB, and that's fine too. Just pick something and stick with it.

Jemiah