use example;
drop procedure if exists example;
delimiter //
create procedure example(in _at char(32),in _to char(32), in step int(2))
not deterministic
sql security invoker
comment 'example'
begin
declare _user_id, _minutes int;
declare _login, _reg_time varchar(64);
declare m int default step;
declare max int;
declare count int;
declare user_ids text;
declare done int default 0;
declare cur cursor for select id_user, login, reg_time, round(time_to_sec(timediff(reg_time,str_to_date(_at, '%Y-%m-%d %H:%i:%s')))/60, 0) as minutes
from user where date(reg_time) between _at and _to order by reg_time asc;
declare continue handler for not found SET done = 1;
drop temporary table if exists tmpUserReg;
create temporary table tmpUserReg (
id int not null auto_increment ,
user_ids text null ,
reg_count varchar(45) null ,
minutes int(11) null ,
primary key (`id`)
);
select datediff(date_add(_to,interval 1 day),_at)*24*60 into max;
while (m<=max) do
set count=0;
set user_ids='';
set done=0;
open cur;
repeat
fetch cur INTO _user_id, _login, _reg_time, _minutes;
if not done then
# In ascending order
/*
if _minutes<=m then
set user_ids=concat(user_ids, _user_id, ' ');
set count=count+1;
end if;
*/
# The segments for step
if _minutes>=m and _minutes<=m+step then
set user_ids=concat(user_ids, _user_id, ' ');
set count=count+1;
end if;
end if;
until done end repeat;
close cur;
insert into tmpUserReg values(null,user_ids,count,m);
set m=m+step;
end while;
select * from tmpUserReg;
end;
//
call example('2009-09-21','2009-09-23',10); |
