Add values and join rows on condition in a Mysql query

Question

I have a table with time-in/time-out data in a structure as shown below.

id	date	timein	timeout	duration
3	2024-01-02	01:00	02:00	01:00
3	2024-01-02	02:30	07:00	04:30
3	2024-01-02	09:00	09:10	00:10
3	2024-01-02	09:10	09:30	00:20
3	2024-01-02	12:00	12:25	00:25

Time-in positions with a duration less than 30 minutes should not be considered for calculation. however, if two consecutive time-in for a day is more than or equal to 30 minutes then it should be considered. like in the above example, row 3 time-out is same as row 4 time-in 09:00-09:10/09:1--09:30. they are considered as single time-in and should be added if their total time is greater than 30 minutes. I came up with the following query to get the day wise total duration excluding durations less than 30 minutes but not including the time-in duration as explained above.

SELECT
    DATE,  TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(duration))),'%H:%i' ) AS total,
    SUM(TIME_TO_SEC(duration)) AS seconds
FROM
    `timeintable`
WHERE
    id = '3' AND( DATE BETWEEN '2024-01-01' AND '2024-01-10') AND(TIME_TO_SEC(duration) >= 1800)
GROUP BY
    DATE
ORDER BY
    DATE ASC

Is it possible to achieve the same through SQL query rather than php array loop? My attempt using LEAD/LAG functions were unsuccessful.

Answer 1

WITH 
cte1 AS (
  SELECT *, CASE WHEN timein = LAG(timeout) OVER (PARTITION BY id, `date` ORDER BY timein)
                 THEN 0 
                 ELSE 1
                 END not_adjacent  
  FROM test
-- WHERE id IN ( {needed ids list} )
--   AND `date` IN ( {needed dates list} )
  ),
cte2 AS (
  SELECT *,
         SUM(not_adjacent) OVER (PARTITION BY id, `date` ORDER BY timein) group_no
  FROM cte1
  ),
cte3 AS (
  SELECT id, `date`, TIMESTAMPDIFF(SECOND, MIN(timein), MAX(timeout)) duration
  FROM cte2
  GROUP BY id, `date`, group_no
  HAVING duration >= TIME_TO_SEC('00:30:00')
  )
SELECT id, `date`, SEC_TO_TIME(SUM(duration)) daily_duration
FROM cte3
GROUP BY id, `date`

step-by-step fiddle with some comments.