mysql - 根据 MySQL 中的时间戳选择条目中的间隙

Question

我有一个非常简单的表格，例如:box，some_data，unix_timestamp

理想情况下，每个盒子每小时都会有一个新的时间戳。在生成丢失条目的每日报告时，我如何选择条目中的间隙(其中某个框丢失了一两个小时)？我是否需要选择当天的所有数据并慢慢解析它，或者是否有更快的方法在 MySQL 查询中执行此操作？

例如

SELECT 时间戳 WHERE [[另一个时间戳在 65 分钟内未出现]]

所以我的报告数据基本上是......

错过的条目:

方框 2:2:01 am 。 。 。 4:01 am(缺少 3:01 的条目)

Box 8:6:05 am 。 。 。 上午 10:05(缺少 7:05、8:05 和 9:05)

Box 28:3:15 am 。 。 。 (仍然离线!)

这可能有很多盒子，所以我想以最有效的方式解决这个问题。如果有人有关于如何在不选择一整天的数据并解析它的情况下解决这个问题的想法，我很想听听。

谢谢!!!

Answer 1

仅使用一个查询，我只能想到这个选项，它不会给你你需要的东西，因为它只适用于最多跳过一小时的情况，但不适用于例如跳过了两个条目左右:

SELECT DATE_ADD(timestamp, INTERVAL 1 HOUR) FROM table t1
WHERE NOT EXISTS (
    SELECT * FROM t2 WHERE t2.timestamp = DATE_ADD(t1.time, INTERVAL 1 HOUR)
) 

因此，我建议您使用您最喜欢的编程语言在应用程序级别实现此功能。

这是一些关于我如何做到这一点的伪代码:

result := [result set coming from your database]
// assuming that result is returned sorted by timestamp (i.e. ORDER BY timestamp)

i = 0
for j in 0..23
    elem := [ith elem from result]
    if hour(elem) <= j
        i++
    else
        add_to_result(j)