superphonic superphonic - 11 days ago 5
MySQL Question

PHP free time blocks from start and end dates

I attempted to get these free time blocks purely from a MySQL query, but I was not able to construct a query that worked with the data that have. I have opted to simply get the

start
and
end
dates from the query and work out the free time blocks in PHP. However this is also proving difficult.

I have the following array (Just an example, days and times will vary):

$meeting = array(array('start' => '2016-11-14 16:00:00',
'end' => '2016-11-14 16:30:00'
),
array('start' => '2016-11-14 16:45:00',
'end' => '2016-11-14 20:00:00'
),
array('start' => '2016-11-14 14:00:00',
'end' => '2016-11-14 15:00:00'
),
array('start' => '2016-11-14 13:00:00',
'end' => '2016-11-14 14:00:00'
),
array('start' => '2016-11-11 15:20:00',
'end' => '2016-11-11 16:00:00'
),
array('start' => '2016-11-11 14:00:00',
'end' => '2016-11-11 15:00:00'
),
array('start' => '2016-11-07 07:00:00',
'end' => '2016-11-09 15:00:00'
)
);


What I would like to end up with is an array of days with start and end times for each free time block on that day, only between 09:00 and 17:00 on each day, excluding weekends. So for example, the meeting times array above should produce the following array of free time:

$daily_free = array('2016-11-09' => array(array('free_start' => '15:00:00',
'free_end' => '17:00:00'
)
),
'2016-11-11' => array(array('free_start' => '09:00:00',
'free_end' => '14:00:00'
),
array('free_start' => '15:00:00',
'free_end' => '15:20:00'
),
array('free_start' => '16:00:00',
'free_end' => '17:00:00'
)
),
'2016-11-14' => array(array('free_start' => '09:00:00',
'free_end' => '13:00:00'
),
array('free_start' => '15:00:00',
'free_end' => '16:00:00'
),
array('free_start' => '16:45:00',
'free_end' => '17:00:00'
)
)
);


As you can see only days with any free time appear in the array, the 7th and 8th of November have no free time so they do not appear at all. Also, the 12th and 13th of November do not appear in the free time array, this is because they fall on the weekend. However, if the 12th and 13th were not weekends, they should appear in the free time array as full days of free time (09:00 to 17:00).

One of the only ways I can think to achieve this is to create an array of each day, and within it have an array of 5 minute increments from 09:00 to 17:00 with a NULL value. Then loop through this array checking if each date and 5 minute increment is within the start and end time in the first array, and mark it as busy if it is. I can then loop through again and get the start and end times of the null values on each day? This doesn't seem like a great solution, and also fails if a meeting is less than minutes.

I don't think I can see the wood for trees at this point, and may be over thinking it.

Is there another solution?

EDIT:
SQL Fiddle for anyone who wants to try via SQL

Answer

They way I would work through it would be to try thinking of the problem like you are carving something out of a material. I'd start with a day full of free time [09 - 17] then loop over the appointments. For every appointment you are either splitting the existing free time (then re-ordering by start date) or changing the range. It's a lot easier if the times don't overlap. If the appointment is outside of the free time range you can ignore it.

{----}  {--------} Free Time Range
           [---]   Appointment in the middle - you need to split

{----}  {--------} Free Time Range
        [---]      Appointment at the beginning - change the start date

{----}  {--------} Free Time Range
             [---] Appointment at the end - change the end date

{----}  {--------} Free Time Range
        [--------] Appointment fills the whole spot - delete
Comments