Grouping timestamps by interval between timestamps, then calculating from group MySQL
- by Emile
To put this question into context, I'm trying to calculate "time in app" based on an event log.
Assume the following table:
user_id event_time
2 2012-05-09 07:03:38
3 2012-05-09 07:03:42
4 2012-05-09 07:03:43
2 2012-05-09 07:03:44
2 2012-05-09 07:03:45
4 2012-05-09 07:03:52
2 2012-05-09 07:06:30
I'd like to get the difference between the highest and lowest event_time from a set of timestamps that are within 2 minutes of eachother (and grouped by user). If a timestamp is outside of a 2 minute interval from the set, it should be considered a part of another set.
Desired output:
user_id seconds_interval
2 7 (because 07:03:45 - 07:03:38 is 7 seconds)
3 0 (because 07:03:42)
4 9 (because 07:03:52 - 2012-05-09 07:03:43)
2 0 (because 07:06:30 is outside 2 min interval of 1st user_id=2 set)
This is what I've tried, although I can't group on seconds_interval (even if I could, I'm not sure this is the right direction):
SELECT (max(tr.event_time)-min(tr.event_time)) as seconds_interval
FROM some_table tr
INNER JOIN TrackingRaw tr2 ON (tr.event_time BETWEEN
tr2.event_time - INTERVAL 2 MINUTE AND tr2.event_time + INTERVAL 2 MINUTE)
GROUP BY seconds_interval
