MySQL lag/lead function?

Question

I want to calculate the difference between a users start time and stop time from previous session..

Table contains: user numeric, start/stop unix timestamp. It is sorted after both user AND start

user  start   stop
1    150000 150010
1    160011 160050
1    160100 160200
2    150011 150023
2    155001 155055
2    160001 160500
3    159001 159550

Im expecting the result to look like:

user start   stop  diff
1   150000 150010  160011-150010=10001
1   160011 160050  160100-160050
1   160100 160200  0 (new user on next row)
2   150011 150023  155001-150023
2   155001 155055  160001-155055
2   160001 160500  0 (new user on next row)
3   159001 159550  and so on..

Is it possible to do this, if so, how?

Answer 1

This is better done on application level, but just for fun, here it is on database level:

select `user`, `start`, `stop`, diff from (
    select
    t.*
    , if(@prev_user = `user`, (`stop` - @prev) * -1, 0) as diff
    , @prev := `start`
    , @prev_user := `user`
    from
    t
    , (select @prev := null, @prev_user := null) var_init
    order by `user`, `start` desc
) sq
order by `user`, `start`

• see it working live in an sqlfiddle

Note, that there are no lag/lead functions in MySQL. All you can do, is to use variables. The SELECT clause gets processed one line at a time. So you can assign the value of the current row in the last lines of the SELECT clause and therefore use this variable as the value of "the previous row" in the first lines of the SELECT clause.
Also note, that the ORDER BY is very important. A table is not sorted. Data in a relational DBMS is not sorted unless you specify an order with the ORDER BY clause.

• read more about using variables in queries here

EDIT:

Change it to

UPDATE inactivitytmp
JOIN (
SELECT
inactivitytmp.*
, if(@prev_user_id = `user_id`, (`end_ts` - @prev) * -1, 0) as diff2
, @prev := `start_ts`
, @prev_user_id := `user_id`
FROM
inactivitytmp
, (SELECT @prev := null, @prev_user_id := null) var_init
ORDER BY `user_id`, `start_ts` DESC
) query_alias
ON inactivitytmp.user_id=query_alias.user_id AND inactivitytmp.start_ts=q uery_alias.start_ts AND inactivitytmp.end_ts=query_alias.end_ts
SET inactivitytmp.diff=query_alias.diff2;