Complex MySQL Query - Checking for overlapping DAT

2019-06-14 10:28发布

问题:

I'm trying to write a function to move an scheduled task. The schedule can not overlap with any other event. My user inputs are as follows:

  • schedule_id (int)
  • new_start_time (DATETIME)

My table structure is as follows:

Schedules
| schedule_id | start_time              | end_time              |   task_id
|     1       | 2015-12-21 02:00:00     | 2015-12-21 04:00:00   |      1 
|     2       | 2015-12-21 08:30:00     | 2015-12-21 09:30:00   |      1
|     3       | 2015-12-22 01:00:00     | 2015-12-22 02:00:00   |      2

Tasks
| task_id     | name                    | max_duration
|       1     | do things               |    2
|       2     | do stuff                |    1

A user has between start_time and end_time to start a "task". The user can not begin the "task" until that window. Once that user begins the task they have whatever the max_duration for that task ID is to complete it. There is also a 15 minute window to set up for the next task. That means a user who starts a task 1 second before the end of the window still has max_duration amount of time to complete the task. Therefore the "actual window" that nothing can be scheduled in is start_time to (end_time+max_duration+15). I would like to move an event (or insert a new one) but I must check for overlaps. Essentially I must ensure:

  • Does the start_time from user input run into any other schedule's end_time+max_duration+15?
  • Does the end_time+max_duration+15 run into any other schedule's start time. end_time is simply obtained by taking the new start_time and adding the original duration (end_time = (orig_end_time-orig_start_time)+start_time

For example, the above table is valid for schedule_id's 1 and 2 because a user can start any time between 2:00 and 4:00. Assuming he starts right at the end, 3:59:59 the event will last at max until 5:59:59. Even with the cleanup window of 15 minutes this still leads to 6:14:59 and since the next schedule starts at 8:30 this is ok.

I've been wrapping my head around this for hours. I would like to do it in pure MySQL however I am considering using PHP if I really have to. Even in PHP this problem seems difficult. Sure I could grab every schedule with a start time a day or two earlier and an end time a day or two later then compare my interval but that seems very hacky.

Any ideas?