Check if two times overlap

Question

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?

Your code is vulnerable to SQL injections. *Please* read this: http://stackoverflow.com/questions/601300/what-is-sql-injection and switch over to parameterised queries. The old `mysql_` functions are soon-to-be-deprecated, you should use MySQLi instead. — Polynomial, Jun 19 '12 at 09:54
@DarkRanger : can you type echo $result before while loop and let me know what you get? — Fahim Parkar, Jun 19 '12 at 10:17

Emil Vikström · Accepted Answer · 2017-07-07T08:03:01.160

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

P1 starts between the start and end of P2 (P2.from <= P1.from <= P2.to)
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.

This seems to be working perfectly. I've changed it a bit to allow exact same times (I'm working with engineers, and specifying 14:01 instead of just 14:00 seems to be a major brainflaw). Baie Dankie (Thank you very much in Afrikaans) — Bird87 ZA, Jun 19 '12 at 12:23

Brian · Answer 2 · 2012-06-19T10:14:24.053

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";
}

nyson · Answer 3 · 2012-06-19T11:26:58.033

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;
}

edited Jun 19 '12 at 11:26

answered Jun 19 '12 at 10:44

nyson

1,055
6
20

Might work, but then I would also need to checkk if the meeting doesn't overlap another one completely. Example: Meeting 1 starts. Meeting 2 starts, meeting 2 ends, meeting 1 ends. Looking at your code, it wont return false in this case... – Bird87 ZA Jun 19 '12 at 11:02
Your conditions are unfortunately wrong. What happens if $start is before meeting_start and $end is after meeting_end? The parentheses will evaluate to `false` and you negate it into `true`! It's also a rather strange approach to fetch all non-conflicting meetings when you really just want to know if there are conflicts or not. Try to match conflicting meetings instead and use `COUNT(*)` and `LIMIT 1` to speed it up a bit. – Emil Vikström Jun 19 '12 at 11:09
It seems like I'm a bit tired, I'm going to rewrite it >___ – nyson Jun 19 '12 at 11:23

score 0 · Answer 4 · answered Oct 29 '17 at 05:39

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.

P1.start_time <= P2.start_time <= P1.end_time true
P1.start_time <= P3.end_time <= P1.end_time true
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.

My answer already solves this. If P1 is a full subset of P2, then my first condition holds true: P1 will start within P2. — Emil Vikström, Mar 05 '18 at 10:25

score 0 · Answer 5 · answered Nov 27 '18 at 17:15

0

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

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

answered Nov 27 '18 at 17:15

Para-Diz

9
1

score 0 · Answer 6 · answered Feb 13 '22 at 20:27

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;
    }

Check if two times overlap

6 Answers6

Linked

Related