The INTERVAL keyword can be used to add or subtract a time interval of time to a DATETIME, DATE or TIME value.

The syntax is:

INTERVAL time_quantity time_unit

For example, the SECOND unit is used below by the DATE_ADD() function:

SELECT '2008-12-31 23:59:59' + INTERVAL 1 SECOND;
+-------------------------------------------+
| '2008-12-31 23:59:59' + INTERVAL 1 SECOND |
+-------------------------------------------+
| 2009-01-01 00:00:00                       |
+-------------------------------------------+

The following units are valid:

UnitDescription
MICROSECONDMicroseconds
SECONDSeconds
MINUTEMinutes
HOURHours
DAYDays
WEEKWeeks
MONTHMonths
QUARTERQuarters
YEARYears
SECOND_MICROSECONDSeconds.Microseconds
MINUTE_MICROSECONDMinutes.Seconds.Microseconds
MINUTE_SECONDMinutes.Seconds
HOUR_MICROSECONDHours.Minutes.Seconds.Microseconds
HOUR_SECONDHours.Minutes.Seconds
HOUR_MINUTEHours.Minutes
DAY_MICROSECONDDays Hours.Minutes.Seconds.Microseconds
DAY_SECONDDays Hours.Minutes.Seconds
DAY_MINUTEDays Hours.Minutes
DAY_HOURDays Hours
YEAR_MONTHYears-Months

The time units containing an underscore are composite; that is, they consist of multiple base time units. For base time units, time_quantity is an integer number. For composite units, the quantity must be expressed as a string with multiple integer numbers separated by any punctuation character.

Example of composite units:

INTERVAL '2:2' YEAR_MONTH
INTERVAL '1:30:30' HOUR_SECOND
INTERVAL '1!30!30' HOUR_SECOND -- same as above

Time units can be used in the following contexts:

See also

Comments

Comments loading...