MariaDB 10.3

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

CREATE TABLE mytable(
   id               INTEGER  NOT NULL PRIMARY KEY 
  ,component_number INTEGER  NOT NULL
  ,timestamp        VARCHAR(19) NOT NULL
  ,status           VARCHAR(1) NOT NULL
);
INSERT INTO mytable(id,component_number,timestamp,status) VALUES (1,1,'2020-05-10 16:30:00','A');
INSERT INTO mytable(id,component_number,timestamp,status) VALUES (2,1,'2020-05-18 16:34:05','A');
INSERT INTO mytable(id,component_number,timestamp,status) VALUES (3,1,'2020-05-19 16:36:01','I');
INSERT INTO mytable(id,component_number,timestamp,status) VALUES (4,1,'2020-05-19 16:36:52','A');
INSERT INTO mytable(id,component_number,timestamp,status) VALUES (5,1,'2020-05-19 16:38:57','I');
INSERT INTO mytable(id,component_number,timestamp,status) VALUES (6,2,'2020-05-11 17:04:50','A');
INSERT INTO mytable(id,component_number,timestamp,status) VALUES (7,2,'2020-05-15 10:00:00','A');
INSERT INTO mytable(id,component_number,timestamp,status) VALUES (8,2,'2020-05-16 11:25:16','I');
select 
    component_number, 
    min(timestamp) start_timestamp,
    max(timestamp) end_timestamp
from (
    select 
        t.*, 
        sum(case when status = 'A' and lag_status = 'I' then 1 else 0 end) 
            over(partition by component_number order by timestamp) grp
    from (
        select 
            t.*,
            lag(status) 
                over(partition by component_number order by timestamp) lag_status
        from mytable t
    ) t
) t
group by component_number, grp


component_number
start_timestamp
end_timestamp


1
2020-05-10 16:30:00
2020-05-19 16:36:01

1
2020-05-19 16:36:52
2020-05-19 16:38:57

2
2020-05-11 17:04:50
2020-05-16 11:25:16


select id, component_number, timestamp, status
from (
    select 
        t.*,
        lag(status) 
            over(partition by component_number order by timestamp) lag_status
    from mytable t
) t
where status = 'I' or lag_status is null or lag_status = 'I'


id
component_number
timestamp
status


1
1
2020-05-10 16:30:00
A

3
1
2020-05-19 16:36:01
I

4
1
2020-05-19 16:36:52
A

5
1
2020-05-19 16:38:57
I

6
2
2020-05-11 17:04:50
A

8
2
2020-05-16 11:25:16
I

component_number	start_timestamp	end_timestamp
1	2020-05-10 16:30:00	2020-05-19 16:36:01
1	2020-05-19 16:36:52	2020-05-19 16:38:57
2	2020-05-11 17:04:50	2020-05-16 11:25:16