interval在物联网场景中的应用案例

应用场景：监控区域内工地的噪音值，每隔1小时生成一条数据，比如0:00-1:00,1:00-2:00，如果这个时间段没有数据就返回0。

noise1 = rand(80,2000) ;
noise2 = rand(80, 2000);
device = rand(1..10, 2000);
rec_time = rand(2022.01.01 00:00:00..2022.12.30 11:59:59,2000)


t= table(device, noise1, rec_time)
data_a_day = select * from t where date(rec_time)=2022.01.01

//插入2条时间的占位数据
place_holder1 = [int(NULL), double(NULL), 2022.01.01T23:59:59.999]
place_holder2 =  [int(NULL), double(NULL), 2022.01.01 00:00:00.000]
insert into data_a_day values(place_holder1)
insert into data_a_day values(place_holder2)

select 
	max(noise1) as max_noise1
from data_a_day
where  date(rec_time)=2022.01.01 //传天参数
group by interval(rec_time, 1H, 0) as interval_1h
order by interval_1h