MYSQL difference between two datetime

Question

I have the following table.

table1:

id_user | action |  time 
--------+--------+----------------------
1       | 2      | '2009-05-18 11:45:42'
1       | 2      | '2009-05-18 11:45:45'
1       | 3      | '2009-05-18 11:45:50'
1       | 2      | '2009-05-18 11:46:50'

And I want to achieve result where the column timediff contains timediff with the previous row in seconds.

table2

id_user | action | timediff
--------+--------+----------
1       | 2      | 3
1       | 2      | 5
1       | 3      | 60

I tried this query, but It did not work:

SELECT
    id_user,action, 
    TIMESTAMPDIFF(SECOND,LEAD(time),time) OVER (PARTITION BY id_user, ORDER BY time) AS timediff
FROM table1

... but it throws an error. :/

I read other answers, but I didn't see one that use LEAD or LAG with TIMESTAMPDIFF simultaneously.

Thanks in advance.

Answer 1

Your syntax is wrong, you must use this:

SELECT id_user,
       action, 
       TIMESTAMPDIFF(
         SECOND, 
         time, 
         LEAD(time) OVER (PARTITION BY id_user ORDER BY time)
       )  AS timediff 
FROM table1

The TIMESTAMPDIFF() function's 2nd and 3d arguments are datetime expressions and the 2nd is subtracted from the 3d.

See the demo.
Results:

| id_user | action | timediff |
| ------- | ------ | -------- |
| 1       | 2      | 3        |
| 1       | 2      | 5        |
| 1       | 3      | 60       |
| 1       | 2      |          |