信号灯mysql存储过程计算时间段

delimiter $$

drop procedure if exists count_area;
create procedure count_area()
begin
declare done int default false;
declare g time;
declare f time;
declare r time;
declare pre time;
declare cur cursor for select green,flash,red from SignalStatus;
declare continue handler for sqlstate '02000' set done=true;
set pre=SEC_TO_TIME(43200);
open cur;
fetch cur into g,f,r;
while(not done) do
-- select TIME_TO_SEC(g);
update SignalStatus set GreenArea=TIME_TO_SEC(f)-TIME_TO_SEC(g),FlashArea=TIME_TO_SEC(r)-TIME_TO_SEC(f),RedArea=TIME_TO_SEC(g)-TIME_TO_SEC(pre) where green=g;
set pre=r;
if TIME_TO_SEC(g)>=TIME_TO_SEC('13:00:23') then
set done=true;
end if;
fetch cur into g,f,r;
end while;
close cur;
select * from SignalStatus;
end;
$$

call count_area();
$$

delimiter ;

 

区间段统计

select * from SignalStatus;
select TIME_TO_SEC('12:00:00');
set @st='12:40:00';
set @et='12:50:00';
select CONCAT(@st,'-',@et) as 'Period',sum(RedArea),sum(GreenArea),sum(FlashArea),count(1) as 'Times' from SignalStatus where TIME_TO_SEC(green)>=TIME_TO_SEC(@st) and TIME_TO_SEC(green)<=TIME_TO_SEC(@et);