CODEWITHSUNDEEP
mysql
Developer Jano
Developer Jano

Reputation: 91

SQL Calculate time between one row and the next one

I have a table clicks

click_id int
created datetime

Example data

1 2021-03-01 12:15:17
2 2021-03-01 12:15:21
3 2021-03-01 12:17:21
4 2021-03-01 13:17:59

I want to select all clicks including the time difference (in seconds) between it and the soonest click afterwards

Result needed:

1 - 2021-03-01 12:15:17 - 4
2 - 2021-03-01 12:15:21 - 120
3 - 2021-03-01 12:17:21 - 3602
4 - 2021-03-01 13:17:23 - null

Can this be achieved using MySQL?

Upvotes: 0

Views: 34

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

On MySQL 8+, we can use LEAD with TIMESTAMPDIFF:

SELECT
    click_id,
    created,
    TIMESTAMPDIFF(SECOND, created, LEAD(created) OVER (ORDER BY created)) AS diff_sec
FROM yourTable
ORDER BY created;

Upvotes: 2

Related Questions