MySQL 使用sql计算类型时间的总和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3054123/
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
calculate a sum of type time using sql
提问by kawtousse
How to caculate sum of times of my colonne called "timeSpent" having this format: HH:mm in SQL? I am using MySQL.
如何计算具有以下格式的名为“timeSpent”的冒号的时间总和：SQL 中的 HH:mm？我正在使用 MySQL。
the type of my column is Time.
我的专栏类型是时间。
it has this structure
它有这个结构
TimeFrom like 10:00:00 12:00:00 02:00:00 TimeUntil 08:00:00 09:15:00 01:15:00 Time spent total time 03:15:00
回答by Siri
SELECT SEC_TO_TIME( SUM( TIME_TO_SEC( `timeSpent` ) ) ) AS timeSum
FROM YourTableName
This worked for me. Hope this helps.
这对我有用。希望这可以帮助。
回答by Developerscentral
100% working code to get sum of time out of MYSQL Database:
从 MYSQL 数据库中获取时间总和的 100% 工作代码：
SELECT
SEC_TO_TIME( SUM(time_to_sec(`db`.`tablename`)))
As timeSum
FROM
`tablename`
Try and confirm. Thanks.
尝试并确认。谢谢。
回答by Mike Dinescu
If the data type of the timeSpentcolumn is TIMEyou should be able to use the following query:
如果timeSpent列的数据类型是TIME您应该能够使用以下查询：
SELECT SUM(timeSpent)
FROM YourTableName -- replace YourTableName with the actual table name
However, if that is not the case, you may have to use a cast to convert to the Timedata type. Something like this should work:
但是，如果情况并非如此，您可能必须使用强制转换来转换为Time数据类型。这样的事情应该工作：
SELECT SUM(timeSpent - CAST('0:0:0' as TIME))
FROM YourTableName -- replace YourTableName with the actual table name
回答by Lèse majesté
In MySQL, you would do something like this to get the time interval:
在 MySQL 中，您可以执行以下操作来获取时间间隔：
SELECT TIMEDIFF('08:00:00', '10:00:00');
Then to add the time intervals, you would do:
然后添加时间间隔，你会这样做：
SELECT ADDTIME('01:00:00', '01:30:00');
Unfortunately, you're not storing dates or using 24-hour time, so these calculations would end up incorrect since your TimeUntilis actually lower than your TimeFrom.
不幸的是，您没有存储日期或使用 24 小时制，因此这些计算最终会不正确，因为您TimeUntil的TimeFrom.
Another approach would be (assuming you sort out the above issue) to store the time intervals as seconds using TIMESTAMPDIFF():
另一种方法是（假设您解决了上述问题）使用TIMESTAMPDIFF()以下方法将时间间隔存储为秒：
UPDATE my_table SET time_spent=TIMESTAMPDIFF(start, end));
SELECT SEC_TO_TIME(SUM(time_spent)) FROM my_table;
