CODEWITHSUNDEEP
mysqlgroup-by
ESP32
ESP32

Reputation: 8728

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

Upvotes: 2

Views: 414

Answers (1)

Blank
Blank

Reputation: 12378

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.

Upvotes: 2

Related Questions