Postgres 13

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

CREATE TABLE

INSERT 0 20

WITH cte AS
(
         SELECT   id,
                  message,
                  datetimestamp,
                  Extract( second FROM datetimestamp )::bigint - rank() over(partition BY message ORDER BY datetimestamp) AS gap
         FROM     (
                         SELECT id,
                                message,
                                datetimestamp
                         FROM   (
                                         SELECT   id,
                                                  message,
                                                  datetimestamp ,
                                                  lag(message) OVER (ORDER BY datetimestamp) ,
                                                  lead(message) OVER (ORDER BY datetimestamp) ,
                                                  lag(message,1) OVER (ORDER BY datetimestamp) IN ('Normal',
                                                                                                   'Warning') AS step1 ,
                                                  lead(message,1) OVER (ORDER BY datetimestamp) IN ('Warning',
                                                                                                    'Error') AS step2 ,
                                                  lag(message,2) OVER (ORDER BY datetimestamp) IN ('Normal',
                                                                                                   'Warning') AS step3 ,
                                                  lead(message,2) OVER (ORDER BY datetimestamp) IN ('Warning',
                                                                                                    'Error') AS step4
                                         FROM     test_data
                                         ORDER BY datetimestamp) sub1
                         WHERE  step1
                         AND    step2
                         AND    step3
                         AND    step4
                         AND    message = 'Warning' ) sub )
SELECT   gap,
         min(datetimestamp),
         max(datetimestamp)
FROM     cte
GROUP BY gap

gap	min	max
11	2022-07-13 08:59:12	2022-07-13 08:59:15
19	2022-07-13 08:59:24	2022-07-13 08:59:26

SELECT 2