how to find time gaps and gap length between mysql table records?

Question

My table looks like this:

id	time
1	2021-07-17 17:44:26
2	2021-07-17 17:44:26
3	2021-07-17 17:44:26
4	2021-07-17 17:44:31
5	2021-07-17 17:44:31
6	2021-07-17 17:44:31
7	2021-07-17 17:44:36
8	2021-07-17 17:44:36
9	2021-07-17 17:44:36
10	2021-07-17 17:44:41
11	2021-07-17 17:44:41
12	2021-07-17 17:44:41
13	2021-07-17 17:44:51
14	2021-07-17 17:44:51
15	2021-07-17 17:44:51
16	2021-07-17 17:44:56
17	2021-07-17 17:44:56
18	2021-07-17 17:44:56
19	2021-07-17 17:45:02
20	2021-07-17 17:45:02
21	2021-07-17 17:45:02

I have MySQL 8.0.21

always next 3 rows have same time and then beetwen usualy is 5 seconds time gap, how to find all gap longer than 8 second and also count gap time to get something like that:

gap_id	gap_time_start	gap_length
1	2021-07-17 17:44:41	10

Answer 1

If you're using MySQL 8+ you can use the LEAD() window function like this:

select * from (
    SELECT id as gap_id, 
        `time` as gap_start_time, 
        timediff( lead(`time`) over W, `time`) as gap_length 
        from `myTable` window w as (order by `time` asc)
    ) T where T.gap_length > 5;

Output:

12, 2021-07-17 17:44:41, 00:00:10
18, 2021-07-17 17:44:56, 00:00:06

Reference: LEAD()