'Check if two times overlap

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?

phpmysqltimeoverlapping


Solution 1:[1]

Was just doing something similar.... but just with times....

$startTime = strtotime("7:00");
$endTime   = strtotime("10:30");

$chkStartTime = strtotime("10:00");
$chkEndTime   = strtotime("12:10");

if($chkStartTime > $startTime && $chkEndTime < $endTime)
{
    // Check time is in between start and end time
    echo "1 Time is in between start and end time";
}
elseif(($chkStartTime > $startTime && $chkStartTime < $endTime) || ($chkEndTime > $startTime && $chkEndTime < $endTime))
{
    // Check start or end time is in between start and end time
    echo "2 ChK start or end Time is in between start and end time";
}
elseif($chkStartTime==$startTime || $chkEndTime==$endTime)
{
    // Check start or end time is at the border of start and end time
    echo "3 ChK start or end Time is at the border of start and end time";
}
elseif($startTime > $chkStartTime && $endTime < $chkEndTime)
{
    // start and end time is in between  the check start and end time.
    echo "4 start and end Time is overlapping  chk start and end time";
}

Solution 2:[2]

I'd probably solve it with something like this:

function avaliable($start, $end) {
  // checks if there's a meeting between start or end
  $q = "SELECT * FROM admin_boardroom_booking "
    . "WHERE NOT (meeting_start BETWEEN '$end' AND '$start' "
    . "OR meeting_end BETWEEN '$end' AND '$start')";
  $result = mysql_query($q);

  // returns true on no conflicts and false elseway
  return mysql_num_rows($result) === 0;
}

Solution 3:[3]

Emil Vikström's answer is correct, but there is a scenario needed to be considered.
Like, One of the time ranges is a subset of another time range.
So, assume that P1{start_time, end_time} and P2{start_time, end_time} will overlap when any of the following is true.

  • P1.start_time <= P2.start_time <= P1.end_time
  • P1.start_time <= P2.end_time <= P1.end_time
  • P2.start_time <= P1.start_time <= P1.end_time <= P2.end_time

Just assuming that the times are sorted in ascending order. Example below: 

|-----------------------------------|
|  Start time  |   End time  | Name |
|-----------------------------------|
|    10:00     |    14:00    |  P1  |
|-----------------------------------|
|    12:00     |    16:00    |  P2  |
|-----------------------------------|
|    08:00     |    12:00    |  P3  |
|-----------------------------------|
|    07:00     |    16:00    |  P4  |
|-----------------------------------|

If you consider P1 as the base time and you want to check P2, P3, P4 against it.

  1. P1.start_time <= P2.start_time <= P1.end_time true
  2. P1.start_time <= P3.end_time <= P1.end_time true
  3. P4.start_time <= P1.start_time <= P1.end_time <= P4.end_time true

This is how you can check if any of the time is overlapping another one or not.

Solution 4:[4]

In case when $to is always later than $from we can use this more shorter solutions

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

Solution 5:[5]

My solution that deals with the edge cases and is quite compact:

    private function meetingOverlap($meeting1, $meeting2){
        if ($meeting1['start']>$meeting2['start'] && $meeting1['start']<$meeting2['end'] ) return true;
        if ($meeting2['start']>$meeting1['start'] && $meeting2['start']<$meeting1['end'] ) return true;
        if ($meeting2['start']==$meeting2['start'] && $meeting1['end']==$meeting2['end'] ) return true;
        return false;
    }

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2
Solution 3 ssi-anik
Solution 4 Para-Diz
Solution 5 justadev

Related Questions