CODEWITHSUNDEEP
phpmysql
Per76
Per76

Reputation: 184

Sum datetime in MySQL rows

Question:

Is it possible to group all rows in MySQL and sum the hours? 

Starttime - type:datetime, Stoptime - type:datetime

Data

Starttime, stoptime
2016-10-25 09:00:00, 2016-10-29 17:00:00
2016-11-26 09:00:00, 2016-11-26 17:00:00
2016-11-30 09:00:00, 2016-11-30 17:00:00
2017-01-28 09:00:00, 2017-01-28 17:45:00

The code below sums all the hours between 2016-10-25 and 2017-01-28. I need it to sum all the time on every row and then all rows together. Is it possible to sum this in MySQl or am I bound to PHP?

SELECT 
MIN(starttime), 
MAX(stoptime), 
SUM(TIME_TO_SEC(TIMEDIFF(stoptime, starttime))/3600) AS total_hours 
FROM mytable WHERE id = 1219

Upvotes: 1

Views: 1582

Answers (1)

Olaf Dietsche
Olaf Dietsche

Reputation: 74008

To sum up all differences, you don't need min(starttime) or max(stoptime), just use sum of timestampdiff, e.g.

select sum(timestampdiff(hour, starttime, stoptime))
from mytable

If you want fractional hours, keep time_to_sec(...) / 3600 as in your question

select sum(time_to_sec(timediff(stoptime, starttime)) / 3600)
from mytable

See also sqlfiddle

Upvotes: 3

Related Questions