INTERVAL
语法说明
-
INTERVAL用于日期和时间计算。 -
INTERVAL可以用于函数运算DATE_ADD()和DATE_SUB()。 -
INTERVAL可以再表达式中使用+或-运算符来进行运算。
date + INTERVAL expr unit
date - INTERVAL expr unit
- 无论
+运算符的左边或者右边，只要它其中一边的表达式是一个date或datetime值，则可以使用INTERVAL expr。 - 对于
-运算符，仅仅可以在-的右边使用INTERVAL expr。
语法结构
> 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()一起使用：
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)
示例 2
- 与
+或-一起使用：
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)
示例 3
如果你在一个 date 值上加上或减去一个包含时间部分的值，执行结果会自动转换为一个 datetime 值：
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)
示例 4
如果添加了 MONTH、YEAR_MONTH 或 YEAR，并且执行结果的日期的某一天比当月的最大天数大，则该天将被调整为当月的最大天数：
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)
示例 5
date 不能用错误的日期，如执行 2016-07-00 或格式严重错误的日期，则执行会报错。
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