Swarm Detection

CREATE TABLE daytime1(row_id char(250),hive_id char(250),doy decimal, year decimal,stamp timestamp, hive_weight_lbs decimal,quality char(250));

create table splits2(early_row_id char(250), hive_id char(250),dayofyear decimal, year decimal, early_time_stamp timestamp, early_hive_weight_lbs decimal,early_quality char(250), later_row_id char(250), later_time_stamp timestamp, later_hive_weight_lbs decimal,later_quality char(250));

insert into daytime1 select row_id, hive_id,EXTRACT(doy FROM hive_observation_time_local),EXTRACT(year FROM hive_observation_time_local), hive_observation_time_local, hive_weight_lbs,quality from hivetool where EXTRACT(HOUR FROM hive_observation_time_local) BETWEEN 6 AND 18;

insert into splits2 select d1.row_id,d1.hive_id,d1.doy,d1.year,d1.stamp, d1.hive_weight_lbs,d1.quality, d2.row_id, d2.stamp, d2.hive_weight_lbs, d2.quality from daytime1 d1 inner join daytime1 d2 on d1.doy = d1.doy and d1.year = d2.year and d1.hive_id = d2.hive_id where (d1.hive_weight_lbs-d2.hive_weight_lbs) >2 and (d1.hive_weight_lbs-d2.hive_weight_lbs) <15 and (extract(epoch from d2.stamp- d1.stamp))<1200 and (extract(epoch from d2.stamp- d1.stamp))>30