Mysql min and max of timed series data

Question

-- create
CREATE TABLE readings (
  id INTEGER PRIMARY KEY,
  date timestamp NOT NULL,
  value int NOT NULL
);

-- insert
INSERT INTO readings VALUES (0001, '2023-02-22 10:00:00', '12');
INSERT INTO readings VALUES (0002, '2023-02-22 10:10:00', '13');
INSERT INTO readings VALUES (0003, '2023-02-22 10:20:00', '15');
INSERT INTO readings VALUES (0004, '2023-02-22 10:30:00', '11');
INSERT INTO readings VALUES (0005, '2023-02-22 10:40:00', '10');
INSERT INTO readings VALUES (0006, '2023-02-22 10:50:00', '11');
INSERT INTO readings VALUES (0007, '2023-02-22 11:00:00', '12');
INSERT INTO readings VALUES (0008, '2023-02-22 11:10:00', '14');
INSERT INTO readings VALUES (0009, '2023-02-22 11:20:00', '13');
INSERT INTO readings VALUES (00010, '2023-02-22 11:30:00', '8');
-- fetch 
SELECT min(date), max(date) FROM readings WHERE VALUE >= 12 group by date

I want an answer of the lowest (min) and latest (max) time for each batch of dates whereby the value is over 12.

min                       max
-- 2023-02-22 10:00:00 -- 2023-02-22 10:20:00
-- 2023-02-22 11:00:00 -- 2023-02-22 11:20:00

I'm not convinced this can be done with SQL alone! Can anyone prove me wrong? The real dates are not quite so cleanly 10 mins apart.

https://onecompiler.com/mysql/3yyvzwwbj

Answer 1

Use SUM() window function to create the groups/batches that you want and then aggregate:

WITH cte AS (SELECT *, SUM(value < 12) OVER (ORDER BY date) AS grp FROM readings)
SELECT MIN(date) min_date,
       MAX(date) max_date
FROM cte
WHERE value >= 12
GROUP BY grp;

See the demo.