INTERVAL

语法说明

  • INTERVAL 用于日期和时间计算。

  • INTERVAL 可以用于函数运算 DATE_ADD() 和 DATE_SUB()。

  • INTERVAL 可以再表达式中使用 + 或 - 运算符来进行运算。

  1. date + INTERVAL expr unit
  2. date - INTERVAL expr unit
  • 无论 + 运算符的左边或者右边，只要它其中一边的表达式是一个 date 或 datetime 值，则可以使用 INTERVAL expr。
  • 对于 - 运算符，仅仅可以在 - 的右边使用 INTERVAL expr。

语法结构

  1. > INTERVAL (expr,unit)

参数释义

参数说明
expr任何数值类型与字符串列的列名
unit说明符，例如 HOUR、DAY 或 WEEK

Note

INTERVAL 关键字和 unit 不区分大小写。

  • Interval 表达式和 unit 参数
unit 值描述
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’

你可以在 expr 中使用任何标点分隔符。上表所示为建议的分隔符。

示例

示例 1

  • 与 DATE_ADD() 和 DATE_SUB() 一起使用：
  1. mysql> 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. mysql> 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. mysql> 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. mysql> 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. mysql> 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. mysql> 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. mysql> 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. mysql> 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)

示例 2

  • 与 + 或 - 一起使用：
  1. mysql> 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. mysql> 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. mysql> 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)

示例 3

如果你在一个 date 值上加上或减去一个包含时间部分的值，执行结果会自动转换为一个 datetime 值：

  1. mysql> 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. mysql> 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)

示例 4

如果添加了 MONTH、YEAR_MONTH 或 YEAR，并且执行结果的日期的某一天比当月的最大天数大，则该天将被调整为当月的最大天数：

  1. mysql> 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)

示例 5

date 不能用错误的日期，如执行 2016-07-00 或格式严重错误的日期则结果为 NULL。

  1. mysql> 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. mysql> 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)