Name

DATE_ADD()

Synopsis

DATE_ADD(date, INTERVAL expr unit)

Returns the result of adding the expression expr using the units unit to the date. The date argument is the starting date or DATETIME value, and expr may start with a - symbol for negative intervals. Table D-1 shows the interval types supported and the expected expr values. Note the examples in this table that show where it is necessary to surround the expr value with quotes for MySQL to interpret them correctly (although, if you are ever in doubt, adding the quotes will always work).

Table D-1. Expected expr values

Type

Expected expr value

Example

MICROSECOND

MICROSECONDS

111111

SECOND

SECONDS

11

MINUTE

MINUTES

11

HOUR

HOURS

11

DAY

DAYS

11

WEEK

WEEKS

11

MONTH

MONTHS

11

QUARTER

QUARTERS

1

YEAR

YEARS

11

SECOND_MICROSECOND

'SECONDS.MICROSECONDS'

11.22

MINUTE_MICROSECOND

'MINUTES.MICROSECONDS'

11.22

MINUTE_SECOND

'MINUTES:SECONDS'

'11:22'

HOUR_MICROSECOND

'HOURS.MICROSECONDS'

11.22

HOUR_SECOND

'HOURS:MINUTES:SECONDS'

'11:22:33'

HOUR_MINUTE

'HOURS:MINUTES'

'11:22'

DAY_MICROSECOND

'DAYS.MICROSECONDS'

11.22

DAY_SECOND

'DAYS HOURS:MINUTES:SECONDS'

'11 22:33:44'

DAY_MINUTE

'DAYS HOURS:MINUTES'

'11 22:33'

DAY_HOUR

'DAYS HOURS'

'11 22'

YEAR_MONTH

'YEARS-MONTHS'

'11-2'

You can also use the DATE_SUB function to subtract date intervals. However, it’s not actually necessary for you to use the DATE_ADD or DATE_SUB functions, as you can use date arithmetic directly in MySQL. This code:

SELECT DATE_ADD('1975-01-01', INTERVAL 77 DAY);
SELECT DATE_SUB('1982-07-04', INTERVAL '3-11' YEAR_MONTH);
SELECT '2016-12-31 23:59:59' + INTERVAL 1 SECOND;
SELECT '2000-01-01' - INTERVAL 1 SECOND;

returns the following values:

1975-03-19
1978-08-04
2017-01-01 00:00:00
1999-12-31 23:59:59

Notice how the last two commands use date arithmetic directly without recourse to functions.