I have a problem with MYSQL query to get the overlapping with reservations.
We are working at project to book a parking for cars, And we have a problem in searching for availability
if the user searches for a parking between two dates, we need to get the remaining available number of parking.
the problem here is not the overlapping dates and times
for example, we have 3 reservations, the 1st one from 1-3/8, the second is from 3-5/8 and the 3rd is from 5-7/8 and we have 5 available spots, and the user is looking for 1 - 7/8, the remaining available spots is 4, not 2. because each reservation starts after another one is done.
we tried several solutions, like checking every hour of search
but that’s not a good solution especially when searching for long times/dates
This is an image explain what is the problem
Thanks