sql oracle – select availability between dates

I have the below table INDISPONIBILITY, that respresents the availability of teachers, for example the teacher a1 is not available from 25-Jan-21 to 25-Jan-21 from 8:00 to 12:00.

I want to select teachers available in a givin date and hour. For example with a date 26-Jan-21 and hour 10:00 the teachers a1 and a2 are not available, but at 12:00 are available.

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

I tried with this query, but in this date 26-Jan-21 and hour 10:00 it returns the teachers a1 and a2.

public static String query = "SELECT * FROM ESP_ENSEIGNANT s n" + 
        "  LEFT JOIN INDISPONIBILITY ds ON ds.fk_teacher = s.teacherIdn" + 
        "  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

It took me a moment to understand why dates and hours are separated in the table. But yes, the teacher a2 is not booked from 2021-01-28 12:00 until 2021-01-29 14:00, but each day from 2021-01-28 to 2021-01-29 at the hours 12:00 to 14:00. This makes sense.

We can also observe that enddate is inclusive (the teacher is booked on 2021-01-29) and the hour is exclusive (the teacher is booked until before 14:00). Another thing to notice is that we are not only talking about full hours here, as there is 11:30 in your sample data.

You want to see if a teacher is available at a certain hour, say 2021-01-26 10:00. You don’t say for how long, so I expect a full hour (i.e. until right before 11:00). This means, we check the date and see whether the hour falls into their time range. We check the time tables with NOT EXISTS or NOT IN. As fot the time range we need to check for overlapping ranges. 10:00-11:00 is in the range of 09:00-12:00, but also overlapping with 09:00-10:30 and 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
);