Time based resource availability for events in mysql/php

Question

For an event system I need to check if there are enough resources available for an event. Events have a a start and end timestamp, and needs a number of resources (for the sake of simplicity we'll assume that there is only 1 type of resource). When a new event is booked, there should be a check if it's possible. All events are doctrine-based php objects, the timestamps don't need to be any more specific than minutes.

I'm running into troubles in an example like the following:

• 1 resource is needed from 12:00 to 13:00
• 1 resource is needed from 13:00 to 14:00
• a new event is created that wants 1 resource from 12:00 to 14:00

Let's assume we have a total of 2 resources, then this should be possible. I currently select all events that have an overlap with the new event (so from 12:00 to 14:00), and take the sum of all resources needed by these events. This would mean that the new event is not possible.

How can I check (efficiently) if a new event is possible? Either using only a doctrine query, or making a basic selection using a doctrine query and doing filtering with php afterwards. The only solution I came up with so far is: select all events from 12:00 to 14:00, and in check for every minute (so 120 iterations) if a resource is overbooked.

Answer 1

Since you're working with timestamps this can be fairly easy.

You would pull all events for the day. Then loop over each of them to see if the new event's timestamp boundaries conflict with an existing event's timestamp boundaries

$conflict = false;
foreach($arrEvents as $event) {
    if ($newStart >= $existingStart && $newStart < $existingEnd) {
        // The start time falls within this event's time
        $conflict = true;
    } elseif ($newEnd > $existingStart && $newEnd <= $existingEnd) {
        // The end time falls within this event's time
        $conflict = true;
    } elseif ($newStart <= $existingStart && $newEnd >= $existingEnd) {
        // Existing event falls completely within the new event
        $conflict = true;
    }
}

Obviously looping over all events isn't the most efficient way possible but this is a basic solution that I have used in the past where efficiency wasn't an issue