Is there a way to calculate total breaks time in between first_check_in and last_check_out?

MySQL version is 8.0.16

I want to obtain the total break time for a given date the table is as follows:

id | employee_id  | check_in             | check_out           
--------------------------------------------------------------
 1 | 2103         | 2019-09-15 07:30:00  | 2019-09-15 09:20:00  
 2 | 2103         | 2019-09-15 10:35:00  | null
 3 | 2103         | 2019-09-16 08:00:00  | 2019-09-16 10:00:00
 4 | 2103         | 2019-09-16 11:00:00  | 2019-09-16 18:00:00
 5 | 2095         | 2019-09-16 08:30:00  | 2019-09-16 18:30:00

my expected results

id | employee_id  | check_in             | check_out          |  breaks 
--------------------------------------------------------------------------
 1 | 2103         | 2019-09-15 07:30:00  | null               |  75
 3 | 2103         | 2019-09-16 08:00:00  | 2019-09-16 18:00:00|  60
 5 | 2095         | 2019-09-16 08:30:00  | 2019-09-16 18:30:00|  0

my query only returns columns employee_id, date, first_check_in, last_check_out

SELECT 
    a1.employee_id,
    cast(a1.check_in as date) AS date,
    MIN(a1.check_in) AS first_check_in,
    CASE WHEN a2.employee_id IS NULL 
            THEN MAX(a1.check_out)     
        ELSE NULL
    END AS last_check_out
    FROM  attendance_employees AS a1  
    LEFT JOIN attendance_employees AS a2   
        ON a1.employee_id = a2.employee_id
        AND  CAST(a1.check_in AS date) =  CAST(a2.check_in AS date)
        AND a2.check_out IS NULL
    WHERE  1 = 1
    AND DATE(a1.check_in) in ('2019-09-15', '2019-09-16')
    GROUP BY  a1.employee_id, 
    CAST(a1.check_in AS date)
    ORDER BY  a1.employee_id,    
    date

Is there a way to calculate this break time in MySQL? Thanks

UPDATED my full query with time periods for expected working and break times ranges

SELECT  CONCAT(IFNULL(employees.first_name,''),' ',IFNULL(employees.surname,'')) as full_name,
        teams.description,
        time_periods.start_time as start,
        time_periods.end_time as end,
        time_periods.time_period_type,
        time_groups.name,
        cast(a1.check_in as date) AS date,
        MIN(a1.check_in) AS first_check_in,
        CASE WHEN a2.employee_id IS NULL 
                THEN MAX(a1.check_out)     
            ELSE NULL
        END AS last_check_out
        FROM  attendance_employees AS a1  
        LEFT JOIN attendance_employees AS a2   
            ON a1.employee_id = a2.employee_id
            AND  CAST(a1.check_in AS date) =  CAST(a2.check_in AS date)
            AND a2.check_out IS NULL
        JOIN employees
            ON employees.id = a1.employee_id
        JOIN teams
            ON employees.team_id = teams.id
        JOIN time_groups
            ON teams.time_group_id = time_groups.id
        LEFT JOIN day_time_group_time_period
            ON time_groups.id = day_time_group_time_period.time_group_id
            AND day_time_group_time_period.day_id = 3
        LEFT JOIN time_periods
            ON day_time_group_time_period.time_period_id = time_periods.id
        WHERE  1 = 1
        AND DATE(a1.check_in) in ('2019-09-15', '2019-09-16') 
        GROUP BY  a1.employee_id, 
        CAST(a1.check_in AS date),    
        a2.employee_id,
        time_periods.start_time,
        time_periods.end_time,
        time_periods.time_period_type
        ORDER BY  a1.employee_id,    
        date

giving the following results:

full_name| start   | end     | type| name     | date      | first_check_in     | last_check_out
-----------------------------------------------------------------------------------------------------
EMP1     | 08:00:00| 16:30:00| 1   | Day Shift| 2019-09-16| 2019-09-16 08:45:00| 2019-09-16 19:30:00
EMP1     | 10:00:00| 10:30:00| 2   | Day Shift| 2019-09-16| 2019-09-16 08:45:00| 2019-09-16 19:30:00
EMP1     | 12:00:00| 12:30:00| 2   | Day Shift| 2019-09-16| 2019-09-16 08:45:00| 2019-09-16 19:30:00
EMP2     | 08:00:00| 16:30:00| 1   | Day Shift| 2019-09-15| 2019-09-15 07:30:00| NULL
EMP2     | 10:00:00| 10:30:00| 2   | Day Shift| 2019-09-15| 2019-09-15 07:30:00| NULL
EMP2     | 12:00:00| 12:30:00| 2   | Day Shift| 2019-09-15| 2019-09-15 07:30:00| NULL

Where type 1 is for expected working hours range and type 2 is for expected break time range

Answer

In your original problem, following approach can work:

SELECT   dt.employee_id,
         Min(dt.check_in) AS check_in,
         CASE WHEN COUNT(dt.check_out) = COUNT(*) THEN MAX(dt.check_out)
              ELSE NULL
         END AS check_out,
         Sum(dt.break_interval) AS break_interval
FROM     (
                  SELECT   employee_id,
                           check_in,
                           check_out,
                           timestampdiff(minute, Lag(check_out) over w, check_in) AS break_interval
                  FROM     attendance_employees
                  WHERE    check_in BETWEEN '2019-09-15 00:00:00' AND      '2019-09-16 23:59:59'
                  WINDOW w AS (partition BY employee_id, date(check_in) ORDER BY check_in ASC) ) dt
GROUP BY dt.employee_id,
         date(dt.check_in);

| employee_id | check_in            | check_out           | break_interval |
| ----------- | ------------------- | ------------------- | -------------- |
| 2095        | 2019-09-16 08:30:00 | 2019-09-16 18:30:00 |                |
| 2103        | 2019-09-15 07:30:00 |                     | 75             |
| 2103        | 2019-09-16 08:00:00 | 2019-09-16 18:00:00 | 60             |

View on DB Fiddle

Details:

• In MySQL 8+, we can use Analytic/Window functions like LAG(), to compute the just previous check_out time for an employee on the same date. To get this, our partition window will be over employee, and the check_in date. Partition window will be sorted by check_in time, so that the LAG() returns the just previous entries only.
• We can determine the break_interval in minutes, using TimeStampDiff() function between the current row’s check_in time and previous row’s check_out time.
• Once we have determined these values for every row; we can utilize them in a Subquery (Derived Table) and do sum aggregation of break_interval for an employee on a specific date. Also, getting check_out time will require some special handling when there is no check_out in the last entry of an employee for a specific date, as you need NULL there and MAX(..) won’t return NULL.
• You can also make the query sargeable by avoiding DATE() function on the check_in time and use Range conditions.