我有MYSQL数据库,我想编写一个查询,该查询将在DOWN和UP事件之间以秒为单位返回时差。我只需要DOWN和UP事件之间的区别,而不需要UP和DOWN事件之间的区别。
这是我的数据库记录的示例:
comment date_time type
146 2015-05-20 07:07:34 UP
146 2015-05-20 07:02:32 DOWN
146 2015-05-20 00:03:13 UP
146 2015-05-19 23:57:36 DOWN
所以因为我只需要DOWN和UP事件之间的区别,所以
2015-05-20 00:00:00
和2015-05-20 12:00:00
之间的区别应该是00:05:02
我有一个MYSQL查询:
SELECT UP1.comment, sum(timestampdiff(second, DOWN1.date_time, UP1.date_time)) AS down_time_d FROM
(SELECT comment, date_time, type FROM event
WHERE comment like '%146%'
AND event.date_time BETWEEN '2015-05-20 00:00.00' and '2015-05-20 13:23:58'
AND type = 'SENSOR_UP'
) AS UP1,
(SELECT comment, date_time, type FROM event
WHERE comment like '%146%'
AND event.date_time BETWEEN '2015-05-20 00:00.00' and '2015-05-20 13:23:58'
AND type = 'SENSOR_DOWN'
) AS DOWN1
WHERE UP1.comment=DOWN1.comment
GROUP BY comment
此查询的问题是返回DOWN UP和UP与DOWN之间的区别。返回结果,我的区别是
06:54:17
而不是我的期望00:05:02
。如果有更多的DOWN和UP记录,则会在DOWN和UP之间的所有可能组合之间产生差异。我应该在查询中更改什么,以便仅向我返回DOWN和UP事件之间的时间差总和?
感谢帮助
最佳答案
我要解释你的要求...
-系统停机的总时间
从DOWN
事件发生到任何后续事件之间,系统都已关闭。
如果该后续事件也是DOWN
,则它将继续保持关闭状态。
如果没有后续事件,则系统将保持关闭状态直到“现在”。
SELECT
comment,
SUM(down_time)
FROM
(
SELECT
down_event.comment,
TIMESTAMPDIFF(
second,
down_event.date_time,
COALESCE(
MIN(next_event.date_time),
'2015-05-20 13:23:58'
)
) AS down_time
FROM
event down_event
LEFT JOIN
event next_event
ON next_event.comment = down_event.comment
AND next_event.date_time > down_event.date_time
AND next_event.date_time BETWEEN '2015-05-20 00:00.00'
AND '2015-05-20 13:23:58'
WHERE
down_event.type = 'DOWN'
AND down_event.date_time BETWEEN '2015-05-20 00:00.00'
AND '2015-05-20 13:23:58'
GROUP BY
down_event.comment,
down_event.date_time
)
AS down_time
GROUP BY
comment
;
关于mysql - DOWN和UP事件之间的MYSQL时间戳差异,但UP和DOWN事件之间的时间戳差异,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/30349253/