Calculate maximum overlapping events within a range in PHP or MySQL

Question

This has probably been asked before, but my searches are coming up short. Imagine a car reservation system where I have three cars I can rent out at any given time.

How, using PHP and MySQL 5.7, can I make sure I do not exceed this limit within the bounds of a new reservation's timeframe?

I found this answer, but I'm not sure how to workaround the fact that MySQL 5.7 does not support window functions.

I could load in all reservations that conflict and use PHP to step through on an interval counting which reservations conflict at that interval and take the max, but I'm thinking this is a common problem with a better solution.

Info

• The database stores start_time and end_time as TIMESTAMP datatypes.
• Need to check at intervals of 1 minute from the start to the end of a range.

Diagram

Answer 1

So to check a particular moment, you do:

select count(1) conflicts
from events
where start_time <= desired_time && end_time > desired_time;

To find the maximum number of conflicts for a desired time range, you don't need to check every minute, you only need to check at the start time (or desired start time, if that is later) of each conflicting event:

select coalesce(max(conflicts),0) max_conflicts
from (
    select count(1) conflicts
    from (
        select distinct greatest(DESIRED_START_TIME, start_time) desired_time
        from events
        where start_time < DESIRED_END_TIME && end_time > DESIRED_START_TIME
    ) conflicting_events
    join events on start_time <= desired_time && end_time > desired_time
) point_in_time_conflicts;

(untested)