php - 多个日期之间的小时数总和

Question

我需要将时间加起来，但减去状态为“停止”时的时间。我可以进行 SQL 查询还是需要结合 SQL + PHP？
我得到了这样一个数据库。

ID Date                 Status
1  2020.11.02 15:00     New 
1  2020.11.02 15:00     Start 
1  2020.11.02 15:05     Stop   
1  2020.11.02 15:10     Start
1  2020.11.02 16:15     End  
2  2020.11.02 16:00     New 
2  2020.11.02 16:00     Start 
2  2020.11.02 16:05     Stop   
2  2020.11.02 16:10     Start
2  2020.11.02 16:15     Stop   
2  2020.11.02 16:20     Start
2  2020.11.02 16:25     End  

我需要得到这个结果

ID SumTime
1  10 min
2  15 min

Answer 1

IF 对日期和时间进行排序，并为所有 start 提供一个 row_number在另一边 end或 stop并总结

CREATE TABLE table1 (
  `ID` VARCHAR(20),
  `Date` DATetime,
  `Status` VARCHAR(5)
);

INSERT INTO table1
  (`ID`, `Date`, `Status`)
VALUES
  ('1', '2020.11.02 15:00', 'New'),
  ('1', '2020.11.02 15:00', 'Start'),
  ('1', '2020.11.02 15:05', 'Stop'),
  ('1', '2020.11.02 15:10', 'Start'),
  ('1', '2020.11.02 16:15', 'End'),
  ('2', '2020.11.02 16:00', 'New'),
  ('2', '2020.11.02 16:00', 'Start'),
  ('2', '2020.11.02 16:05', 'Stop'),
  ('2', '2020.11.02 16:10', 'Start'),
  ('2',  '2020.11.02 16:15', 'Stop'),
  ('2', '2020.11.02 16:20', 'Start'),
  ('2', '2020.11.02 16:25', 'End');

SELECT t1.ID, SUM(TIMESTAMPDIFF(MINUTE,t1.`Date`,t2.`Date`)) complsum
FROM (SELECT  *,ROW_NUMBER() OVER (PARTITION BY `ID` ORDER BY `Date` ASC) AS row_num 
FROM table1 WHERE `Status` =  'Start') t1 
INNER JOIN (SELECT  *,ROW_NUMBER() OVER (PARTITION BY `ID` ORDER BY `Date` ASC) AS row_num 
FROM table1 WHERE `Status`  IN ('Stop','End')) t2 
ON t1.ID = t2.ID AND t1.`row_num` =  t2.`row_num`
GROUP BY t1.ID

ID | complsum:- | -------:1  |       702  |       15

分贝<> fiddle here