All geek questions in one place

Calculate total time without overlapping time and breaks in MySQL

There is a list of start and end times in the selection request. I need to find out the total time, excluding overlap times and any breaks.

StartTime EndTime 2014-10-01 10:30:00.000 2014-10-01 12:00:00.000 -- 90 mins 2014-10-01 10:40:00.000 2014-10-01 12:00:00.000 --0 since its overlapped with previous 2014-10-01 10:42:00.000 2014-10-01 12:20:00.000 -- 20 mins excluding overlapped time 2014-10-01 10:40:00.000 2014-10-01 13:00:00.000 -- 40 mins 2014-10-01 10:44:00.000 2014-10-01 12:21:00.000 -- 0 previous ones have already covered this time range 2014-10-13 15:50:00.000 2014-10-13 16:00:00.000 -- 10 mins

Therefore, the amount should be 160 minutes in this case.

I have ideas with lots of loops and if's. Just look for some simple solutions, if available.

0
source share
2 answers

This is a problem with spaces and islands. Here is one way to solve the problem.

 SELECT SUM(minutes) total FROM ( SELECT TIMESTAMPDIFF(MINUTE, MIN(starttime), MAX(endtime)) minutes FROM ( SELECT starttime, endtime, @g := IF(@e BETWEEN starttime AND endtime OR endtime < @e, @g, @g + 1) g, @e := endtime FROM table1 CROSS JOIN ( SELECT @g := 0, @e := NULL ) i ORDER BY starttime, endtime ) q GROUP BY g ) q

Conclusion:

  |  TOTAL |
 | ------- |
 |  160 |

Here is the SQLFiddle demo

+1
source

Here's a frankly pretty bad and incomplete solution ...

 SELECT SUM(TIME_TO_SEC(n))/60 FROM ( SELECT MAX(diff) n FROM ( SELECT x.* , MAX(y.endtime) max_endtime , TIMEDIFF(MAX(y.endtime),x.starttime) diff FROM my_table x JOIN my_table y ON y.endtime >= x.starttime AND y.starttime <= x.endtime GROUP BY x.id ) a GROUP BY max_endtime ) z;

In the unlikely event that no one hits me, I will post something better later.

0
source

Source: https://habr.com/ru/post/1208061/

More articles:


All Articles