mysql查询中日期时间差的总和 [英] Sum of date time difference in a mysql query
本文介绍了mysql查询中日期时间差的总和的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
mysql表列是
Starttime - type:datetime, Stoptime - type:datetime
数据
Starttime, stoptime
2013-10-25 09:00:00, 2013-10-25 17:00:00
2013-10-26 09:00:00, 2013-10-26 17:00:00
2013-10-27 09:00:00, 2013-10-27 17:00:00
2013-10-28 09:00:00, 2013-10-28 17:45:00
查询
select, min(starttime), max(stoptime),
SUM(TIME_TO_SEC(TIME_DIFF(stoptime - starttime))) total_hours from mytable
返回秒数,我可以使用TIME_TO_SEC()
将其转换回时间...它以HH:MM:SS
格式给出结果,例如28:45:00
returns a number of seconds which I can convert back to time using TIME_TO_SEC()
... it gives me result in the format HH:MM:SS
e.g. 28:45:00
但是我需要将28:45:00转换为28.75小时格式.我该怎么办?
But I need to convert 28:45:00 into 28.75 hours format. How do I do that?
推荐答案
select, min(starttime), max(stoptime),
SUM(TIME_TO_SEC(TIME_DIFF(stoptime - starttime))/3600) total_hours from mytable
这篇关于mysql查询中日期时间差的总和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文