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
);
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…