Mysql中求两个时间差的小时数以及mysql中substring_index 用法
1.mysql中求两个时间的差,返回小时数:可以使用mysql的hour() 函数,具体如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql>
set
@d1=
'2010-01-03 15:30:00'
;
Query OK, 0
rows
affected (0.00 sec)
mysql>
set
@d2=
'2010-01-04 17:40:00'
;
Query OK, 0
rows
affected (0.00 sec)
mysql>
select
hour
(timediff(@d2,@d1));
+
-------------------------+
|
hour
(timediff(@d2,@d1)) |
+
-------------------------+
| 26 |
+
-------------------------+
1 row
in
set
(0.02 sec)
mysql>
|
2.mysql中substring_index()的用法:
具体:SUBSTRING_INDEX(str,delim,count);
其中:str 是要被截取的字符串,delim 是分隔符,count是第count个分隔符,话不多说,大家看例子就明白:
注意:count可以为负值,默认是正数,如果是正数,则从左往右开始算,取分隔符左边的内容;如果是负数,那就是从右往左开始算,取分隔符右边的内容,大家看下面的例子,一看就懂……
mysql> select substring_index('www.baidu.com.','.',2);
+-----------------------------------------+
| substring_index('www.baidu.com.','.',2) |
+-----------------------------------------+
| www.baidu |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql> select substring_index('www.baidu.com.','.',-2);
+------------------------------------------+
| substring_index('www.baidu.com.','.',-2) |
+------------------------------------------+
| com. |
+------------------------------------------+
1 row in set (0.00 sec)
mysql> select substring_index('www.baidu.com.cn.google.','.',-2);
+----------------------------------------------------+
| substring_index('www.baidu.com.cn.google.','.',-2) |
+----------------------------------------------------+
| google. |
+----------------------------------------------------+
1 row in set (0.00 sec)
注意:下面的例子是对地1点的补充:
mysql> set @d1 = '2010-01-03 15:30:00';
Query OK, 0 rows affected (0.00 sec)
mysql> set @d2 = '2010-01-04 17:40:00';
Query OK, 0 rows affected (0.00 sec)
mysql> select hour(timediff(@d2,@d1));
+-------------------------+
| hour(timediff(@d2,@d1)) |
+-------------------------+
| 26 |
+-------------------------+
1 row in set (0.00 sec)
mysql> select substring_index(timediff(@d1,@d2),':',1);
+------------------------------------------+
| substring_index(timediff(@d1,@d2),':',1) |
+------------------------------------------+
| -26 |
+------------------------------------------+
1 row in set (0.03 sec)
mysql> select timediff(@d2,@d1);
+-------------------+
| timediff(@d2,@d1) |
+-------------------+
| 26:10:00 |
+-------------------+
1 row in set (0.00 sec)
mysql>
good luck for you !