|
On 11/14/2012 01:02 PM, Andrus wrote:
I'm not sure if it will work well for your specific use-case and it requires an up-to-date version (9.2+??) but I would recommend investigating range types which have some characteristics that are useful for reservation and calendaring applications including the ability to have a "non-overlapping" constraint that prevents creating a record with a range that overlaps an existing range in the table. Instead of having three columns (startdate, starthour and duration) you would have a single column of type tsrange which includes the starting- and ending-times of each reservation. Here's the info on range types: http://www.postgresql.org/docs/9.2/static/rangetypes.html If you want to limit reservations to start/end at half-hours and/or to certain times of the day you will probably want to include those constraints in your table definition. You asked about finding a free half-hour but since you show durations that exceed a half-hour, you may want to include the capability to search for the first available occurrence of X free-time. Range-types are new and I'm not experienced with them - others may have better ideas - but the method of finding the first occurrence that springs to mind is to make a query that uses generate_series to create a list of "candidate" reservation periods of the desired duration and select the first one that doesn't overlap an existing reservation. This should work fine as long as you are looking a limited time in the future (there are fewer than 20 possible start-times in a day so even looking 100-days ahead is only 2000 candidates) however generating a series of ranges may involve a sub-query - I don't know if you can generate a series of ranges directly. Hope this helps. Cheers, Steve |
