dpd3982 2016-04-10 15:26
浏览 26
已采纳

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

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?

  • 写回答

1条回答 默认 最新

  • douwen1549 2016-04-10 16:34
    关注

    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)

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 孟德尔随机化结果不一致
  • ¥15 深度学习残差模块模型
  • ¥50 怎么判断同步时序逻辑电路和异步时序逻辑电路
  • ¥15 差动电流二次谐波的含量Matlab计算
  • ¥15 Can/caned 总线错误问题,错误显示控制器要发1,结果总线检测到0
  • ¥15 C#如何调用串口数据
  • ¥15 MATLAB与单片机串口通信
  • ¥15 L76k模块的GPS的使用
  • ¥15 请帮我看一看数电项目如何设计
  • ¥23 (标签-bug|关键词-密码错误加密)