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 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
The content is written by members of the stackoverflow.com community.
It is licensed under cc-wiki