Check if two times overlap

Albert picture Albert · Jun 19, 2012 · Viewed 10k times · Source

I want to see if a time I read from a db overlaps with a time provided by a user.

My database looks like this:

-----------------------------------------------
|organiser|meeting_start|meeting_end|boardroom|
-----------------------------------------------
| John Doe| 1340193600  | 1340195400| big     |
-----------------------------------------------

My code looks like this:

date_default_timezone_set('Africa/Johannesburg');
$from = strtotime($_GET['meeting_date'] . ' ' . $_GET['meeting_start']);
$to = strtotime($_GET['meeting_date'] . ' ' . $_GET['meeting_end']);
$another_meeting = false;
$meeting_date = strtotime($_GET['meeting_date']);
$meeting_next = $meeting_date + 86400;

$result = mysql_query("SELECT meeting_start, meeting_end FROM admin_boardroom_booking WHERE boardroom = '" . $_GET['boardroom'] . "' AND meeting_start >= '" . $meeting_date . "' AND meeting_end < '" . $meeting_next . "'")or die(mysql_error());
while($row = mysql_fetch_array($result)) {
    $from_compare = $row['meeting_start'];
    $to_compare = $row['meeting_end'];

    $intersect = min($to, $to_compare) - max($from, $from_compare);
    if ( $intersect < 0 )
        $intersect = 0;

    $overlap = $intersect / 3600;
    if ( $overlap <= 0 ) {
        $another_meeting = true;
        break;
    }
}

if ($another_meeting)
    echo 'ERROR';

If I type two overlapping times on purpose, it doesn't echo error. What am I doing wrong?

Answer

Emil Vikstr&#246;m picture Emil Vikström · Jun 19, 2012

Two time periods P1 and P2 overlaps if, and only if, at least one of these conditions hold:

  1. P1 starts between the start and end of P2 (P2.from <= P1.from <= P2.to)
  2. P2 starts between the start and end of P1 (P1.from <= P2.from <= P1.to)

This will catch partly overlapping periods as well as periods where one completely covers the other. One of the periods must always start (or end) inside the other if they are overlapping.

So $another_meeting would be defined by:

$another_meeting = ($from >= $from_compare && $from <= $to_compare) ||
                   ($from_compare >= $from && $from_compare <= $to);

You may want to change the borderline cases to strict < checks if one event can start at the exact same time as another ends.