Calculate maximum overlapping events within a range in PHP or MySQL

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

https://stackoverflow.com/questions/67393981/calculate-maximum-overlapping-events-within-a-range-in-php-or-mysql May 05, 2021 at 09:11AM