Date and Time Units

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:

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:

• after a + or a - operator;
• with the following DATE or TIME functions: ADDDATE(), SUBDATE(), DATE_ADD(), DATE_SUB(), TIMESTAMPADD(), TIMESTAMPDIFF(), EXTRACT();
• in the ON SCHEDULE clause of CREATE EVENT and ALTER EVENT.
• when defining a partitioning BY SYSTEM_TIME

See also

• Date and time literals