MariaDB 10.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table samples (ts datetime, vout22 varchar(1));
insert samples (ts, vout22) values (' 2023-01-04 09:47:57 ', '1');
insert samples (ts, vout22) values (' 2023-01-04 09:47:58 ', '1');
insert samples (ts, vout22) values (' 2023-01-04 09:47:59 ', '1');
insert samples (ts, vout22) values (' 2023-01-04 09:48:00 ', '1');
insert samples (ts, vout22) values (' 2023-01-04 09:48:01 ', '1');
insert samples (ts, vout22) values (' 2023-01-04 09:48:02 ', '1');
insert samples (ts, vout22) values (' 2023-01-04 09:57:21 ', '1');
insert samples (ts, vout22) values (' 2023-01-04 09:57:22 ', '1');
insert samples (ts, vout22) values (' 2023-01-04 09:57:23 ', '1');
insert samples (ts, vout22) values (' 2023-01-04 09:57:24 ', '1');
insert samples (ts, vout22) values (' 2023-01-04 09:57:25 ', '1');
insert samples (ts, vout22) values (' 2023-01-04 09:57:26 ', '0');
insert samples (ts, vout22) values (' 2023-01-04 10:07:10 ', '0');
insert samples (ts, vout22) values (' 2023-01-04 10:07:29 ', '0');
insert samples (ts, vout22) values (' 2023-01-04 10:17:20 ', '0');
insert samples (ts, vout22) values (' 2023-01-04 10:17:34 ', '0');
insert samples (ts, vout22) values (' 2023-01-04 10:17:35 ', '0');
insert samples (ts, vout22) values (' 2023-01-04 10:17:36 ', '0');
insert samples (ts, vout22) values (' 2023-01-04 10:17:37 ', '0');
insert samples (ts, vout22) values (' 2023-01-04 10:17:38 ', '0');
insert samples (ts, vout22) values (' 2023-01-04 10:17:39 ', '0');
insert samples (ts, vout22) values (' 2023-01-04 10:17:40 ', '1');
insert samples (ts, vout22) values (' 2023-01-04 10:17:41 ', '1');
insert samples (ts, vout22) values (' 2023-01-04 10:17:42 ', '1');
insert samples (ts, vout22) values (' 2023-01-04 10:17:43 ', '1');
insert samples (ts, vout22) values (' 2023-01-04 10:17:44 ', '1');
insert samples (ts, vout22) values (' 2023-01-04 10:17:45 ', '1');
insert samples (ts, vout22) values (' 2023-01-04 10:17:46 ', '1');
insert samples (ts, vout22) values (' 2023-01-04 10:17:47 ', '1');
insert samples (ts, vout22) values (' 2023-01-04 10:17:49 ', '1');
insert samples (ts, vout22) values (' 2023-01-04 10:17:50 ', '0');
insert samples (ts, vout22) values (' 2023-01-04 10:17:51 ', '0');
SELECT *
FROM (
    SELECT ts, vout22,
    LAG(ts)        OVER w AS previous_ts,
    LEAD(ts)        OVER w AS next_ts,
    COALESCE(LAG(vout22)        OVER w, '1') AS previous_vout22,
    LEAD(vout22) OVER w AS next_vout22,
    COALESCE(TIMESTAMPDIFF(SECOND, LAG(ts) OVER w, ts), 0) as diff_seconds
    FROM samples
    WINDOW w AS (ORDER BY ts)
  ) r;


ts
vout22
previous_ts
next_ts
previous_vout22
next_vout22
diff_seconds


2023-01-04 09:47:57
1
null
2023-01-04 09:47:58
1
1
0

2023-01-04 09:47:58
1
2023-01-04 09:47:57
2023-01-04 09:47:59
1
1
1

2023-01-04 09:47:59
1
2023-01-04 09:47:58
2023-01-04 09:48:00
1
1
1

2023-01-04 09:48:00
1
2023-01-04 09:47:59
2023-01-04 09:48:01
1
1
1

2023-01-04 09:48:01
1
2023-01-04 09:48:00
2023-01-04 09:48:02
1
1
1

2023-01-04 09:48:02
1
2023-01-04 09:48:01
2023-01-04 09:57:21
1
1
1

2023-01-04 09:57:21
1
2023-01-04 09:48:02
2023-01-04 09:57:22
1
1
559

2023-01-04 09:57:22
1
2023-01-04 09:57:21
2023-01-04 09:57:23
1
1
1

2023-01-04 09:57:23
1
2023-01-04 09:57:22
2023-01-04 09:57:24
1
1
1

2023-01-04 09:57:24
1
2023-01-04 09:57:23
2023-01-04 09:57:25
1
1
1

2023-01-04 09:57:25
1
2023-01-04 09:57:24
2023-01-04 09:57:26
1
0
1

2023-01-04 09:57:26
0
2023-01-04 09:57:25
2023-01-04 10:07:10
1
0
1

2023-01-04 10:07:10
0
2023-01-04 09:57:26
2023-01-04 10:07:29
0
0
584

2023-01-04 10:07:29
0
2023-01-04 10:07:10
2023-01-04 10:17:20
0
0
19

2023-01-04 10:17:20
0
2023-01-04 10:07:29
2023-01-04 10:17:34
0
0
591

2023-01-04 10:17:34
0
2023-01-04 10:17:20
2023-01-04 10:17:35
0
0
14

2023-01-04 10:17:35
0
2023-01-04 10:17:34
2023-01-04 10:17:36
0
0
1

2023-01-04 10:17:36
0
2023-01-04 10:17:35
2023-01-04 10:17:37
0
0
1

2023-01-04 10:17:37
0
2023-01-04 10:17:36
2023-01-04 10:17:38
0
0
1

2023-01-04 10:17:38
0
2023-01-04 10:17:37
2023-01-04 10:17:39
0
0
1

2023-01-04 10:17:39
0
2023-01-04 10:17:38
2023-01-04 10:17:40
0
1
1

2023-01-04 10:17:40
1
2023-01-04 10:17:39
2023-01-04 10:17:41
0
1
1

2023-01-04 10:17:41
1
2023-01-04 10:17:40
2023-01-04 10:17:42
1
1
1

2023-01-04 10:17:42
1
2023-01-04 10:17:41
2023-01-04 10:17:43
1
1
1

2023-01-04 10:17:43
1
2023-01-04 10:17:42
2023-01-04 10:17:44
1
1
1

2023-01-04 10:17:44
1
2023-01-04 10:17:43
2023-01-04 10:17:45
1
1
1

2023-01-04 10:17:45
1
2023-01-04 10:17:44
2023-01-04 10:17:46
1
1
1

2023-01-04 10:17:46
1
2023-01-04 10:17:45
2023-01-04 10:17:47
1
1
1

2023-01-04 10:17:47
1
2023-01-04 10:17:46
2023-01-04 10:17:49
1
1
1

2023-01-04 10:17:49
1
2023-01-04 10:17:47
2023-01-04 10:17:50
1
0
2

2023-01-04 10:17:50
0
2023-01-04 10:17:49
2023-01-04 10:17:51
1
0
1

2023-01-04 10:17:51
0
2023-01-04 10:17:50
null
0
null
1


SELECT min(ts) AS min_ts,
MAX(previous_ts) AS max_ts,
vout22, SUM(diff_seconds) AS total_seconds

FROM (
    SELECT ts, vout22,
    LAG(ts)        OVER w AS previous_ts,
    LEAD(ts)        OVER w AS next_ts,
    COALESCE(LAG(vout22)        OVER w, '1') AS previous_vout22,
    LEAD(vout22) OVER w AS next_vout22,
    COALESCE(TIMESTAMPDIFF(SECOND, LAG(ts) OVER w, ts), 0) as diff_seconds
    FROM samples
    WINDOW w AS (ORDER BY ts)
) r
GROUP BY next_vout22
ORDER BY min_ts, max_ts
;


min_ts
max_ts
vout22
total_seconds


2023-01-04 09:47:57
2023-01-04 10:17:46
1
576

2023-01-04 09:57:25
2023-01-04 10:17:49
1
1217

2023-01-04 10:17:51
2023-01-04 10:17:50
0
1

ts	vout22	previous_ts	next_ts	previous_vout22	next_vout22	diff_seconds
2023-01-04 09:47:57	1	null	2023-01-04 09:47:58	1	1	0
2023-01-04 09:47:58	1	2023-01-04 09:47:57	2023-01-04 09:47:59	1	1	1
2023-01-04 09:47:59	1	2023-01-04 09:47:58	2023-01-04 09:48:00	1	1	1
2023-01-04 09:48:00	1	2023-01-04 09:47:59	2023-01-04 09:48:01	1	1	1
2023-01-04 09:48:01	1	2023-01-04 09:48:00	2023-01-04 09:48:02	1	1	1
2023-01-04 09:48:02	1	2023-01-04 09:48:01	2023-01-04 09:57:21	1	1	1
2023-01-04 09:57:21	1	2023-01-04 09:48:02	2023-01-04 09:57:22	1	1	559
2023-01-04 09:57:22	1	2023-01-04 09:57:21	2023-01-04 09:57:23	1	1	1
2023-01-04 09:57:23	1	2023-01-04 09:57:22	2023-01-04 09:57:24	1	1	1
2023-01-04 09:57:24	1	2023-01-04 09:57:23	2023-01-04 09:57:25	1	1	1
2023-01-04 09:57:25	1	2023-01-04 09:57:24	2023-01-04 09:57:26	1	0	1
2023-01-04 09:57:26	0	2023-01-04 09:57:25	2023-01-04 10:07:10	1	0	1
2023-01-04 10:07:10	0	2023-01-04 09:57:26	2023-01-04 10:07:29	0	0	584
2023-01-04 10:07:29	0	2023-01-04 10:07:10	2023-01-04 10:17:20	0	0	19
2023-01-04 10:17:20	0	2023-01-04 10:07:29	2023-01-04 10:17:34	0	0	591
2023-01-04 10:17:34	0	2023-01-04 10:17:20	2023-01-04 10:17:35	0	0	14
2023-01-04 10:17:35	0	2023-01-04 10:17:34	2023-01-04 10:17:36	0	0	1
2023-01-04 10:17:36	0	2023-01-04 10:17:35	2023-01-04 10:17:37	0	0	1
2023-01-04 10:17:37	0	2023-01-04 10:17:36	2023-01-04 10:17:38	0	0	1
2023-01-04 10:17:38	0	2023-01-04 10:17:37	2023-01-04 10:17:39	0	0	1
2023-01-04 10:17:39	0	2023-01-04 10:17:38	2023-01-04 10:17:40	0	1	1
2023-01-04 10:17:40	1	2023-01-04 10:17:39	2023-01-04 10:17:41	0	1	1
2023-01-04 10:17:41	1	2023-01-04 10:17:40	2023-01-04 10:17:42	1	1	1
2023-01-04 10:17:42	1	2023-01-04 10:17:41	2023-01-04 10:17:43	1	1	1
2023-01-04 10:17:43	1	2023-01-04 10:17:42	2023-01-04 10:17:44	1	1	1
2023-01-04 10:17:44	1	2023-01-04 10:17:43	2023-01-04 10:17:45	1	1	1
2023-01-04 10:17:45	1	2023-01-04 10:17:44	2023-01-04 10:17:46	1	1	1
2023-01-04 10:17:46	1	2023-01-04 10:17:45	2023-01-04 10:17:47	1	1	1
2023-01-04 10:17:47	1	2023-01-04 10:17:46	2023-01-04 10:17:49	1	1	1
2023-01-04 10:17:49	1	2023-01-04 10:17:47	2023-01-04 10:17:50	1	0	2
2023-01-04 10:17:50	0	2023-01-04 10:17:49	2023-01-04 10:17:51	1	0	1
2023-01-04 10:17:51	0	2023-01-04 10:17:50	null	0	null	1