计算不包括重叠时间的总时间 &MySQL 中断 [英] Calculate total time excluding overlapped time & breaks in MySQL

问题描述

有一个选择查询的开始时间和结束时间列表.我需要找出不包括重叠时间和任何休息时间的总时间.

StartTime EndTime2014-10-01 10:30:00.000 2014-10-01 12:00:00.000 -- 90 分钟2014-10-01 10:40:00.000 2014-10-01 12:00:00.000 --0 因为它与之前的重叠2014-10-01 10:42:00.000 2014-10-01 12:20:00.000 -- 20 分钟(不包括重叠时间)2014-10-01 10:40:00.000 2014-10-01 13:00:00.000 -- 40 分钟2014-10-01 10:44:00.000 2014-10-01 12:21:00.000 -- 之前的 0 个已经涵盖了这个时间范围2014-10-13 15:50:00.000 2014-10-13 16:00:00.000 -- 10 分钟

所以在这种情况下，总数应该是 160 分钟.

我有很多循环和 if 的想法.如果可以，只是寻找一些简单的解决方案.

解决方案

这是一个间隙和孤岛类型的问题.这是解决它的一种方法

SELECT SUM(minutes) total从(SELECT TIMESTAMPDIFF(MINUTE, MIN(starttime), MAX(endtime)) 分钟从(选择开始时间，结束时间，@g := IF(@e BETWEEN starttime AND endtime OR endtime < @e, @g, @g + 1) g,@e := 结束时间FROM table1 交叉连接(选择 @g := 0, @e := NULL) 一世按开始时间、结束时间排序) q按克分组) q

输出:

<前>|总计 ||-------||160 |

这是一个 SQLFiddle 演示

There is a list of start time and end times from a select query. I need to find out the total time excluding the overlapping time 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

So the total should be 160 mins in this case.

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

解决方案

This is a gaps-and-islands type of problem. Here is one way to tackle it

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

Output:

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

Here is a SQLFiddle demo

这篇关于计算不包括重叠时间的总时间 &amp;MySQL 中断的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！