interval 作为函数时
当 interval 作为一个函数时，它被当做一个比较函数，即 interval ()，如 interval (4,0,1,2,3,4,5,6)，则在函数中，第一个数 4 作为被比较数，后面的 0,1,2,3,4,5,6 为比较数，然后将后面的数字依次与 4 进行比较，返回小于等于 4 的个数，所以上述结果为 5，注意，只有将 4 后面的数字从小到大进行排列，interval 函数才能正常使用，若排序混乱，可以使用，但会影响最终结果。
interval 作为关键字时
当 interval 作为一个关键字时，表示为时间间隔，常用在 date_add ()、date_sub ()函数中，常用于时间的加减法。
时间的加法
使用 date_add 函数时
mysql> select date_add('2020-07-20', interval +1 day);
+-----------------------------------------+
| date_add('2020-07-20', interval +1 day) |
+-----------------------------------------+
| 2020-07-21 |
+-----------------------------------------+
1 row in set (0.01 sec)
mysql> select date_add('2020-07-20', interval +1 hour);
+------------------------------------------+
| date_add('2020-07-20', interval +1 hour) |
+------------------------------------------+
| 2020-07-20 01:00:00 |
+------------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('2020-07-20', interval +1 minute);
+--------------------------------------------+
| date_add('2020-07-20', interval +1 minute) |
+--------------------------------------------+
| 2020-07-20 00:01:00 |
+--------------------------------------------+
1 row in set (0.00 sec)
使用 DATE 关键字时，需要确定时间的粒度大小，再进行具体时刻的选取，可供选择的时间粒度如下
- MICROSECOND
- SECOND
- MINUTE
- HOUR
- DAY
- WEEK
- MONTH
- QUARTER
- YEAR
- SECOND_MICROSECOND
- MINUTE_MICROSECOND
- MINUTE_SECOND
- HOUR_MICROSECOND
- HOUR_SECOND
- HOUR_MINUTE
- DAY_MICROSECOND
- DAY_SECOND
- DAY_MINUTE
- DAY_HOUR
- YEAR_MONTH
使用如下
mysql> select DATE '2020-07-20' + interval '1 31' hour_second;
+-------------------------------------------------+
| DATE '2020-07-20' + interval '1 31' hour_second |
+-------------------------------------------------+
| 2020-07-20 00:01:31 |
+-------------------------------------------------+
1 row in set (0.00 sec)
mysql> select DATE '2020-07-20' + interval '2' microsecond;
+----------------------------------------------+
| DATE '2020-07-20' + interval '2' microsecond |
+----------------------------------------------+
| 2020-07-20 00:00:00.000002 |
+----------------------------------------------+
1 row in set (0.00 sec)时间的减法
与时间的加法使用方式一致，将 + 号换成 - 号就可以
mysql> select date_add('2020-07-20', interval -1 day);
+-----------------------------------------+
| date_add('2020-07-20', interval -1 day) |
+-----------------------------------------+
| 2020-07-19 |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('2020-07-20', interval -1 hour);
+------------------------------------------+
| date_add('2020-07-20', interval -1 hour) |
+------------------------------------------+
| 2020-07-19 23:00:00 |
+------------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('2020-07-20', interval -1 minute);
+--------------------------------------------+
| date_add('2020-07-20', interval -1 minute) |
+--------------------------------------------+
| 2020-07-19 23:59:00 |
+--------------------------------------------+
1 row in set (0.00 sec)
-----------------
mysql> select DATE '2020-07-20' - interval '1 31' hour_second;
+-------------------------------------------------+
| DATE '2020-07-20' - interval '1 31' hour_second |
+-------------------------------------------------+
| 2020-07-19 23:58:29 |
+-------------------------------------------------+
1 row in set (0.00 sec)
mysql> select DATE '2020-07-20' - interval '2' microsecond;
+----------------------------------------------+
| DATE '2020-07-20' - interval '2' microsecond |
+----------------------------------------------+
| 2020-07-19 23:59:59.999998 |
+----------------------------------------------+
1 row in set (0.00 sec)
评论 (0)