Mysql – Calculate hours based on a course schedule in thesql

MySQLtime

I am trying to calculate user's attendance hours based on a course schedule.
The user enters a room and I'm tracking in & out time.
So I have the following tables

user_id     |   room_id      |  in                  |  out
-----------------------------------------------------------
1           | 1              |  2016-01-01 08:30:00 |  2016-01-01 12:30:00
1           | 1              |  2016-01-01 12:33:00 |  2016-01-01 14:00:00


course_id  |   room_id      |  start                |  end
-----------------------------------------------------------
1           | 1              |  2016-01-01 09:00:00 |  2016-01-01 11:00:00
2           | 1              |  2016-01-01 11:15:00 |  2016-01-01 12:00:00
3           | 1              |  2016-01-01 12:05:00 |  2016-01-01 13:00:00

Based on above schedule scenario user attended 3 hours and 37 mins.

Any idea on the query because I'm pretty confused and can't get it to work.
Maybe I have to rethink of how I'm storing the values?

Best Answer

Firstly, join the two tables on this condition:

attendance.room_id = schedule.room_id
AND schedule.`start` < attendance.`out`
AND attendance.`in` < 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` < a.`out`
    AND a.`in` < 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.

Related Question