Comparing Time Availability Using Ruby on Rails

Question

I want to have an availability table for several objects and compare them. For example, a tenant will look for a rental unit that will be available from 1-3 pm on Monday.

To do this, I thought to divide the week into 30-minute time intervals and give each time interval an identifier. Then the date range will look for the corresponding time interval identifiers.

However, then I would need 5040 records to track every time interval, and I'm not too excited about this manually (and in Ruby, I don't know how I would do it). Is there a more elegant method using datetimes or some Rails plugin that will do something similar to what I want, but much more easily?

The critical requirement is that it must compare multiple separated time blocks.

EDIT: that would be a bonus if there was a way to compare multiple time blocks to see a better match. For example, if I want to rent a camcorder on Monday, Wednesday, and Friday, and one camera is available all three days, and the other is available only on two of these days, I want to be able to compare and rank cameras based on the best fit.

Answer 1

You do not need to have such a complex model. All you need is a table in which time blocks are indicated in which rental units are not available , in other words, a table with booked time for each rental unit. For instance:

create table BOOKING ( rental_unit_id int , start_datetime date , end_datetime date ) 

Now, if you want to get a list of rental units that are available for the entire specified time block, say @Arrive @Depart, then all you have to do is run this query:

 select R.rental_unit_id -- (and anything else you want) from RENTAL_UNIT R where not exists ( select B.rental_unit_id from BOOKING B where B.rental_unit_id = R.rental_unit_id and end_datetime > @Arrive and start_datetime < @Depart ) 

This request tells me to get a list of rental units that do not have a reservation that overlaps our search period (from @Arrive to @Depart). Please note that you can play with <= @Depart depending on whether you want to have an inclusive or exclusive endpoint for booking periods.

EDIT: Handling multiple accessibility blocks

@OP has added a requirement for several accessibility blocks. If you rent equipment for several days, then @Arrive and @Depart just happen on different dates. If, for example, in the @OP example, several days have gaps in the middle - presumably where the equipment comes back and can be leased to someone else - then you just need to add additional sentences where not exists - one for each independent block desired availability. Simply “and” them together, and you will find rental units that are available at all required time intervals. The concept of better or worse matches does not actually apply. The rental unit is either available or not.

Answer 2

I would say that your time interval should be as follows:

 TimeSlot rental_id : int -> foreign key to your rental table (housing, whatever) start_time : time end_time : time day_of_week : int 1-7 

Then a search from availability between two points will be: (let's call these AVAILABLE_SLOTS)

 ( (wanted_start_time >= TimeSlotTable.start_time && wanted_start_time <= TimeSlotTable.end_time) OR (wanted_end_time >= TimeSlotTable.start_time && wanted_end_time <= TimeSlotTable.end_time) ) AND Optionally: ( wanted_day_of_week = TimeSlotTable.day_of_week) 

where TimeSlotTable is the name of your table.

Then, if you really want to get more detail from the query, you might have an exception table. That is, a table in which the owner of the lease indicates if they are not available on a certain day, which will require the user interface to request a date in addition to the time. (let's call it EXCEPTIONS)

 ExceptionTimeSlot rental_id : int -> foreign key to your rental table (housing, whatever) date : date 

and request:

 ( wanted_date = ExceptionTimeSlotTable.date) 

Finally, you want rent_ids to be in AVAILABLE_TIMESLOTS, but not in EXCEPTIONS.

All this is similar to an event calendar, so you can select an even calendar and configure it for your purposes and call the time intervals of events.