INTERVAL
SQL construct
Adds a time interval to a date or subtracts it.
MySQL 8.1
date {+ | -} INTERVAL expr unitParameters
- dateDATE
- Source date or datetime
- exprINT
- Number of units; for a compound unit, a string, for example '1:30'
- unitkeyword
- Interval unit
- Values:MICROSECONDSECONDMINUTEHOURDAYWEEKMONTHQUARTERYEARSECOND_MICROSECONDMINUTE_MICROSECONDMINUTE_SECONDHOUR_MICROSECONDHOUR_SECONDHOUR_MINUTEDAY_MICROSECONDDAY_SECONDDAY_MINUTEDAY_HOURYEAR_MONTH
Return value
A date if days or larger units are added to a date, otherwise a datetime; if the month has no such day, its last day is used
Examples
A month added to January 31
MySQL 8.1
SELECT '2024-01-31' + INTERVAL 1 MONTH AS next_month;Payment due in 30 days
MySQL 8.1
SELECT payment_id,
date,
date + INTERVAL 30 DAY AS due_date
FROM Payments
LIMIT 5;Interval on the left and minus
MySQL 8.1
SELECT INTERVAL 1 DAY + '2024-12-31' AS new_year,
'2024-03-08 14:30:00' - INTERVAL '1:30' HOUR_MINUTE AS earlier;Details
INTERVAL is part of an expression with a date, not a separate value: SELECT INTERVAL 1 DAY is an error. date + INTERVAL 7 DAY is equivalent to DATE_ADD(date, INTERVAL 7 DAY), and the interval can also be written to the left of the plus. You cannot subtract a date from an interval.
Without an interval, a number is added not to the date but to the number YYYYMMDD: CURDATE() + 1 is not tomorrow. In PostgreSQL, INTERVAL is a data type, and the value is written as a string: date + INTERVAL '7 days'.