Mysql 计算相邻两条记录的时间差

旧城等待， 2022-10-06 05:47 303阅读 0赞

# 为什么使用 #

项目中一个设备的数据每五秒发送一次写入数据库中  
![在这里插入图片描述][20210615095816555.png]  
但是，如果设备损坏或者出现什么意外，没有传递信息。  
这是，领导就说我要这个设备的工作时间段，就是两条数据之间的间隔超过一定时间后就是不同的时间段。  
左思右想，可以用循环啊！！！  
领导：不行！这然执行效率太慢了！  
没办法只能，从新想办法，最终在网上找到了数据库的方法。

# 结果 #

首先我在这里将结果列出来  
![在这里插入图片描述][20210615100632253.png]

select A.DeviceId,B.Time endTime,A.Time startTime
            from(
                select h.DeviceId, h.Time ,(@i := @i + 1) as ord_num from data_device_bdcard_history h,(select @i := 1) b where h.DeviceId = '410103000BDC0001' order by Time
            ) as A LEFT JOIN (
                select h.DeviceId, h.Time ,(@j := @j + 1) as ord_num from data_device_bdcard_history h,(select @j := 0) c where h.DeviceId = '410103000BDC0001'  order by Time
            ) as B on A.ord_num=B.ord_num
            WHERE TIMESTAMPDIFF(SECOND,A.Time,B.Time) > 5

当然这个时间段是有问题的，因为这个时间段是没有第一个开始时间和最后一个结束时间的。  
这时，只要做一个小小的循环就可以了。  
在这里我就不演示了，毕竟主要讲的是**Mysql 计算相邻两条记录的时间差**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h0MzE0MTU5_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h0MzE0MTU5_size_16_color_FFFFFF_t_70 1]

select h.DeviceId, h.Time ,(@i := @i + 1) as ord_num from data_device_bdcard_history h,(select @i := 1) b where h.DeviceId = '410103000BDC0001' order by Time

和

select h.DeviceId, h.Time ,(@j := @j + 1) as ord_num from data_device_bdcard_history h,(select @j := 0) c where h.DeviceId = '410103000BDC0001'  order by Time

查询**data\_device\_bdcard\_history**表（设备保存数据表），根据设备编号查、根据时间排序。  
返回：设备ID，数据加入的时间，以及每一条数据添加的编号。一个从1开始 **表B**，一个从2开始**表A**。  
之后根据**TIMESTAMPDIFF**函数查询时间的差值。  
如果在一个表中**表B**为上一条数据，**表A**下一条数据，  
计算出差值之后判断是否超过规定时间段。  
超过时间段，就将上一条数据作上一个时间段的结束时间，下一条数据作为下一个时间段的开始时间。  
结果：

![在这里插入图片描述][20210615100632253.png]

[20210615095816555.png]: /images/20221005/4268ae99f7414cb9a30ccc4deefd9925.png
[20210615100632253.png]: /images/20221005/916f955799284507b558f20d1b73dac5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h0MzE0MTU5_size_16_color_FFFFFF_t_70]: /images/20221005/10523ce01ff64ff48564eae375513e8a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h0MzE0MTU5_size_16_color_FFFFFF_t_70 1]: /images/20221005/a13e813ee3054943880319d421e5a100.png