CODEWITHSUNDEEP
mysql
Ram
Ram

Reputation: 35

How can I sum time in MySQL?

DOWNTIME

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?

Upvotes: 0

Views: 4091

Answers (2)

Dimos
Dimos

Reputation: 8918

Use the following query :

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

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

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';

Upvotes: 3

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

Steps:

  1. Fetch time duration values for given date range
  2. Sum all the time duration values
  3. Cast the result back to time, which is nothing but total time duration

Example: 

mysql> SELECT CAST(SUM(time_duration) AS TIME) AS total_time_duration FROM (
    ->        SELECT CAST('01:08:00' AS TIME) time_duration
    ->        UNION
    ->        SELECT CAST('00:39:00' AS TIME)
    -> ) date_range_results;
+---------------------+
| total_time_duration |
+---------------------+
| 01:47:00            |
+---------------------+
1 row in set (0.00 sec)

mysql>

Upvotes: 0

Related Questions