从select语句中查找重叠时间

Question

Okay, so I have a table (timetable) in a database (calendar) which if formatted like so:

+----+--------+-----------+-----------+---------+
| id |  name  |    day    | startTime | endTime |
+----+--------+-----------+-----------+---------+
|  1 | George | Sunday    | 12:00     | 14:00   |
| 2  | Dan    | Monday    | 13:30     | 15:30   |
|  3 | Jeff   | Wednesday | 12:00     | 14:00   |
|  4 | Bill   | Monday    | 13:45     | 15:45   |
+----+--------+-----------+-----------+---------+

Then I have some PHP as follows:

  <?php
 $sql = "SELECT * FROM timetable WHERE id IN (1, 2, 3, 4)"
    $result = $con->query($sql);
     while ($row = mysqli_fetch_assoc($result)) {
         $array2[] = $row;
    }    

  echo json_encode($array2, JSON_PRETTY_PRINT);

  ?>

And that outputs the entire table, as an associated array in JSON format.

I want it to only output the two that clash. There will never be more than two that clash in the database due to the input method, so it only needs to check if there's one clash, and output the JSON for both rows.

I'm not sure where to start. Is it better to do it programatically via PHP, or is there a way to do with with mysql? I was thinking if it's done programatically, maybe two nested for loops and if statements for greater than or less than on the time fields? But seems messy, and I'm thinking there's likely a more clever end elegant solution that I can't think of.

Thank you for any help you can provide.

Answer 1

You can do it with JOIN in SQL like below:-

To find "any overlap", you compare the opposite ends of the timeframe with each other.

SELECT * FROM timetable a
JOIN timetable b 
    on a.starttime <= b.endtime
    and a.endtime >= b.starttime
    and a.name != b.name;