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 1pm to 2pm. How can I find the next available block of say 1 hour?


i'm gonna answer this tonight, if no-one answered you by then

Written by Jules

I'm very curious to know if there's a "simple" way to do this...

Written by Paul Sonier

Accepted Answer

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
Written by Erik van Brakel
This page was build to provide you fast access to the question and the direct accepted answer.
The content is written by members of the stackoverflow.com community.
It is licensed under cc-wiki