PHP / MySQL - 查找可用的时间段

Question

I have found a few questions on similar topic, but I can't find a solution to this specific problem.

I'm currently working on a booking system, where it should be possible for users to book services online. I'm stuck at finding a way to display the available timeslots within a specific day.

The information that I know is the length of the nedded time slot (eg. 30 minutes) and the business hours where it's possible to book a time.

I already have a MySQL table which stores already existing appointments.

id  start (datetime)        end (datetime)
1   '2016-04-09 12:00:00'   '2016-04-09 13:30:00'
2   '2016-04-09 15:00:00'   '2016-04-09 17:00:00'
3   '2016-04-09 12:00:00'   '2016-04-09 15:45:00'
4   '2016-04-09 13:00:00'   '2016-04-09 16:15:00'
...

I can't really seems to find a good solution (MySQL or PHP) for retrieving a list of available time slots. The only condition is that time slots can only start in quarterly steps. Ie. for the example data above on the 16th available one hour time slots would be: 09:00, 09:15, 09:30, 09:45, 10:00, ..., 12:00, 16:00, 16:15, ..., 17:00.

Please note that even though there may be overlapping times (as in sample data) an available time slot cannot overlap.

What would you think is the best way to approach this?

Answer 1

Due to the nature of SQL, you can only select on existing data:

You will have to add a table with all the time slots (table can be generated for the requested period as temporary table):

 CREATE PROCEDURE genSlots(start_date DATETIME, end_date DATETIME) 
 BEGIN
     DECLARE v_end_time DATETIME;
     DROP TABLE IF EXISTS tempSlots;
     CREATE TEMPORARY TABLE tempSlots (
         start_time,end_time DATETIME
     );

     WHILE(start_date<end_date) DO
         SET v_end_time=DATE_ADD(start_date,INTERVAL 15 MINUTE);
         INSERT INTO tempSlots VALUES (start_date,v_end_time);
         SET start_date=end_time;
     END WHILE;
 END;

Now with this table, you can use a JOIN to find the available slots. For this is it important that the tables have either the same interval (15 minutes) or that you pre-process your already registered time intervals. You can do that with a similar procedure like the one above, or use the end_time in the temporary table in a smart fashion (INNER JOIN the result of the SELECT below back on itself with start_time=end_time and you automatically keep the longer then 15 minute intervals assuming you split your table in 15 minute intervals)

The JOIN example:

CREATE TEMPORARY TABLE possible_timeslots AS
SELECT a.start_time,a.end_time
FROM tempSlots a
LEFT JOIN your_table b ON a.start_time=b.start_time
WHERE b.start_time IS NULL;

Then to get intervals longer then 15 minutes:

SELECT a.start_time,a.end_time
FROM possible_timeslots a
INNER JOIN possible_timeslots b ON a.end_time=b.start_time;

(Watch the end of the interval: The last interval in the generated temp_slots table will of course not show what is available after that)