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:
Unit | Description |
---|---|
MICROSECOND |
Microseconds |
SECOND |
Seconds |
MINUTE |
Minutes |
HOUR |
Hours |
DAY |
Days |
WEEK |
Weeks |
MONTH |
Months |
QUARTER |
Quarters |
YEAR |
Years |
SECOND_MICROSECOND |
Seconds.Microseconds |
MINUTE_MICROSECOND |
Minutes.Seconds.Microseconds |
MINUTE_SECOND |
Minutes.Seconds |
HOUR_MICROSECOND |
Hours.Minutes.Seconds.Microseconds |
HOUR_SECOND |
Hours.Minutes.Seconds |
HOUR_MINUTE |
Hours.Minutes |
DAY_MICROSECOND |
Days Hours.Minutes.Seconds.Microseconds |
DAY_SECOND |
Days Hours.Minutes.Seconds |
DAY_MINUTE |
Days Hours.Minutes |
DAY_HOUR |
Days Hours |
YEAR_MONTH |
Years-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:
+
or a -
operator; DATE
or TIME
functions: ADDDATE()
, SUBDATE()
, DATE_ADD()
, DATE_SUB()
, TIMESTAMPADD()
, TIMESTAMPDIFF()
, EXTRACT()
; ON SCHEDULE
clause of CREATE EVENT
and ALTER EVENT
. BY SYSTEM_TIME
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/date-and-time-units/