MySQL 计算两个日期或时间的间隔 解决方法

1，使用时间函数

计算两个日期之间的间隔，可以使用DATEDIFF()函数

查看代码 复制到剪贴板

1. SELECT DATEDIFF('2010-06-01','2010-01-01'),DATEDIFF('2010-01-01','2010-06-01')   
2. 151 -151  

SELECT DATEDIFF('2010-06-01','2010-01-01'),DATEDIFF('2010-01-01','2010-06-01')
151	-151

注意：DATEDIFF函数也可以作用于date-and-time，但是它会忽略时间只显示天数

使用TIMEDIFF()函数计算出两个TIME类型之间的时间间隔

查看代码 复制到剪贴板

1. mysql> SELECT TIMEDIFF('12:12:12','11:11:11');   
2. +---------------------------------+   
3. | TIMEDIFF('12:12:12','11:11:11') |   
4. +---------------------------------+   
5. | 01:01:01                        |   
6. +---------------------------------+   
7. 1 row in set (0.00 sec)  

mysql> SELECT TIMEDIFF('12:12:12','11:11:11');
+---------------------------------+
| TIMEDIFF('12:12:12','11:11:11') |
+---------------------------------+
| 01:01:01                        |
+---------------------------------+
1 row in set (0.00 sec)

这里我添加一个TIMESTAMPDIFF(UNIT,TIME1,TIME2)函数

查看代码 复制到剪贴板

1. mysql>  SET @D1 = '1999-09-09 12:11:11', @D2 = '2009-09-09 12:11:11';   
2. Query OK, 0 rows affected (0.00 sec)   
3.   
4. mysql>  SELECT TIMESTAMPDIFF(MINUTE,@D1,@D2), TIMESTAMPDIFF(DAY,@D1,@D2);   
5. +-------------------------------+----------------------------+   
6. | TIMESTAMPDIFF(MINUTE,@D1,@D2) | TIMESTAMPDIFF(DAY,@D1,@D2) |   
7. +-------------------------------+----------------------------+   
8. | 5260320                       | 3653                       |   
9. +-------------------------------+----------------------------+   
10. 1 row in set (0.00 sec)  

mysql>  SET @D1 = '1999-09-09 12:11:11', @D2 = '2009-09-09 12:11:11';
Query OK, 0 rows affected (0.00 sec)

mysql>  SELECT TIMESTAMPDIFF(MINUTE,@D1,@D2), TIMESTAMPDIFF(DAY,@D1,@D2);
+-------------------------------+----------------------------+
| TIMESTAMPDIFF(MINUTE,@D1,@D2) | TIMESTAMPDIFF(DAY,@D1,@D2) |
+-------------------------------+----------------------------+
| 5260320                       | 3653                       |
+-------------------------------+----------------------------+
1 row in set (0.00 sec)

2，把要处理的时间转换为基本时间单位，天或者是秒，然后再计算时间间隔

TIME_TO_SEC()

查看代码 复制到剪贴板

1. SELECT t1,t2,TIME_TO_SEC(t2) - TIME_TO_SEC(t1),TIME_TO_SEC(t1) - TIME_TO_SEC(t2) FROM time_val   
2. 05:01:30    02:30:20    -9070   9070   
3. 15:00:00    15:00:00     0  0   
4. 12:30:20    17:30:45    18025   -18025  

SELECT t1,t2,TIME_TO_SEC(t2) - TIME_TO_SEC(t1),TIME_TO_SEC(t1) - TIME_TO_SEC(t2) FROM time_val
05:01:30	02:30:20	-9070	9070
15:00:00	15:00:00	 0	0
12:30:20	17:30:45	18025	-18025

TO_DAYS()

查看代码 复制到剪贴板

1. SELECT TO_DAYS('1999-09-09') - TO_DAYS('2009-09-09')   
2. -3653  

查看更多请到http://www.aiice.com/275.html