Finding empty time blocks between two dates?

I have 2 MySQL tables, 'scheduled_time' and 'appointments'

'scheduled_time' has 2 DateTime fields, 'start' and 'end' - this is a time range of when I am available for appointments.

'appointments' contains appointment details but also a 'start' and 'end' field, this will ultimately be within the range specified in 'scheduled_time'.

What is the best way for me to find empty time blocks when taking into account both tables?

Lets say I have 'scheduled_time' starting 11/9/2010 from 8am to 2pm. and I have one 'appointment' from 8am to 10am and one from 1开发者_开发技巧pm to 2pm. How can I find the next available block of say 1 hour?

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

• Available (contained all working hours for an employee, flexible working hours)
• Appointments (similar to yours)

What I did was basically this (steps):

• Get all start and end datetimes for employee < x >, sorted by startdate
• let startAvailable = start of the time search (in your case 11/9/2010 @ 8am)
• let appointment = first appointment in the list of appointments
• get the startdate of the first appointment. If the difference between these is big enough, there's your block
• if not, let startAvailable = enddate of appointment
• remove appointment from the list, let appointment be the next appointment
• repeat the process of checking for an available block

First, create a number table. Here as example named "Numbers" with a column "number".

Then you can do something like

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]
    )

Numbers is my Numbers table, chour is a computed column defined as

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

You can also store it as a persisted column of course.

Sorry, if the syntax isn't completely right, I do T-SQL normally :-)

Edit: This technique only works for fixed blocks of time (hour is just an example, you could do half hours just as easily), but it's quite efficient and readable in this case. A usual application in business is mapping dates, because that's the granularity where most contracts live and you can cover a lot of days with a small number table.

This is a great place to use a dummy integer table, which helps you to create data from nothing. Here is an example:

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

You can then use a few cross joins with this table to generate a table of hour-long windows represented by your scheduled_time table, and left join the result against the appointments table to find windows that do not have something already scheduled.

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

You can tweak various parts of this process to calculate larger/smaller availability windows (by the half hour, by the day, etc), use more or less cross joins of the integer table based on the maximum number of availability windows that could be represented in a single start/end range in scheduled_time, pre-create a date calendar and join against the two tables, etc.