# **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. ```text 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** ```text > 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()`: ```text 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 `-`: ```text 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: ```text 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: ```text 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. ```text 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 ```