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 (
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
primary key (startdate, starthour)
);
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.5
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 ?
Postgres 9.2 has range type and I would recommend to use them.
"EXCLUDE USING gist" creates index which disallows to inset overlapping entries. You can use the following query to find gaps (variant of vyegorov's query):
'union all values' masks out non working times hence you can make reservation between 8am and 18pm only.
Here is the result:
Documentation links: - http://www.postgresql.org/docs/9.2/static/rangetypes.html "Range Types" - https://wiki.postgresql.org/images/7/73/Range-types-pgopen-2012.pdf
Maybe not the best query, but it does what you want:
Some notes:
numeric
hours into thetime
format.