mysql - 每天多个持续时间的 SUM timestampdiff

Question

我的恒温器有一张 table 。它记录数据如下。所以当它打开时，我得到一个时间状态，状态 1 表示打开，状态 0 表示加热关闭。此外，每次开/关时，它都会为我提供每天的总供暖量。

Date                | Status | Total_heatings
2019-01-20 10:00:00 | 1      | 1
2019-01-20 10:10:00 | 0      | 1
2019-01-20 14:00:00 | 1      | 2
2019-01-20 14:25:00 | 0      | 2
2019-01-20 18:00:00 | 1      | 3
2019-01-20 18:15:00 | 0      | 3
2019-01-21 01:00:00 | 1      | 1
2019-01-21 01:30:00 | 0      | 1
2019-01-21 06:00:00 | 1      | 2
2019-01-21 06:15:00 | 0      | 2

我正在尝试按天计算总持续时间。我尝试了下面的脚本，它为我提供了每天多次加热的持续时间。当我使用 SUM(TIMESTAMPDIFF(Minute,Min(Date),MAX(Date))) 时，由于分组使用错误而引发错误。

SELECT 
   DATE_FORMAT(Date, '%d.%m') AS 'day',
   TIMESTAMPDIFF(MINUTE,MIN(Date),MAX(Date)) AS 'Duration'
FROM thermostat 
   WHERE (Date BETWEEN '2019-01-21 00:00:00' + INTERVAL -7 DAY AND '2019-01-21 00:00:00') 
   GROUP BY DAY(Date),Total_heatings;

我所需要的只是每天获取这些不同供暖时间的总和。所以结果应该是这样的:

Day   | Duration
20.01 | 50
21.01 | 45

现在我无法进一步总结每天的所有供暖时间，例如每天的总持续时间。非常感谢您的指点和帮助。

Answer 1

此查询适用于 8.0 之前的 MySQL 版本。它使用 SELF JOIN 为给定的 heater on 行查找匹配的 heater off 行。在不存在匹配行的情况下，它会使用当天的结束时间或当前时间，以较低者为准。

SELECT DATE_FORMAT(t1.Date, '%d.%m') AS `day`,
       SUM(TIMESTAMPDIFF(MINUTE, t1.Date, COALESCE(t2.Date, LEAST(NOW(), DATE(t1.Date) + INTERVAL 1 DAY)))) AS Duration,
       MAX(t1.Total_heatings) AS Total_heatings
FROM thermostat t1
LEFT JOIN thermostat t2 ON t2.Status = 0 AND t2.Total_heatings = t1.Total_heatings AND DATE(t2.Date) = DATE(t1.Date)
WHERE t1.Status = 1 AND DATE(t1.Date) BETWEEN '2019-01-21' - INTERVAL 7 DAY AND '2019-01-21' 
GROUP BY `day`

输出:

day     Duration    Total_heatings
20.01   50          3
21.01   45          2

Demo on dbfiddle