INTERVAL

Description

  • The INTERVAL values are used mainly for date and time calculations. The INTERVAL in expressions represents a temporal interval.

  • Temporal intervals are used for certain functions, such as DATE_ADD() and DATE_SUB().

  • Temporal arithmetic also can be performed in expressions using INTERVAL together with the + or - operator:

  1. date + INTERVAL expr unit
  2. date - INTERVAL expr unit
  • INTERVAL expr unit is permitted on either side of the + operator if the expression on the other side is a date or datetime value.
  • For the - operator, INTERVAL expr unit is permitted only on the right side, because it makes no sense to subtract a date or datetime value from an interval.

Syntax

  1. > INTERVAL (expr,unit)

Arguments

ArgumentsDescription
exprrepresents a quantity.
unitthe unit for interpreting the quantity; it is a specifier such as HOUR, DAY, or WEEK.

Note: The INTERVAL keyword and the unit specifier are not case-sensitive.

  • Temporal Interval Expression and Unit Arguments
unit ValueExpected expr Format
MICROSECONDMICROSECONDS
SECONDSECONDS
MINUTEMINUTES
HOURHOURS
DAYDAYS
WEEKWEEKS
MONTHMONTHS
QUARTERQUARTERS
YEARYEARS
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’

We permits any punctuation delimiter in the expr format. Those shown in the table are the suggested delimiters.

Examples

Example 1

  • Temporal intervals are used for DATE_ADD() and DATE_SUB():
  1. > SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR);
  2. +-----------------------------------------+
  3. | date_sub(2018-05-01, interval(1, year)) |
  4. +-----------------------------------------+
  5. | 2017-05-01                              |
  6. +-----------------------------------------+
  7. 1 row in set (0.00 sec)
  9. > SELECT DATE_ADD('2020-12-31 23:59:59', INTERVAL 1 SECOND);
  10. +----------------------------------------------------+
  11. | date_add(2020-12-31 23:59:59, interval(1, second)) |
  12. +----------------------------------------------------+
  13. | 2021-01-01 00:00:00                                |
  14. +----------------------------------------------------+
  15. 1 row in set (0.01 sec)
  17. > SELECT DATE_ADD('2018-12-31 23:59:59', INTERVAL 1 DAY);
  18. +-------------------------------------------------+
  19. | date_add(2018-12-31 23:59:59, interval(1, day)) |
  20. +-------------------------------------------------+
  21. | 2019-01-01 23:59:59                             |
  22. +-------------------------------------------------+
  23. 1 row in set (0.00 sec)
  25. > SELECT DATE_ADD('2100-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND);
  26. +-------------------------------------------------------------+
  27. | date_add(2100-12-31 23:59:59, interval(1:1, minute_second)) |
  28. +-------------------------------------------------------------+
  29. | 2101-01-01 00:01:00                                         |
  30. +-------------------------------------------------------------+
  31. 1 row in set (0.00 sec)
  33. > SELECT DATE_SUB('2025-01-01 00:00:00', INTERVAL '1 1:1:1' DAY_SECOND);
  34. +--------------------------------------------------------------+
  35. | date_sub(2025-01-01 00:00:00, interval(1 1:1:1, day_second)) |
  36. +--------------------------------------------------------------+
  37. | 2024-12-30 22:58:59                                          |
  38. +--------------------------------------------------------------+
  39. 1 row in set (0.00 sec)
  41. > SELECT DATE_ADD('1900-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR);
  42. +----------------------------------------------------------+
  43. | date_add(1900-01-01 00:00:00, interval(-1 10, day_hour)) |
  44. +----------------------------------------------------------+
  45. | 1899-12-30 14:00:00.000000                               |
  46. +----------------------------------------------------------+
  47. 1 row in set (0.00 sec)
  49. > SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
  50. +-----------------------------------------+
  51. | date_sub(1998-01-02, interval(31, day)) |
  52. +-----------------------------------------+
  53. | 1997-12-02                              |
  54. +-----------------------------------------+
  55. 1 row in set (0.00 sec)
  57. > SELECT DATE_ADD('1992-12-31 23:59:59.000002', INTERVAL '1.999999' SECOND_MICROSECOND);
  58. +------------------------------------------------------------------------------+
  59. | date_add(1992-12-31 23:59:59.000002, interval(1.999999, second_microsecond)) |
  60. +------------------------------------------------------------------------------+
  61. | 1993-01-01 00:00:01.000001                                                   |
  62. +------------------------------------------------------------------------------+
  63. 1 row in set (0.00 sec)

Example 2

  • Using INTERVAL together with the + or - operator
  1. > SELECT '2018-12-31 23:59:59' + INTERVAL 1 SECOND;
  2. +-------------------------------------------+
  3. | 2018-12-31 23:59:59 + interval(1, second) |
  4. +-------------------------------------------+
  5. | 2019-01-01 00:00:00                       |
  6. +-------------------------------------------+
  7. 1 row in set (0.00 sec)
  9. > SELECT INTERVAL 1 DAY + '2018-12-31';
  10. +-------------------------------+
  11. | interval(1, day) + 2018-12-31 |
  12. +-------------------------------+
  13. | 2019-01-01                    |
  14. +-------------------------------+
  15. 1 row in set (0.00 sec)
  17. > SELECT '2025-01-01' - INTERVAL 1 SECOND;
  18. +----------------------------------+
  19. | 2025-01-01 - interval(1, second) |
  20. +----------------------------------+
  21. | 2024-12-31 23:59:59              |
  22. +----------------------------------+
  23. 1 row in set (0.00 sec)

Example 3

If you add to or subtract from a date value something that contains a time part, the result is automatically converted to a datetime value:

  1. > SELECT DATE_ADD('2023-01-01', INTERVAL 1 DAY);
  2. +----------------------------------------+
  3. | date_add(2023-01-01, interval(1, day)) |
  4. +----------------------------------------+
  5. | 2023-01-02                             |
  6. +----------------------------------------+
  7. 1 row in set (0.00 sec)
  9. > SELECT DATE_ADD('2023-01-01', INTERVAL 1 HOUR);
  10. +-----------------------------------------+
  11. | date_add(2023-01-01, interval(1, hour)) |
  12. +-----------------------------------------+
  13. | 2023-01-01 01:00:00                     |
  14. +-----------------------------------------+
  15. 1 row in set (0.01 sec)

Example 4

If you add MONTH, YEAR_MONTH, or YEAR and the resulting date has a day that is larger than the maximum day for the new month, the day is adjusted to the maximum days in the new month:

  1. > SELECT DATE_ADD('2019-01-30', INTERVAL 1 MONTH);
  2. +------------------------------------------+
  3. | date_add(2019-01-30, interval(1, month)) |
  4. +------------------------------------------+
  5. | 2019-02-28                               |
  6. +------------------------------------------+
  7. 1 row in set (0.00 sec)

Example 5

Date arithmetic operations require complete dates and do not work with incomplete dates such as ‘2016-07-00’ or badly malformed dates:

  1. > SELECT DATE_ADD('2016-07-00', INTERVAL 1 DAY);
  2. +----------------------------------------+
  3. | date_add(2016-07-00, interval(1, day)) |
  4. +----------------------------------------+
  5. | NULL                                   |
  6. +----------------------------------------+
  7. 1 row in set (0.00 sec)
  9. > SELECT '2005-03-32' + INTERVAL 1 MONTH;
  10. +---------------------------------+
  11. | 2005-03-32 + interval(1, month) |
  12. +---------------------------------+
  13. | NULL                            |
  14. +---------------------------------+
  15. 1 row in set (0.00 sec)