mysql - 如何获得日期时间范围的总小时数

Question

如何根据我的日期时间范围计算总小时数

如果我有这个接受的日期范围 2015-09-07 06:00:00 - 2015-09-11 22:00:00

我只想获取用户每天从 06:00:00 到 22:00:00 的小时数，然后对所有小时数求和。

这是我的示例数据

+----+---------+--+---------------------+---------------------+--+
|    |         |  |                     |                     |  |
+----+---------+--+---------------------+---------------------+--+
| id | user_id |  | start_time          | end_time            |  |
| 1  | 24      |  | 2015-09-07 07:10:01 | 2015-09-07 10:20:20 |  |
| 2  | 24      |  | 2015-09-07 12:10:00 | 2015-09-07 15:00:00 |  |
| 3  | 24      |  | 2015-09-07 16:00:00 | 2015-09-07 22:20:20 |  |
| 4  | 24      |  | 2015-09-07 23:25:20 | 2015-09-08 05:00:00 |  |
+----+---------+--+---------------------+---------------------+--+  

提前谢谢你。

Answer 1

简单地使用函数 TIMESTAMPDIFF 几个小时:

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timestampdiff

示例:

SELECT TIMESTAMPDIFF(HOUR, start_time, end_time) 
           as `difference` FROM timeattendance WHERE user_id = 24