Gerfried Gerfried - 1 month ago 11
MySQL Question

mysql: find start-end time in an event log - grouping by session time

I have a log of events - for example a network access log.

Id Date IP
---------------------------------
100; 2017-09-21 08:00:00; 192.168.1.10
101; 2017-09-21 08:10:00; 192.168.1.100
102; 2017-09-21 08:15:00; 192.168.1.100
103; 2017-09-21 08:19:00; 192.168.1.10
104; 2017-09-21 08:30:00; 192.168.1.100
105; 2017-09-21 08:45:00; 192.168.1.10
106; 2017-09-21 09:10:00; 192.168.1.100
107; 2017-09-21 09:11:00; 192.168.1.100
108; 2017-09-21 09:30:00; 192.168.1.10
109; 2017-09-21 09:40:00; 192.168.1.10


I would like to get a list of start and end times within a certain session timeout. So let's say the session timeout is 20 minutes. All events from the same IP within 20 minutes should be grouped in one result set with min(data), max(date).

DateFrom DateTo IP
-----------------------------------------------------------------
2017-09-21 08:00:00; 2017-09-21 08:39:00; 192.168.1.10
2017-09-21 08:45:00; 2017-09-21 09:05:00; 192.168.1.10
2017-09-21 09:30:00; 2017-09-21 10:00:00; 192.168.1.10
2017-09-21 08:10:00; 2017-09-21 08:50:00; 192.168.1.100
2017-09-21 09:10:00; 2017-09-21 09:31:00; 192.168.1.100


Note the second column: 08:39 is not in the log - but it is 08:19 + 20 minutes timeout, because there was no succeeding event within the session timeout.

I have found SO answers which helped me a bit, but I always failed with joining max and min date column. MySQL GROUP BY DateTime +/- 3 seconds

Answer Source

A try with using variable, not sure it's suitable to all the occasion in your data or not, but you can check this:

select min(`date`) datefrom, max(`date`) + interval 20 minute dateto ,ip
from (
  select
      log.*,
      @newgrp := case when (@grp = ip and (`date` - interval 20 minute) < @date) then @newgrp else @newgrp + 1 end newgrp,
      @grp := ip, @date := `date`
  from log
  cross join (
      select @grp := null, @date := null, @newgrp := 0
  ) t
  order by ip, `date`
) t
group by newgrp, ip

See SQLFiddle Demo here.