mySQL query to return minimum state duration for a given period

I have the following data in a MySQL database table, and I'd like to calculate the minimum duration where state = 1 for a given period, to place into a Grafana stat.

Within this table snippet, the desired result would be the timestampdiff() between row 10 and 8, i.e. 2 minutes.

I've tried numerous faulty solutions from chatGPT that I won't post here for obvious reasons.

The period part is simple enough using the Grafana global variable WHERE $__timeFilter(datetime) and using that I can calculate the total duration WHERE state = 1 AND $__timeFilter(datetime) because the record generation is on a 1-minute basis which makes summing the binary 0/1 state column an easy out (obviously doesn't require the state = 1).

I'm completely lost trying to work with duration periods within a period.

The query requirements that you had was bit complex, I had to use windows function - LAG to get the results. Below is the query that you need.

WITH temp_prev_state AS (
SELECT id, loc, temp, state, datetime_, LAG(state,1)  OVER(  ORDER BY datetime_  ) AS prev_state FROM Temperature
), latest_on_off_stateSession AS (
(SELECT * from temp_prev_state where state = '0' and prev_state = '1' ORDER BY datetime_ DESC LIMIT 1 ) 
UNION
(SELECT * from temp_prev_state where state = '1' and prev_state = '0' ORDER BY datetime_ DESC LIMIT 1 )
), lat_on_off_prev_dt AS (
SELECT *, LAG(datetime_,1)  OVER(  ORDER BY datetime_  ) AS prev_datetime_ FROM latest_on_off_stateSession
)
select id, loc, temp, state, datetime_, ABS(TIMESTAMPDIFF(MINUTE,datetime_ ,prev_datetime_)) AS abs_Time_Diff_Mins FROM lat_on_off_prev_dt

The output of the query:

Check the demo here