MySQL in a Nutshell, 2nd Edition

Name

DATE_ADD()

Synopsis

DATE_ADD(date, INTERVAL number type)

Using the date or datetime given, this function adds the number of intervals specified. It’s fairly synonymous with the ADDDATE() function. If none of the parameters include datetime or time factors, the results will be returned in date format. Otherwise, the results will be in datetime format. See Table 12-1 for a list of intervals permitted. Here is an example:

UPDATE appointments
SET appt_date = DATE_ADD(appt_date, INTERVAL 1 DAY)
WHERE appt_id='1202';

In this example, the appointment date is changed to its current value plus one additional day to postpone the appointment by a day. If we changed the 1 to –1, MySQL would subtract a day instead. This would make the function the equivalent of DATE_SUB().

If you leave out some numbers in the second argument, MySQL assumes that the leftmost interval factors are 0 and are just not given. In the following example, although we’re using the interval HOUR_SECOND, we’re not giving the number of hours and the function still works—assuming we don’t mean 5 hours and 30 minutes later. MySQL assumes here that we mean '00:05:30' and not '05:30:00':

SELECT NOW( ) AS 'Now', 
DATE_ADD(NOW( ), INTERVAL '05:30' HOUR_SECOND) 
AS 'Later';

+---------------------+---------------------+
| Now                 | Later               |
+---------------------+---------------------+
| 2007-03-14 10:57:05 | 2007-03-14 11:02:35 | 
+---------------------+---------------------+

When adding the intervals MONTH, YEAR, or YEAR_MONTH to a date, ...