CODEWITHSUNDEEP
mysqlsqltimestampgroup-by
Ulile
Ulile

Reputation: 261

MySQL grouping results by time periods

I have a table that contains timestamps of session events. I want to query how many sessions I have according to the timestamps when 2 sessions are separated by at least 10 minutes difference of events. Can I count the sessions with an MySql query ?

Thanks

Upvotes: 2

Views: 705

Answers (1)

Kickstart
Kickstart

Reputation: 21513

With little info on your tables this is no more than a basic idea for you, but you could do something like this:-

SELECT COUNT(*)
FROM
(
    SELECT a.TimeStamp AS ThisTimeStamp, MIN(b.TimeStamp) AS NextTimeStamp
    FROM SomeTable a
    INNER JOIN SomeTable b
    ON a.TimeStamp < b.TimeStamp
    GROUP BY a.TimeStamp
) Sub1
WHERE Sub1.ThisTimeStamp < (Sub1.NextTimeStamp - 600)

Get all the timestamp and join them against all other timestamps that are greater, and use MIN to narrow that down to the next greatest timestamp. Then from that select the count where the difference is less than 600 seconds (assuming unix timestamps).

EDIT - If you want the tied down for the number of 10 minute+ gaps in events for users then:-

SELECT COUNT(*)
FROM
(
    SELECT a.user_id, a.TimeStamp AS ThisTimeStamp, MIN(b.TimeStamp) AS NextTimeStamp
    FROM SomeTable a
    INNER JOIN SomeTable b
    ON a.TimeStamp < b.TimeStamp
    AND a.user_id = b.user_id
    GROUP BY a.user_id, a.TimeStamp
) Sub1
WHERE Sub1.ThisTimeStamp < (Sub1.NextTimeStamp - 600)

Upvotes: 3

Related Questions