Microsoft Word - Sam's Teach Yourself MySQL in 21 Days - SAMS.doc

Adding and Subtracting Time

MySQL offers a number of ways of adding and subtracting time. You have the following functions available:
ƒ DATE_ADD(date,INTERVAL expression type) the same as
ƒ ADDDATE(date,INTERVAL expression type)
ƒ DATE_SUB(date,INTERVAL expression type) the same as
ƒ SUBDATE(date,INTERVAL expression type)
ƒ PERIOD_ADD(period,months)
ƒ PERIOD_DIFF(period1,period2)
DATE_ADD takes a date, adds an INTERVAL of time given by expression, which in a format given by
type, and returns the resulting date and time.
Note These functions are new for MySQL 3.22. MySQL 3.23 adds the ability to use and

• instead of DATE_ADD()and DATE_SUB() respectively. It also adds the
  EXTRACT(type FROM date) function. This can be used to extract date and
  time information and takes the same parameters for type as shown in Table
  16.4.
  Table 16.4 lists the supported types.
  Table 16.4 Values for expression and type When Adding and Subtracting Time

type Value Expected Format for

expression

SECOND SECONDS

MINUTE MINUTES

HOUR HOURS

DAY DAYS

MONTH MONTHS

YEAR YEARS

MINUTE_SECOND "MINUTES:SECONDS"

HOUR_MINUTE "HOURS:MINUTES"

DAY_HOUR "DAYS HOURS"

YEAR_MONTH "YEARS-MONTHS"

HOUR_SECOND "HOURS:MINUTES:SECONDS"

DAY_MINUTE "DAYS HOURS:MINUTES"

DAY_SECOND "DAYS

HOURS:MINUTES:SECONDS"

Look at a few example. First take 4 July 1980 and add 15 years:

mysql> SELECT DATE_ADD('1980-07-04',INTERVAL 15 YEAR);

results in

+-----------------------------------------+

| DATE_ADD('1980-07-04',INTERVAL 15 YEAR) |

+-----------------------------------------+

| 1995-07-04 |

+-----------------------------------------+

Next, add 6 months from last Christmas:

mysql> SELECT DATE_ADD('1999-12-25',INTERVAL 6 MONTH);

results in

+-----------------------------------------+

| DATE_ADD('1999-12-25',INTERVAL 6 MONTH) |

+-----------------------------------------+

| 2000-06-25 |