[antibot]

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);