MySQL - 如何求和时间？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1329458/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL - How to SUM times?
提问by John M
I have a table that contains date-time values in this format:
我有一个包含以下格式的日期时间值的表：
START
开始
1/13/2009 7:00:00AM
1/13/2009 7:00:00AM
END
结尾
1/13/2008 2:57:00PM
2008/1/13 下午 2:57:00
I use the 'str to date' function to convert these into a date-time format.
我使用“str to date”函数将这些转换为日期时间格式。
How do I calculate a difference between them? And then sum it all up so that it displays in total hours (ie total hours for week is 40:53).
我如何计算它们之间的差异？然后将其汇总起来，以总小时数显示（即一周的总小时数为 40:53）。
I was trying the timediff function but the results don't sum.
我正在尝试 timediff 函数，但结果不求和。
采纳答案by Eric Petroelje
Try looking into UNIX_TIMESTAMPand SEC_TO_TIME.
尝试查看UNIX_TIMESTAMP和SEC_TO_TIME。
You would sum up the differences between the timestamps, then use that value (would be in milliseconds) to get the time:
您将总结时间戳之间的差异，然后使用该值（以毫秒为单位）来获取时间：
SELECT SEC_TO_TIME(time_milis / 1000)
FROM (
SELECT SUM(UNIX_TIMESTAMP(date1) - UNIX_TIMESTAMP(date2)) as time_milis
FROM table
)
回答by vhan
I think you have to use this code... it works :)
我认为您必须使用此代码...它有效:)
SELECT
SEC_TO_TIME( SUM( TIME_TO_SEC( `time` ) ) ) AS total_time
FROM time_table;
回答by Jason
Check out the MySQL DateDiff Function
SELECT SUM(DATEDIFF(endtime, starttime))
FROM somerecordstable
WHERE starttime > '[some date]' AND endtime <= '[some date]'
You could also try:
你也可以试试：
SELECT SUM(TIMEDIFF(endtime, starttime))
FROM somerecordstable
WHERE starttime > '[some date]' AND endtime <= '[some date]'
I haven't tested the second one, but I know the first one should work.
我还没有测试第二个，但我知道第一个应该有效。
回答by dnagirl
SELECT some_grouping_column, SUM(TIMEDIFF(datecol1,datecol2)) as tot_time
FROM my_table
GROUP BY some_grouping_column;
