user2673664 user2673664 - 21 days ago 7
MySQL Question

Mysql - Group rows where time different is not bigger than 15 min

I like to group rows that are not bigger than 15 min in between. I like to see when a user have been loged in and have a "session".

Table
Time
2016-11-15 11:37:34
2016-11-15 11:35:04
2016-11-15 11:21:11
2016-11-15 09:37:22
2016-11-15 09:27:01
2016-11-14 21:37:59
2016-11-14 21:33:35
2016-11-14 21:31:14


Result:
Start End
2016-11-15 11:21:11 2016-11-15 11:37:34
2016-11-15 09:27:01 2016-11-15 09:37:22
2016-11-14 21:31:14 2016-11-14 21:37:59


This is my current query:

SELECT
ROUND(UNIX_TIMESTAMP(Time)/900) as Session
FROM workouts
GROUP BY Session
ORDER BY Session DESC


But it only group all row that are in between 15 min.

Answer

You're not after a GROUP BY at all...

SELECT x.* 
  FROM my_table x 
  LEFT 
  JOIN my_table y ON y.dt > x.dt 
   AND y.dt <= x.dt + INTERVAL 15 MINUTE 
 WHERE y.dt IS NULL;

Edit:

To answer the amended question, you might consider something along these lines. It looks considerably more complicated, but in actual fact, it probably executes quicker...

SELECT MIN(dt) start
     , MAX(dt) end 
  FROM 
     ( SELECT dt
            , CASE WHEN @prev > dt - INTERVAL 15 MINUTE THEN @i:=@i ELSE @i:=@i+1 END rank
            , @prev:=dt 
         FROM my_table
            , ( SELECT @i:=0,@prev:=null ) vars 
        ORDER 
            BY dt
     ) a 
 GROUP 
    BY rank;

To see how it works, it may help to run the subquery (a) on its own

Comments