Table of Contents for

MySQL in a Nutshell, 2nd Edition

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, if the given date is valid but the results would be an invalid date because it would be beyond the end of a month, the results are adjusted to the end of the month:

SELECT DATE_ADD('2009-01-29', INTERVAL 1 MONTH)
AS 'One Month Later';

+-----------------+
| One Month Later |
+-----------------+
| 2009-02-28      | 
+-----------------+

Table 12-1 shows the intervals that may be used and how the data should be ordered. For interval values that require more than one factor, a delimiter is used and the data must be enclosed in quotes. Other delimiters may be used besides those shown in the table. For example, 'hh|mm|ss' could be used for HOUR_SECOND. In case you hadn’t noticed, the names for intervals involving more than two time factors use the name of the first and last factor (e.g., DAY_MINUTE and not DAY_HOUR_MINUTE). Keep that in mind when trying to remember the correct interval.