Looking for a pattern of temperatures

Trying to produce a list of results from a simple table that records temperature and time every 5 minutes. The table only has two columns 'temp' and 'ttime'. The time is recorded as MySQL timestamp.

What I need to do is check for any patterns where the temperature goes over 40 for more than two hours within a 24 hour period using just the data from same table and there are thousands of rows of data.

Quick sample of data:

Not sure how to even start something like this.

CodePudding user response：

I'd try something like

SELECT * FROM data d1
 WHERE temp >= 40 
   AND temp >= ALL (
       SELECT temp FROM d2 
        WHERE d2.ttime BETWEEN 
                 date_add(d1.ttime, INTERVAL -2 HOUR)
                 AND d1.ttime
   );

to get the first record after two hours higher than 40 degrees (can't verify at the moment)