1

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

Example Schedule

Josh Hudnall
  • 1,021
  • 8
  • 16

1 Answers1

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)

ysth
  • 96,171
  • 6
  • 121
  • 214
  • Thank you! I'll check it out and mark the answer as soon as I see the results. I knew there had to be a way, and good note on only needing to check the start times. That seems so obvious once you said it, but it hadn't occurred to me. – Josh Hudnall May 07 '21 at 20:12
  • So I was never able to get this to work. I'll probably spend some more time with it in the future, but in the meantime it pointed me in the right direction with something that is working. Thank you. Given that I believe it's the right solution even if it needs tweaks, I'm marking as correct. – Josh Hudnall May 18 '21 at 17:14