MYSQL Date Calculation Functions: DATE_ADD vs simple + INTERVAL - what's the difference?

Question

Forgive me if this is a duplicate question but I have not been able to find any answer on here or anywhere else.

I have become used to calculating dates in MYSQL using the functions DATE_ADD, DATE_SUB etc. as detailed here: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html.

However I saw in another answer that it is possible to simply use code such as CURDATE() + INTERVAL 1 DAY.

The following test produced two rows of identical results.

SELECT 
  CURDATE() AS Today,
  CURDATE() + INTERVAL 5 DAY AS 5_Days,
  CURDATE() - INTERVAL 3 MONTH AS 3_Months_Ago,
  CURDATE() + INTERVAL 1 YEAR AS 1_Year
UNION ALL SELECT 
  CURDATE() AS Today,
  DATE_ADD(CURDATE(), INTERVAL 5 DAY) AS 5_Days,
  DATE_SUB(CURDATE(), INTERVAL 3 MONTH) AS 3_Months_Ago,
  DATE_ADD(CURDATE(), INTERVAL 1 YEAR) AS 1_Year

My question is: is there any difference between the two methods? Is either better than the other? IMO, not using the DATE_ADD functions improves readability and simplicity but I may be wrong!

Strawberry · Accepted Answer · 2017-04-27T15:07:37.403

As you can see, both variations execute in an identical manner...

EXPLAIN EXTENDED
SELECT * FROM weddings WHERE CURDATE()-INTERVAL 40 YEAR > dob;
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | weddings | ALL  | dob           | NULL | NULL    | NULL |   10 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

SHOW WARNINGS;
select test.weddings.id AS id
     , test.weddings.name AS name
     , test.weddings.gender AS gender
     , test.weddings.dob AS dob
     , test.weddings.birthplace AS birthplace
     , test.weddings.wedding_date AS wedding_date 
  from test.weddings 
 where ((curdate() - interval 40 year) > test.weddings.dob)

EXPLAIN EXTENDED
SELECT * FROM weddings WHERE DATE_SUB(CURDATE(),INTERVAL 40 YEAR) > dob;
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | weddings | ALL  | dob           | NULL | NULL    | NULL |   10 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

SHOW WARNINGS;

select test.weddings.id AS id
     , test.weddings.name AS name
     , test.weddings.gender AS gender
     , test.weddings.dob AS dob
     , test.weddings.birthplace AS birthplace
     , test.weddings.wedding_date AS wedding_date 
  from test.weddings 
 where ((curdate() - interval 40 year) > test.weddings.dob)

And FWIW, I agree with you.

MYSQL Date Calculation Functions: DATE_ADD vs simple + INTERVAL - what's the difference?

1 Answers1