Postgres 16

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

CREATE TABLE

INSERT 0 10

INSERT 0 10

WITH cte AS -- the common table expression (CTE)
(
   -- table projection with all leading and lagging columns to then inform begin and end of failed states 
    SELECT 
            LAG(id) OVER (PARTITION BY id ORDER BY timestamp) previous_id, 
            LAG(status) OVER (PARTITION BY id ORDER BY timestamp) previous_status, 
            LAG(timestamp) OVER (PARTITION BY id ORDER BY timestamp) previous_timestamp,
            id, 
            status,
            timestamp,
            LEAD(id) OVER (PARTITION BY id ORDER BY timestamp) next_id, 
            LEAD(status) OVER (PARTITION BY id ORDER BY timestamp) next_status, 
            LEAD(timestamp) OVER (PARTITION BY id ORDER BY timestamp) next_timestamp
  
    FROM device_status
),
-- AND WITH (comment for better readability of multiple queries being introduced on the CTE 
-- fail start table being defined as FS, 
fs AS 
(
  -- the order of fail start and, later similarly, failed are preserved in row_number
  SELECT ROW_NUMBER() OVER (ORDER BY id, fail_start) r, x.* FROM 
  ( 
    -- if next _status changed to failed for same id that is the start timestamp.  
    SELECT id, next_timestamp AS fail_start FROM cte
    WHERE next_status = 'Failed' AND next_status IS DISTINCT FROM status AND next_id = id
     UNION
    -- making an exception for when it is the only fail record
    SELECT id, timestamp AS fail_start FROM cte
    WHERE previous_status IS NULL AND status = 'Failed'
   ) x
),
-- AND WITH
-- fail end table FE, mirror image of FS
fe AS 
(
  SELECT ROW_NUMBER() OVER (ORDER BY id, fail_end) r, y.* FROM 
  (   
    SELECT id, previous_timestamp AS fail_end FROM cte
    WHERE previous_status = 'Failed' AND previous_status IS DISTINCT FROM status AND next_id = id

UNION

SELECT id, timestamp AS fail_end FROM cte
    WHERE next_status IS NULL AND status = 'Failed'
  ) y
)
-- simple join of row numbers of FS and FE to generate the required result
SELECT fs.id, fs.fail_start, fe.fail_end FROM fs JOIN fe ON fs.r = fe.r

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

SELECT 4