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.

    DATE,  TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(duration))),'%H:%i' ) AS total,
    SUM(TIME_TO_SEC(duration)) AS seconds
    id = '3' AND( DATE BETWEEN '2024-01-01' AND '2024-01-10') AND(TIME_TO_SEC(duration) >= 1800)

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

  • however, if two consecutive time-in for a day is more than or equal to 30 minutes then it should be considered. Does these timeranges must be adjacent? I.e. if in 4th row the times are 9:11-9:31 then this row should not be combined with row 3 and the combined value should not be taken into account? Also - if more than 2 consecutive timeranges gives 30 min or more whereas any 2 of them are less (9:00-9:10, 9:10-9:20, 9:20-9:30) does these rows should be counted?
    – Akina
    Jan 17 at 5:16
  • What is MySQL version precisely? SELECT VERSION();
    – Akina
    Jan 17 at 5:19
  • @Akina it should be considered only when they are adjacent. If the time-out time is equal to the time-in time of next row on same day. More than two consecutive rows satisfying above condition should also be considered for checking if greater than 30 minutes. MySQL version is 10.6.16-MariaDB-cll-lve. Jan 17 at 5:31
  • MySQL version is 10.6.16-MariaDB-cll-lve. MariaDB <> MySQL !!!
    – Akina
    Jan 17 at 5:39

1 Answer 1

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 (
         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.

  • Thank you @Akina. When I test it , I get the error like 'Warning: #1292 Incorrect datetime value: '18:30'" probably because time-in and timeout are saved as VARCHAR in my table. Jan 17 at 6:45
  • @ShahabMohd time-in and timeout are saved as VARCHAR Adjust cte1 and perform correct convertion (CAST) from VARCHAR to TIME (also - replace an asterisk with definite columns names and perform the convertion for them too). PS. Next time - always provide a sample of your table as CREATE TABLE + INSERT INTO, not as table-formatted text.
    – Akina
    Jan 17 at 7:15
  • Thank you. I will do that Jan 18 at 11:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.