Skip to content

INTERVAL

Grammar Description

  • INTERVAL is used for date and time calculations.

  • INTERVAL can be used for function operations DATE_ADD() and DATE_SUB().

  • INTERVAL can be used in the expression using the + or - operator.

date + INTERVAL expr unit
date - INTERVAL expr unit
  • INTERVAL expr is available for use as long as the expression on one side of the + operator is a date or datetime value.
  • For the - operator, only INTERVAL expr can be used to the right of -.

Grammar Structure

> INTERVAL (expr,unit)

Parameter definition

Parameters Description
expr Column name of any numeric type and string column
unit specifiers such as HOUR, DAY, or WEEK

Note

The INTERVAL keyword and unit are case-insensitive.

-Interval expression and unit parameters

unit Value 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'

You can use any punctuation separator in expr. The recommended delimiters are shown in the table above.

Example

Example 1

  • Use with DATE_ADD() and DATE_SUB():
mysql> SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR);
+---------------------------------------------------+
| date_sub(2018-05-01, interval(1, year)) |
+---------------------------------------------------+
| 2017-05-01 |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD('2020-12-31 23:59:59', INTERVAL 1 SECOND);
+-----------------------------------------------------------------------------+
| date_add(2020-12-31 23:59:59, interval(1, second)) |
+-----------------------------------------------------------------------------+
| 2021-01-01 00:00:00 |
+-----------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT DATE_ADD('2018-12-31 23:59:59', INTERVAL 1 DAY);
+------------------------------------------------------------------+
| date_add(2018-12-31 23:59:59, interval(1, day)) |
+------------------------------------------------------------------+
| 2019-01-01 23:59:59 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD('2100-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND);
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| date_add(2100-12-31 23:59:59, interval(1:1, minute_second)) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2101-01-01 00:01:00 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

mysql> SELECT DATE_SUB('2025-01-01 00:00:00', INTERVAL '1 1:1:1' DAY_SECOND);
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| date_sub(2025-01-01 00:00:00, interval(1 1:1:1, day_second)) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2024-12-30 22:58:59 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD('1900-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR);
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| date_add(1900-01-01 00:00:00, interval(-1 10, day_hour)) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1899-12-30 14:00:00.000000 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------+
| date_sub(1998-01-02, interval(31, ​​day)) |
+---------------------------------------------------+
| 1997-12-02 |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002', INTERVAL '1.999999' SECOND_MICROSECOND);
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| date_add(1992-12-31 23:59:59.000002, interval(1.999999, second_microsecond)) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1993-01-01 00:00:01.000001 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

Example 2

  • Use with + or -:
mysql> SELECT '2018-12-31 23:59:59' + INTERVAL 1 SECOND;
+-------------------------------------------------------+
| 2018-12-31 23:59:59 + interval(1, second) |
+-------------------------------------------------------+
| 2019-01-01 00:00:00 |
+-------------------------------------------------------+1 row in set (0.00 sec)

mysql> SELECT INTERVAL 1 DAY + '2018-12-31';
+------------------------------------+
| interval(1, day) + 2018-12-31 |
+------------------------------------+
| 2019-01-01 |
+------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT '2025-01-01' - INTERVAL 1 SECOND;
+---------------------------------------+
| 2025-01-01 - interval(1, second) |
+---------------------------------------+
| 2024-12-31 23:59:59 |
+---------------------------------------+
1 row in set (0.00 sec)

Example 3

If you add or subtract a value containing the time part to a date value, the execution result will be automatically converted to a datetime value:

mysql> SELECT DATE_ADD('2023-01-01', INTERVAL 1 DAY);
+-------------------------------------------------+
| date_add(2023-01-01, interval(1, day)) |
+-------------------------------------------------+
| 2023-01-02 |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD('2023-01-01', INTERVAL 1 HOUR);
+---------------------------------------------------+
| date_add(2023-01-01, interval(1, hour)) |
+---------------------------------------------------+
| 2023-01-01 01:00:00 |
+---------------------------------------------------+
1 row in set (0.01 sec)

Example 4

If MONTH, YEAR_MONTH or YEAR is added and the day on which the result is executed is larger than the maximum number of days in the month, that day will be adjusted to the maximum number of days in the month:

mysql> SELECT DATE_ADD('2019-01-30', INTERVAL 1 MONTH);
+-----------------------------------------------------+
| date_add(2019-01-30, interval(1, month)) |
+-----------------------------------------------------+
| 2019-02-28 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

Example 5

date cannot use the wrong date. If the execution 2016-07-00 or the date with a serious format error, the execution will report an error.

mysql> SELECT DATE_ADD('2016-07-00', INTERVAL 1 DAY);
ERROR 20301 (HY000): invalid input: invalid datatime value 2016-07-00

mysql> SELECT '2005-03-32' + INTERVAL 1 MONTH;
ERROR 20301 (HY000): invalid input: invalid datatime value 2005-03-32