CODEWITHSUNDEEP
mysqltimegroup-bybetween
Erik Andershed
Erik Andershed

Reputation: 383

Group rows where time difference is not bigger than 15 min

I'd like to group rows that are not bigger than 15 min in between. I like to see when a user have been logged 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 groups all rows that are in between 15 min.

Upvotes: 2

Views: 289

Answers (1)

Strawberry
Strawberry

Reputation: 33945

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

Upvotes: 2

Related Questions