如何在MySQL中汇总时间? [英] How can I sum time in MySQL?

问题描述

如您在图像中看到的，我有一个停机报告，其中显示了所选日期所选工厂的停机时间.现在，我想在"Time Duration"列中添加所有值，并在"TOTAL TIME DURATION"附近的单独显示中显示它.例如，在图像中，选定的日期是2015年3月17日和2015年3月30日，选定的工厂是Raw Mill.该报告显示了所选13天的Raw Mill的停机时间.我想将这13天的所有时间都添加到"Time duration"列中，并将其显示在"TOTAL TIME DURATION"附近的单独显示中.时间格式为HH:MM:SS.我怎样才能做到这一点?

As you can see in the image I have a downtime report that shows downtime of the selected mill for the selected date. Now I want to add all the values in the "Time Duration" column and show it in a separate display near "TOTAL TIME DURATION". For example, in the image, the selected dates are 17th March 2015 and 30th March 2015 and the selected mill is Raw Mill. the report shows the downtime for Raw Mill for the selected 13 days. I want to add all the time in the "Time duration" column for these 13 days and show it in a separate display near "TOTAL TIME DURATION". The time format is HH:MM:SS. How can I do this?

推荐答案

使用以下查询:

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(Time_Duration))) FROM TableName AS 'TOTAL TIME DURATION';

但是，有时总持续时间可能会超过最大允许时间值，即"838:59:59".因此，通常可以使用以下查询来计算溢出的总和:

However, sometimes the total duration can exceed the maximum allowed time value, which is "838:59:59". So, you can generally use the following query which makes the calculation of the overflowed sum :

SELECT CONCAT_WS(' hours and ',
  ((SUM(TIME_TO_SEC(Time_Duration)) DIV (TIME_TO_SEC('838:59:59')+1))*839), 
  (SEC_TO_TIME(SUM(TIME_TO_SEC(Time_Duration)) MOD (TIME_TO_SEC('838:59:59')+1))) 
) 
FROM TableName AS 'TOTAL TIME DURATION'

这将以以下形式给出输出:

This will give an output in the form :

3 hours and 3:45:05

您还可以按照所需的确切格式获取结果，例如6:45:05，带有以下自定义查询:

You can also get the result in the exact format you want e.g. 6:45:05, with the following custom query :

SELECT CONCAT_WS(
    ':', 
    (SUM(TIME_TO_SEC(Time_Duration)) DIV 3600, 
    (SUM(TIME_TO_SEC(Time_Duration)) MOD 3600) DIV 60, 
    (SUM(TIME_TO_SEC(Time_Duration)) MOD 3600) MOD 60)
FROM TableName AS 'TOTAL TIME DURATION';

这篇关于如何在MySQL中汇总时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！