How do I work out time slots greater than 5 hours
I have a booking application where you can book time against a room. I need a way to find where it is booked for more than 5 hours consecutively to do some reporting.
This can be anytime over a 24 hour period.
So for example it might be booked
Monday
8:00am - 10:00am (2 hours)
10:00am - 13:30pm (3.5 hours)
This should be classed as over 5 hours consecutively.
There could be an occasion where it would be booked like this:
Monday
10:00pm - 12:00am (2 hours)
Tuesday
12:00am - 4:00am (4 hours)
Again this is over 5 hours consecutively
My Models are the following (simplified):
Room
- has_many timeslots
TimeSlot
- start_time (:date_time)
- end_time (:datetime)
I am currently looping through each day of the week and looping through each time slot for that day and then checking to see if start_time == end_time of the next slot to work out if it is consecutive. Not sure if this is the easiest way or if there is something more obvious that would do what I need.
Your help is really appreciated.
I don't think there's any way to do this in the database with the current setup, so what you're doing is probably what I would do.
You could cache the results of the report and re-calculate them every day or week on a regular basis so you don't have to run this expensive calculation all the time.
I'd probably stick with what you've got.