liting李

2020-09-19   阅读量: 541

Mysql

sql中interval用法总结

扫码加入数据分析学习群

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)123456789101112131415161718192021222324

使用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)123456789101112131415

时间的减法

与时间的加法使用方式一致,将 + 号换成 - 号就可以

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.0000 0 0 关注作者 收藏

评论(0)


暂无数据

推荐课程

推荐帖子