php - Finding open contiguous blocks of time for every day of a month, fast -

i working on booking availability system group of several venues, , having hard time generating availability of time blocks days in given month. happening server-side in php, concept language agnostic -- doing in js or else.

given venue_id, month, , year (6/2012 example), have list of events occurring in range @ venue, represented unix timestamps start , end. data comes database. need establish what, if any, contiguous block of time of minimum length (different per venue) exist on each day.

for example, on 6/1 have event between 2:00pm , 7:00pm. minimum time 5 hours, there's block open there 9am - 2pm , between 7pm , 12pm. continue 2nd, 3rd, etc... every day of june. (most) of days have nothing happening @ all, have 1 - 3 events.

the solution came works, takes waaaay long generate data. basically, loop every day of month , create array of timestamps each 15 minutes of day. then, loop time spans of events day 15 minutes, marking "taken" timeslot false. remaining, have array contains timestamp of free time vs. taken time:

//one day's array after processing through loops (not real timestamps) array(   12345678=>12345678,   // <--- avail   12345878=>12345878,   12346078=>12346078,   12346278=>false,      // <--- not avail   12346478=>false,   12346678=>false,   12346878=>false,   12347078=>12347078,   // <--- avail   12347278=>12347278 ) 

now need loop array find continuous time blocks, check see if long enough (each venue has minimum), , if establish descriptive text start , end (i.e. 9am - 2pm). whew! time looping done, user has grown bored , wandered off youtube watch videos of puppies; takes ages examine 30 or days.

is there faster way solve issue? summarize problem, given time ranges t1 , t2 on day d, how can determine remaining time left in d longer minimum time block m.

this data assembled on demand via ajax user moves between calendar months. results cached per-page-load, if user goes july second time, data generated first time reused.

any other details help, let me know.

---

edits

per request, database structure (or part relevant here)

*events* id        (bigint) title     (varchar)  *event_times* id        (bigint) event_id  (bigint) venue_id  (bigint) start     (bigint) end       (bigint)  *venues* id        (bigint) name      (varchar) min_block (int) min_start (varchar) max_start (varchar) 

events start on 15 -- :00, :15, :30, :45

data dump of of actual time stamps: http://pastebin.com/k1prkj44

this should in right direction (i hope). iterates on database records fall within period (e.g. 1 month).

from set find "gaps" between bookings , fill array (with date key).

$days = array();  $stmt = $db->prepare('select     date(from_unixtime(start)) sdate,     group_concat(hour(from_unixtime(start)),",", minute(from_unixtime(start)) order start asc separator ",") from_hours,     group_concat(hour(from_unixtime(end)), ",", minute(from_unixtime(end)) order start asc separator ",") to_hours     event_time     start >= ? , end < ? , start < end     group sdate     order sdate');  $stmt->execute(array($from, $to)); foreach ($stmt->fetchall(pdo::fetch_assoc) $row) {     // , formatted as: [hh,mm,hh,mm,hh,mm,...]     $from = explode(',', $row['from_hours']);     $to = explode(',', $row['to_hours']);      // skew 2 arrays:     // - add 00:00 in front of $to     // - add 23:59 @ of $from     array_unshift($to, 0, 0);     array_push($from, 23, 59);      ($i = 0, $n = count($from); $i != $n; $i += 2) {         // create time values         $start = new datetime("{$to[$i]}:{$to[$i+1]}");         $end = new datetime("{$from[$i]}:{$from[$i+1]}");          // calculate difference         $diff = $start->diff($end);         // difference must positive , @ least 5 hours apart (depending on venue)         if (!$diff->invert && $diff->h >= 5) {             $days[$row['sdate']][] = array($start->format('h:i'), $end->format('h:i'));         }     } } 

at end, $days contain:

[2012-06-30] => array     (         [0] => array             (                 [0] => 00:00                 [1] => 05:30             )          [1] => array             (                 [0] => 11:30                 [1] => 23:59             )      ) 

there few variables should change make calculations:

1. minimum time (e.g. how in morning)
2. maximum time (e.g. until how late @ night)
3. minimum booking time (depending on venue)

also, keys missing in resulting array available whole day, should prime $days array before start loop days within period you're querying.

let me know if helped :)