Looking for empty time blocks between two dates?

Question

I have two MySQL tables, "schedule_time" and "appointments"

'schedule_time' has 2 DateTime fields, 'start' and 'end' - this is the time range when I am available for meetings.

"meetings" contain information about the appointment, but also the field "start" and "end", this will ultimately be within the range specified in "scheduled_time".

What is the best way to find empty time blocks when accounting for both tables?

Let's say that I have a "schedule_time" starting on 11/9/2010 from 8am to 2pm. and I have one “meeting” from 8 am to 10 am and one from 13:00 to 14:00. How can I find the next available block, say 1 hour?

Answer 1

I did this a while ago. We had a similar structure:

• Available (contains all working hours for the employee, flexible working hours)
• Destinations (similar to yours)

What I did basically was (steps):

• Get all start and end dates for employee <x>, sorted by start date
• let startAvailable = start time search (in your case, 9/9/2010 @ 8am)
• let value = first appointment on the appointment list
• get the start date of the first meeting. If the difference between them is large enough, then your block
• if not, let startAvailable = enddate of meeting
• remove the appointment from the list, let me schedule the next appointment
• repeat the process of checking the available block

Answer 2

. "" "".

-

select chour as [Free Hour] from Numbers n
inner join Scheduled s on n.chour >= s.start and n.chour < s.[end]
where chour not in
    (
    select chour from Numbers n
    inner join Appointments a ON n.chour >= q.start and n.chour < a.[end]
    )

- , chour - ,

DATE_ADD('2010-11-08', INTERVAL number HOUR)

, .

, , T-SQL: -)

: ( - , ), . - , , , .

Answer 3

, , :

create table ints(i tinyint); 
insert into ints values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 

- , , , , , .

SELECT 
h.HourWindowStart, h.HourWindowEnd
FROM (
  SELECT
  s.start + INTERVAL t.i*100 + u.i*10 + v.i HOUR AS HourWindowStart,
  s.start + INTERVAL t.i*100 + u.i*10 + v.i + 1 HOUR AS HourWindowEnd
  FROM scheduled_time s
  JOIN ints AS t 
  JOIN ints AS u 
  JOIN ints AS v 
  WHERE s.start + INTERVAL t.i*100 + u.i*10 + v.i HOUR < s.end
  ORDER BY HourWindowStart
) as h
LEFT JOIN appointments a ON a.end > h.HourWindowStart AND a.start < h.HourWindowEnd
WHERE a.start IS NULL

, / ( , ..), - , / sched_time, ..