INTERVAL
- 语法说明
- 语法结构
- 参数释义
- 示例
  - 示例 1
  - 示例 2
  - 示例 3
  - 示例 4
  - 示例 5

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 值	描述
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’

你可以在 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)