sql oracle - 选择日期之间的可用性

Question

我有下表 INDISPONIBILITY ，代表教师的可用性，例如教师 a1 在 25-Jan-21 到 25-Jan-21 的 8:00 到 12:00 不可​​用。
我想选择在给定日期和时间可用的教师。例如，对于日期 26-Jan-21 和小时 10:00，教师 a1 和 a2 不可用，但在 12:00 可用。

id  endDate    startDate  startHour   endHoure  fk_teacher
1   25-Jan-21  25-Jan-21   800         1200      a1
2   25-Jan-21  25-Jan-21   1400        1600      a1
3   26-Jan-21  26-Jan-21   1000        1130      a1
4   29-Jan-21  28-Jan-21   1200        1400      a2
5   26-Jan-21  26-Jan-21   1000        1130      a2

我尝试过这个查询，但在这个日期 26-Jan-21 和小时 10:00 它返回教师 a1 和 a2。

public static String query = "SELECT * FROM ESP_ENSEIGNANT s \n" + 
        "  LEFT JOIN INDISPONIBILITY ds ON ds.fk_teacher = s.teacherId\n" + 
        "  WHERE (?1 NOT BETWEEN ds.STARTDATE AND ds.ENDDATE) AND (?2 NOT BETWEEN ds.startHour AND ds.endHour)";

    @Query(nativeQuery = true, value = query)
List<TeacherEntity> findAllByUps(Date effectDate, int hour);

Answer 1

我花了一点时间才明白为什么表格中的日期和时间是分开的。但是是的，老师a2不是从2021-01-28 12:00预定的直到 2021-01-29 14:00 ，但每天来自 2021-01-28至 2021-01-29在小时 12:00至 14:00 .这是有道理的。
我们还可以观察到 enddate是包容性的(教师在 2021-01-29 上被预订)并且小时是不包括的(教师被预订到 14:00 之前)。另一件需要注意的事情是，我们不仅在这里谈论完整的小时数，还有 11:30在您的样本数据中。
您想查看某个时间是否有老师有空，例如 2021-01-26 10:00 .你没有说多长时间，所以我预计整整一个小时(即直到 11:00 之前)。这意味着，我们检查日期并查看小时是否在他们的时间范围内。我们用 NOT EXISTS 查看时间表或 NOT IN .至于时间范围，我们需要检查重叠范围。 10:00-11:00在 09:00-12:00 的范围内，但也与 09:00-10:30 重叠和 10:30-12:30

select *
from teacher
where teacher_id not in
(
  select fk_teacher
  from timetable
  where :date between startdate and enddate
  and starthour <= :hour + interval '1' hour
  and endhour > :hour
);