Syntax
DATE_ADD(date, INTERVAL expr unit)
Purpose
DATE_ADD() adds a period to a specified date and returns the result.
Parameters:
datespecifies the reference date or time, and can be a value of the Date, Time, or Datetime data type.exprspecifies the time interval, which can be a negative value.unitspecifies the unit of the time interval.
The following table lists all time interval units.
Unit |
Type |
Description |
Format |
|---|---|---|---|
MICROSECOND |
Independent | Microseconds | MICROSECONDS |
SECOND |
Independent | Seconds | SECONDS |
MINUTE |
Independent | Minutes | MINUTES |
HOUR |
Independent | Hours | HOURS |
DAY |
Independent | Days | DAYS |
WEEK |
Independent | Weeks | WEEKS |
MONTH |
Independent | Months | MONTHS |
QUARTER |
Independent | Quarters | QUARTERS |
YEAR |
Independent | Years | YEARS |
SECOND_MICROSECOND |
Combined | Seconds and microseconds | 'SECONDS.MICROSECONDS' |
MINUTE_MICROSECOND |
Combined | Minutes, seconds, and microseconds | 'MINUTES:SECONDS.MICROSECONDS' |
MINUTE_SECOND |
Combined | Minutes and seconds | 'MINUTES:SECONDS' |
HOUR_MICROSECOND |
Combined | Hours, minutes, seconds, and microseconds | 'HOURS:MINUTES:SECONDS.MICROSECONDS' |
HOUR_SECOND |
Combined | Hours, minutes, and seconds | 'HOURS:MINUTES:SECONDS' |
HOUR_MINUTE |
Combined | Hours and minutes | 'HOURS:MINUTES' |
DAY_SECOND |
Combined | Days, hours, minutes, and seconds | 'DAYS HOURS:MINUTES:SECONDS' |
DAY_MINUTE |
Combined | Days, hours, and minutes | 'DDAYSD HOURS:MINUTES' |
DAY_HOUR |
Combined | Days and hours | 'DAYS HOURS' |
YEAR_MONTH |
Combined | Years and months | 'YEARS-MONTHS' |
Examples
obclient> SELECT
DATE_ADD(NOW(), INTERVAL 5 DAY),
DATE_ADD('2021-01-10', INTERVAL 5 MICROSECOND),
DATE_ADD('2021-01-10', INTERVAL 5 SECOND),
DATE_ADD('2021-01-10', INTERVAL 5 MINUTE),
DATE_ADD('2021-01-10', INTERVAL 5 HOUR),
DATE_ADD('2021-01-10', INTERVAL 5 DAY),
DATE_ADD('2021-01-10', INTERVAL 5 WEEK),
DATE_ADD('2021-01-10', INTERVAL 5 MONTH),
DATE_ADD('2021-01-10', INTERVAL 5 QUARTER),
DATE_ADD('2021-01-10', INTERVAL 5 YEAR),
DATE_ADD('2021-01-10', INTERVAL '5.000005' SECOND_MICROSECOND),
DATE_ADD('2021-01-10', INTERVAL '05:05.000005' MINUTE_MICROSECOND),
DATE_ADD('2021-01-10', INTERVAL '05:05' MINUTE_SECOND),
DATE_ADD('2021-01-10', INTERVAL '05:05:05.000005' HOUR_MICROSECOND),
DATE_ADD('2021-01-10', INTERVAL '05:05:05' HOUR_SECOND),
DATE_ADD('2021-01-10', INTERVAL '05:05' HOUR_MINUTE),
DATE_ADD('2021-01-10', INTERVAL '01 05:05:05.000005' DAY_MICROSECOND),
DATE_ADD('2021-01-10', INTERVAL '01 05:05:05' DAY_SECOND),
DATE_ADD('2021-01-10', INTERVAL '01 05:05' DAY_MINUTE),
DATE_ADD('2021-01-10', INTERVAL '01 05' DAY_HOUR),
DATE_ADD('2021-01-10', INTERVAL '1-01' YEAR_MONTH)
\G
*************************** 1. row ***************************
DATE_ADD(NOW(), INTERVAL 5 DAY): 2021-04-21 16:04:53
DATE_ADD('2014-01-10', INTERVAL 5 MICROSECOND): 2021-01-10 00:00:00.000005
DATE_ADD('2014-01-10', INTERVAL 5 SECOND): 2021-01-10 00:00:05
DATE_ADD('2014-01-10', INTERVAL 5 MINUTE): 2021-01-10 00:05:00
DATE_ADD('2014-01-10', INTERVAL 5 HOUR): 2021-01-10 05:00:00
DATE_ADD('2014-01-10', INTERVAL 5 DAY): 2021-01-15
DATE_ADD('2014-01-10', INTERVAL 5 WEEK): 2021-02-14
DATE_ADD('2014-01-10', INTERVAL 5 MONTH): 2021-06-10
DATE_ADD('2014-01-10', INTERVAL 5 QUARTER): 2021-04-10
DATE_ADD('2014-01-10', INTERVAL 5 YEAR): 2021-01-10
DATE_ADD('2014-01-10', INTERVAL '5.000005' SECOND_MICROSECOND): 2021-01-10 00:00:05.000005
DATE_ADD('2014-01-10', INTERVAL '05:05.000005' MINUTE_MICROSECOND): 2021-01-10 00:05:05.000005
DATE_ADD('2014-01-10', INTERVAL '05:05' MINUTE_SECOND): 2021-01-10 00:05:05
DATE_ADD('2014-01-10', INTERVAL '05:05:05.000005' HOUR_MICROSECOND): 2021-01-10 05:05:05.000005
DATE_ADD('2014-01-10', INTERVAL '05:05:05' HOUR_SECOND): 2021-01-10 05:05:05
DATE_ADD('2014-01-10', INTERVAL '05:05' HOUR_MINUTE): 2021-01-10 05:05:00
DATE_ADD('2014-01-10', INTERVAL '01 05:05:05.000005' DAY_MICROSECOND): 2021-01-11 05:05:05.000005
DATE_ADD('2014-01-10', INTERVAL '01 05:05:05' DAY_SECOND): 2021-01-11 05:05:05
DATE_ADD('2014-01-10', INTERVAL '01 05:05' DAY_MINUTE): 2021-01-11 05:05:00
DATE_ADD('2014-01-10', INTERVAL '01 05' DAY_HOUR): 2021-01-11 05:00:00
DATE_ADD('2014-01-10', INTERVAL '1-01' YEAR_MONTH): 2021-02-10
1 row in set