2

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

ESP32
  • 8,089
  • 2
  • 40
  • 61

1 Answers1

2

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.

Blank
  • 12,308
  • 1
  • 14
  • 32
  • Many thanks - to me this looks like magic - I will test carefully and get back soon. Thank you. – ESP32 Sep 22 '17 at 22:39
  • I have tested it here http://sqlfiddle.com/#!9/b0655b/3 and it works brilliant. If I need to add a WHERE condition to the log table, where would I put it? I guess it's to be before the ORDER BY, right? – ESP32 Sep 23 '17 at 16:06