MySql -- 通过查询判断缺失数据的周期

Question

我有一个这样设置的数据库:

(Schema Name)
Historical
     -CID      int UQ AI NN 
     -ID       Int PK
     -Location Varchar(255)
     -Status   Varchar(255)
     -Time     datetime

所以条目可能看起来像这样

433275 | 97  | MyLocation | OK | 2013-08-20 13:05:54

我的问题是，如果我希望从我的每个站点获得 5 分钟间隔的数据，我如何才能确定站点关闭了多长时间？

例如，如果 MyLocation 没有发送从 13:05:54 到 14:05:54 的 5 分钟间隔数据，它就会错过 60 分钟的间隔时间，我怎么能找到这个停机时间并报告它容易吗？

谢谢，

Answer 1

*免责声明:我假设您的时间列决定了表中条目的顺序，并且您不能轻易(并且不会造成严重的性能损失)在 auto_increment 列上自行加入表，因为它可能包含间隙.*

要么创建一个只包含日期时间值的表，然后执行一个

FROM datetime_table d
LEFT JOIN your_table y ON DATE_FORMAT(d.datetimevalue, '%Y-%m-%d %H:%i:00') = DATE_FORMAT(y.`time`, '%Y-%m-%d %H:%i:00')
WHERE y.some_column IS NULL

(此处使用 date_format() 函数去除日期时间值中的秒部分)。

或者您使用用户定义的变量。

SELECT * FROM (
SELECT
y.*,
TIMESTAMPDIFF(MINUTE, @prevDT, `Time`) AS timedifference
@prevDT := `Time`
FROM your_table y ,
(SELECT @prevDT:=(SELECT MIN(`Time`) FROM your_table)) vars
ORDER BY `Time` 
) sq
WHERE timedifference > 5

编辑: 我以为您想扫描整个表(或其中的一部分)以查找与前一行的时间差大于 5 分钟的行。要检查特定 ID(并且仍然具有与免责声明中相同的假设)，您必须采用不同的方法:

SELECT
TIMESTAMPDIFF(MINUTE, (SELECT `Time` FROM your_table sy WHERE sy.ID < y.ID ORDER BY ID DESC LIMIT 1), `Time`) AS timedifference
FROM your_table y 
WHERE ID = whatever

编辑 2:

当您说“如果 ID 当前已关闭”时，您的表中是否已经有一个条目？如果没有，您可以通过

简单地检查一下

SELECT TIMESTAMPDIFF(MINUTE, NOW(), (SELECT MAX(`Time`) FROM your_table WHERE ID = whatever));