Query user session length on a timestamped activity log I'm trying to retrofit some analytics on a customer's MySQL 5.5 table.

Given a simple but large table:  | user_id | timestamp |

A session_length is defined as the longest number of minutes without an hour of idle time.

When I run some SQL query, I get user_id, session_length for each span of time a user is using the system without an hour of idleness.

Example table:

1 | 01:10
1 | 01:11
1 | 01:12
1 | 01:13
1 | 01:14
1 | 01:15
              ...
1 | 03:59
              ...
1 | 12:10
1 | 12:20
1 | 12:30
1 | 12:40
1 | 12:50


Should result in:

user_id | session_length
1       | 5
1       | 1
1       | 40


I'm not worried about time delta granularity.

I found a hint that this is dealt with in other db's with LAG functions. No such luck in MySQL.
Plan A:


Create a table with an AUTO_INCREMENT PK, plus the 2 columns in question.  (It can't be a TEMPORARY table due to an unfortunate limitation.)
Do a "self-join" to pair up adjacent rows.  Put this (id, user, and pair of times) in another table.
Filter -- keep pairs that are more than 1 hour.  (Need to also keep first and last times; this may involve UNION.)
Subtract adjacent id pairs to get session_length.  (This probably involves another table and another AUTO_INCREMENT, but could be done with an @variable.)  (Note:  Be aware of auto_increment_increment; if it is not 1, the subtract will be 'wrong'.)


So, Yes, it can be done in SQL; but do you want to go to this much work?

Plan B:  Somehow use @variables to count the session_length, watch out for change in userid, test for 1 hour, etc.

Plan C:  Migrate to MariaDB 10.2 and use its Windowing Functions.