Обсуждение: Finding first free time from reservations table

"Andrus" <kobruleht2@hot.ee> writes:

> How to find first free half hour in table which is not reserved ?
>
> E.q if table contains
>
>  startdate   starthour  duration
>  14          9           1              -- ends at 9:59
>  14          10          1.5            -- ends at 11:29, e.q there is
> 30 minute gap before next
>  14          12          2
>  14          16          2
>
> result should be:
>
>  starthour  duration
>  11.5       0.5
>
>
> Probably PostgreSql 9.2 window function should used to find
> first row whose starthour is greater than previous row starthour +
> duration

Yes, you could use something like this:

SELECT min(c1)
FROM (
    SELECT starthour + duration AS c1,
           lead(starthour) OVER (ORDER BY starthour) AS c2
    FROM tst
  ) dummy
WHERE c2 >= c1 + 0.5

I’m looking for a way to find first free time in reservations table.

 

Reservation table contains reservations start dates, start hours and durations.

Start hour is by half hour increments in working hours 8:00 .. 18:00 in work days.

Duration is also by half hour increments in day.

 

    CREATE TABLE reservation (

      id serial primary key,

      startdate date not null,  -- start date

      starthour numeric(4,1) not null , -- start hour 8 8.5 9 9.5  ..  16.5 17 17.5

      duration  Numeric(3,1) not null -- duration by hours 0.5 1 1.5 .. 9 9.5 10

    );

 

table structure can changed if required.

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