Working with Dates and Times - Database Design Manual: Using MySQL for Windows - page 123

The timeperiod will be a value, followed by the type of time period. This sounds com-
plex, so as usual we will demonstrate by example. To find out what date it will be on this day
next week, we can add 7 days to the current date with the following:

DATE_ADD('2002-12-23', INTERVAL 7 DAY )

You will note that we have typed the date here, instead of using NOW() to show you an
alternative way of entering the date. Figure 11.10 shows this running, with the column
name of nextweek. So the date this time next week will be 30 December.
You can also add other time periods. For instance, the following will return the date that
it will be three months from today:

DATE_ADD('2002-12-23', INTERVAL 3 MONTH )

The above is shown in the threemonths column of Figure 11.10; three months from today
will be 23 March 2003. The complete script to generate Figure 11.10 is as follows:

SELECT '2002-12-23' AS TodaysDate,

DATE_ADD('2002-12-23', INTERVAL 7 DAY ) AS NextWeek,
DATE_ADD('2002-12-23', INTERVAL 3 MONTH ) AS ThreeMonths

As well as adding day, month and year periods to a date, you can also add time periods
as well. For instance, to add six hours to the current date we would use:

DATE_ADD('2002-12-23', INTERVAL 6 HOUR )

You can see this in Figure 11.11, under the 6 hrs column. Notice though that because we
only specified a date in the creation string, it has taken the default value of 00:00:00, mid-
night, as the time, and added six hours to that. The 6 fromnow column shows the results
when we use the NOW() function to generate a date that also has a time part. This time,
adding six hours gives us a date and time for tomorrow. Again, to recreate the results of
Figure 11.11, you can use the following:

SELECT '2002-12-23' AS TodaysDate,

DATE_ADD('2002-12-23', INTERVAL 6 HOUR ) AS 6Hrs,
DATE_ADD(NOW(), INTERVAL 6 HOUR ) AS 6fromnow

Figure 11.10 DATE_ADD() adds day periods to given dates.