mysql停止更新时间_我如何更新这个MySQL查询以获取从开始、停止、暂停和恢复事件经过的总时间...

我有一个查询,我可以创建从搜索通过SOF。

SELECT

start_log.reportID,

start_log.userID,

start_log.testID,

MAX(start_log.eventDateTime) AS start_time,

end_log.eventDateTime AS end_time,

TIMESTAMPDIFF(MINUTE, MAX(start_log.eventDateTime), end_log.eventDateTime) AS diff

FROM

testtracker_event AS start_log

INNER JOIN

testtracker_event AS end_log ON (

start_log.userID = end_log.userID

AND

start_log.reportID = end_log.reportID

AND

start_log.testID = end_log.testID

AND

end_log.eventDateTime > start_log.eventDateTime)

WHERE start_log.eventType = 'start'

AND end_log.eventType = 'finish'

AND start_log.testID = 2

AND start_log.userID = '$user'

GROUP BY

start_log.userID,

start_log.testID,

start_log.eventDateTime,

start_log.reportID

ORDER BY

-- userID DESC,

start_time DESC

按预期工作,查询返回

start_time

和

end_time

作为

diff

列。我现在想扩展这个查询以考虑暂停和恢复事件,我只是在理解如何在当前查询中考虑这些事件时遇到了困难。我可以看到扩展select来解释pause和resume,列如下

pause_time

和

resume_time

除了如何解释可能有多个暂停和恢复事件的情况之外?我还冒昧地猜测,我可能需要做一些额外的计算才能得到实际经过的时间,但我也不确定如何引用它们。

任何帮助都将不胜感激。

样本数据

reportID|testID|eventDateTime |eventType

--------|------|-------------------|----------

11881 | 2 |2013-08-16 14:19:09|start

11910 | 2 |2013-08-16 14:48:04|start

11910 | 2 |2013-08-16 14:55:08|finish

11891 | 2 |2013-08-16 15:01:01|start

11891 | 2 |2013-08-16 15:25:42|finish

11888 | 2 |2013-08-16 16:20:27|start

11888 | 2 |2013-08-16 16:51:16|finish

11889 | 2 |2013-08-19 09:00:13|start

11889 | 2 |2013-08-19 10:31:17|finish

11905 | 2 |2013-08-19 12:10:00|start

11898 | 2 |2013-08-19 12:12:02|start

11898 | 2 |2013-08-19 12:19:28|finish

11905 | 2 |2013-08-19 12:27:21|finish

11880 | 2 |2013-08-19 16:40:26|start

11880 | 2 |2013-08-19 16:40:27|finish

11895 | 2 |2013-08-19 16:42:58|start

11895 | 2 |2013-08-19 16:55:14|finish

11908 | 2 |2013-08-20 09:46:11|start

11908 | 2 |2013-08-20 09:56:20|finish

11862 | 2 |2013-08-20 10:20:11|start

11862 | 2 |2013-08-20 10:29:44|finish

11911 | 2 |2013-08-20 11:31:33|start

11911 | 2 |2013-08-20 11:59:15|finish

11913 | 2 |2013-08-20 12:44:07|start

11913 | 2 |2013-08-20 13:32:52|finish

11928 | 2 |2013-08-20 14:28:38|start

11928 | 2 |2013-08-20 14:40:00|finish

11836 | 2 |2013-08-20 16:26:03|start

11887 | 2 |2013-08-20 16:30:36|start

11836 | 2 |2013-08-20 16:43:53|finish

11887 | 2 |2013-08-20 16:58:44|pause

11902 | 2 |2013-08-22 14:51:53|start

11902 | 2 |2013-08-22 15:01:05|finish

11912 | 2 |2013-08-22 15:01:43|start

11912 | 2 |2013-08-22 15:12:21|finish

11930 | 2 |2013-08-22 16:09:22|start

11814 | 2 |2013-08-14 10:39:07|finish

11930 | 2 |2013-08-23 09:55:14|finish

11914 | 2 |2013-08-26 14:55:04|start

11914 | 2 |2013-08-26 15:19:48|pause

11940 | 2 |2013-08-27 07:44:12|start

11940 | 2 |2013-08-27 07:55:52|finish

11914 | 2 |2013-08-27 09:13:28|resume

11914 | 2 |2013-08-27 09:13:44|pause

11914 | 2 |2013-08-27 09:15:56|resume

11914 | 2 |2013-08-27 09:30:12|finish

已从示例中删除userId列以保护标识。

不是预期的结果

reportID - 11914

reportID|testID|start|end|diff

--------|------|-----|---|-----

11914 | 2 |2013-08-26 14:55:04|2013-08-27 09:30:12|1115

预期结果

reportID - 11902

reportID|testID|start|end|diff

--------|------|-----|---|-----

11902 | 2 | 2013-09-10 14:26:53 | 2013-09-10 14:45:36 | 18