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.
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 | |