Mysql timestampdiff 重叠间隔之间的总和

Question

我有以下表结构:

+----+---------------------+---------------------+  
| id | created_at          | closed              |  
+----+---------------------+---------------------+  
|  1 | 2017-04-03 04:00:00 | 2017-04-03 04:30:00 |  
|  2 | 2017-04-03 04:25:00 | 2017-04-03 04:35:00 |  
+----+---------------------+---------------------+  

在我真正的 SQL 查询中，我使用 timestampdiff 来计算时间戳字段中两个日期之间的差异。如果所有记录的 ctreatment_at 和 close_at 以不同的时间间隔开始，则没有问题，因此我能够获取所有行消耗的时间，如下所示:

SELECT SUM(timestampdiff(SECOND, created_at, closed_at)) as TotalTime FROM table

我的问题如上表所示，时间间隔交叉。上面的查询将得到 900 但我应该得到 600

我尝试过类似的方法:

SELECT timestampdiff(
                    MINUTE,
                    a.created_at,
                    (SELECT max(b.closed) from times as b WHERE b.created_at < a.closed)
                    ) as periods
FROM `times` as a

结果是:

+---------+
| periods |
+---------+
|      35 |
|      10 |
+---------+

这里我只想要第一个结果，它代表重叠周期的净时间。将 MAX 与上一个查询一起使用应该返回第一条记录，但它会导致忽略在 futere 中添加的任何其他周期组，假设表变为:

+----+---------------------+---------------------+
| id | created_at          | closed              |
+----+---------------------+---------------------+
|  1 | 2017-04-03 04:00:00 | 2017-04-03 04:30:00 |
|  2 | 2017-04-03 04:25:00 | 2017-04-03 04:35:00 |
|  3 | 2017-04-03 05:00:00 | 2017-04-03 05:15:00 |
+----+---------------------+---------------------+

以上查询返回:

+---------+
| periods |
+---------+
|      35 |
|      10 |
|      15 |
+---------+

在这里，我再次不需要第二条记录，因为它描述了之前已计入第一条记录的时期。事实上，我不知道MySQL是否可能只获取(对于上面的示例)两条记录甚至三条记录，但第二条记录是空、0、负值等，以便与其他实际值区分开来。

Answer 1

您需要按每小时的时间和最大的拍摄差异时间进行分组。

SELECT max(timestampdiff(
                    MINUTE,
                    a.created_at,closed)  as periods
FROM `times` as a group by hour(created_at)

现在您将得到按照您的要求的结果。