MySQL query to group results by date range?

Question

I got a system that pings to the database every 2 to 5 seconds when he is connected the application. Depending on his connection, the ping timeframe can be bigger, like 10 seconds or so.

Example:

Pings: 1,4,6,8,9,12,16,20,50,180,187,189,200,203,206,210 ...

I want run a query to grab ranges that does not exceed 1 minute between the pings, group them, so I can tell for how long the user has been connected, and save into a new table like:

Connections table:
user: X | start_date: 1   | end_date: 50  | duration: 49
user: X | start_date: 180 | end_date: 210 | duration: 30

Since the pings from ,50,180, ... that exceeded 1 minute, where disconsidered by the query groups.

The pings are stored as timestamp, so can be easily converted to date, and the range from the last ping to the first one will give me the session duration.

Is there anyway to do it in a MySQL query? Any help?

EDIT: Adding ping history schema

id  int(11)
user_id int(11)
ping_timestamp  int(11)

Thanks,

Answer 1

I doubled your given sample data to show how it works with multiple userIds.

See it working live in an sqlfiddle here.

select
userid, groupnum,
min(ping) as start_date,
max(ping) as end_date,
max(ping) - min(ping) as duration
from (
select
*,
@groupnum := if(@prevUser != userId, @groupnum + 1, @groupnum),
@groupnum := if(ping - @prevTS > 60, @groupnum + 1, @groupnum) as groupnum,
@prevUser := userid,
@prevTS := ping
from
Table1 t
, (select @groupnum:=1, @prevTS:=NULL, @prevUser:=NULL) vars
order by userid, ping
) sq
group by userid, groupnum

Answer 2

Try this on your data:

set @rnum = 1;
select user_id
     , min(ping_timestamp) start_date
     , max(ping_timestamp) end_date
     , max(ping_timestamp)-min(ping_timestamp) duration
  from ( select user_id
              , ping_timestamp
              , @rnum := if(ping_timestamp - @prev_ping_ts > 60, @rnum+1, @rnum) rnum
              , @prev_ping_ts := ping_timestamp
           from test2
          order by user_id, ping_timestamp
       ) t
 group by user_id, rnum
;