Calculate hours based on a course schedule in mysql
Firstly, join the two tables on this condition:

attendance.room_id = schedule.room_id
AND schedule.`start` &lt; attendance.`out`
AND attendance.`in` &lt; schedule.`end`


The first predicate matches the rooms, obviously, and the other two determine if the ranges intersect. Any two ranges intersect if the beginning of each is less than the end of the other.

Secondly, take the time difference between GREATEST(attendance.`in`, schedule.`start`) and LEAST(attendance.`out`, schedule.`end`).

Finally, just aggregate the time differences per user.

So, the complete query might look like this:

SELECT
  a.user_id,
  SUM(TIMESTAMPDIFF(MINUTE,
                    GREATEST(a.`in`, s.`start`),
                    LEAST(a.`out`, s.`end`))) AS total_attendance
FROM
  attendance AS a
  INNER JOIN schedule AS s
    ON a.room_id = s.room_id
    AND s.`start` &lt; a.`out`
    AND a.`in` &lt; s.`end`
GROUP BY
  a.user_id
;


The result of this query is in minutes, as determined by the first argument of the TIMESTAMPDIFF function. Other than simply changing the argument to return the value in different units, you can also return it as time using the SEC_TO_TIME function like this:

SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, ...))) AS total_attendance


Specific formatting, like e.g. 3 hours 37 minutes, may be achieved using MySQL too, but normally it is better to use the presentation level for that.