Finding first free time from reservations table

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.

 

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 

How to write select statement which returns this information ?

 

Andrus.

 

I posted this also in