Postgres 16

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

CREATE TYPE status AS ENUM ('Active', 'Failed');
DROP TABLE IF EXISTS Device_Status;
CREATE TABLE Device_Status
(
  ID integer,
  Status status,
  temptime text
);
INSERT INTO Device_Status VALUES (1,'Active','2023-01-13 10.00.01.528');
INSERT INTO Device_Status VALUES (2,'Active','2023-01-13 10.00.01.529');
INSERT INTO Device_Status VALUES (1,'Failed','2023-01-13 10.00.01.530');
INSERT INTO Device_Status VALUES (3,'Active','2023-01-13 10.00.01.531');
INSERT INTO Device_Status VALUES (1,'Failed','2023-01-13 10.00.01.532');
INSERT INTO Device_Status VALUES (1,'Active','2023-01-13 10.00.01.533');
INSERT INTO Device_Status VALUES (3,'Active','2023-01-13 10.00.01.534');
INSERT INTO Device_Status VALUES (1,'Failed','2023-01-13 10.00.01.535');
INSERT INTO Device_Status VALUES (4,'Failed','2023-01-13 10.00.01.536');
INSERT INTO Device_Status VALUES (1,'Active','2023-01-13 10.00.01.537');
insert into device_status values (1,'Failed','2023-01-13 10:00:01.538'), 
                                 (1,'Failed','2023-01-13 10:00:01.539'), 
                                 (1,'Failed','2023-01-13 10:00:01.540'), 
                                 (1,'Failed','2023-01-13 10:00:01.541'), 
                                 (1,'Failed','2023-01-13 10:00:01.542'), 
                                 (1,'Failed','2023-01-13 10:00:01.543'), 
                                 (1,'Failed','2023-01-13 10:00:01.544'),
                                 (1,'Failed','2023-01-13 10:00:01.545'), 
                                 (1,'Failed','2023-01-13 10:00:01.546'),
                                 (1,'Failed','2023-01-13 10:00:01.547');
ALTER TABLE Device_Status ADD timestamp TIMESTAMP;
UPDATE Device_Status set timestamp = 
      to_timestamp(temptime,'YYYY-MM-DD HH:MI:SS.MS');
ALTER TABLE Device_Status DROP COLUMN temptime;
WITH Device_Status_With_Change_Column AS
(
  WITH flag AS
  (
    WITH lag AS
    (
      SELECT *, 
             LAG(status) OVER (PARTITION BY Id ORDER BY TimeStamp) Last_Status,
             LAG(Id,1,0) OVER (PARTITION BY Id ORDER BY TimeStamp) Last_Id
      FROM Device_Status
    )
    SELECT *, 
           CASE
           WHEN lag.last_status IS NULL THEN 0
           WHEN lag.last_status IS DISTINCT FROM lag.status 
            AND lag.last_id = lag.id  
            THEN 1
           ELSE 0
         END Change_Flag
     FROM lag
  )
  SELECT *, 
         SUM(change_flag) OVER (PARTITION BY Id ORDER BY TimeStamp) Change
  FROM flag
)
SELECT Id, min(Timestamp) AS Fail_Begin, max(Timestamp) AS Fail_End
FROM Device_Status_With_Change_Column
WHERE status = 'Failed'
GROUP BY Id, Change
ORDER BY Id

CREATE TYPE

DROP TABLE

CREATE TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 10

ALTER TABLE

UPDATE 20

ALTER TABLE

id	fail_begin	fail_end
1	2023-01-13 10:00:01.53	2023-01-13 10:00:01.532
1	2023-01-13 10:00:01.535	2023-01-13 10:00:01.535
1	2023-01-13 10:00:01.538	2023-01-13 10:00:01.547
4	2023-01-13 10:00:01.536	2023-01-13 10:00:01.536

SELECT 4