Finding first free time from reservations table

From: "Andrus" <kobruleht2(at)hot(dot)ee>
To: <pgsql-general(at)postgresql(dot)orG>
Subject: Finding first free time from reservations table
Date: 2012-11-14 21:02:33
Message-ID: C4548EC84E8746E9B4BAA92310F1A4FE@dell2
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

http://stackoverflow.com/questions/13387189/how-to-find-first-free-time-in-reservations-table-in-postgresql

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Frank Cavaliero 2012-11-14 21:04:34 Failed Login Attempts parameter
Previous Message Jeff Janes 2012-11-14 19:59:29 Re: How do query optimizers affect window functions