I have a reservation table that has from_date, to_date, and parking_location_id. What is the best way to calculate location spots availability for each location and each hour?

I tried to write a query that calculates availability from reservation table but this query is very slow when the period is long even though the from_date and to_date columns are indexed.

Another approach to speed things up was to create a location availability table

enter image description here

The contains all locations and all dates and hours in a range of years and whenever a reservation is saved a trigger updates the reserved spots in this table. However, in this approach, the table rows size grows exponentially and we need to add a range of dates with location ids whenever a new location is added. Any ideas how can I improve this design or what alternatives do we have?

The slow query tries to calculate number of reserved spots per date and then we find the max number in that date range.

select  sum(r.number_of_slots) as reserved_spots
from location l 
inner JOIN reservation  r on r.location_id=  l.id  , time_dimension t
where t.db_date between r.from_date and r.to_date and 
 t.db_date < r.to_Date and t.db_date > r.from_date  t.id=type_id and group by db_date;

Time dimension table contains a list of all dates in 10 years.

  • Which table are from_date and to_date in? (Please add aliases.) Also db_date. – Rick James Feb 8 '17 at 23:41
  • So, that is a subquery with ORDER BY reserved_spots DESC LIMIT 1 in the outer query? – Rick James Feb 8 '17 at 23:44
  • @RickJames I aliases to query. I don't have order by in the query not sure what do you mean? – Feras Odeh Apr 7 '17 at 8:54
  • It seems you used the alias t for time_dimension and for location? – mendosi Apr 7 '17 at 9:22
  • You didn't specify: what is the granularity of your db_date table? Is it every hour? – mendosi Apr 7 '17 at 9:33

I'd suggest to use a calendar or schedule table.

In my example I've set up a 3-day table calendar with intervals of one hour:

(Do a google search to find out some date series generator, you'll find a lot)

    Hours table
2017/01/01 00:00:00
2017/01/01 01:00:00
2017/01/01 02:00:00
2017/01/01 03:00:00
2017/01/01 04:00:00
2017/01/01 05:00:00

Then simply LEFT JOIN your table with the calendar table and get NULL rows:

    Hours table               Reservations
===================        ===================
2017/01/01 00:00:00        2017/01/01 00:00:00
2017/01/01 01:00:00  LEFT  2017/01/01 01:00:00
2017/01/01 02:00:00  JOIN  NULL                 <-- Free
2017/01/01 03:00:00        2017/01/01 03:00:00
2017/01/01 04:00:00        NULL                 <-- Free
2017/01/01 05:00:00        NULL                 <-- Free
SELECT    hdate 
FROM      calendar
LEFT JOIN reservations
ON        hdate = from_date
WHERE     parking_id is null;

dbfiddle here

  • The reservation has from_date and to_date so it is a range. As far as I can see, your solution only uses from_date. Can we improve this to work on a time range? – Feras Odeh Apr 7 '17 at 19:35
  • You said granularity is one hour, If you want I can add one hour to hdate just to show the range. – McNets Apr 7 '17 at 22:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.