How to find first free time in reservations table in PostgreSql
postgresql, postgresql-9.2, schedule, sql
Solution
Postgres 9.2 has range type and I would recommend to use them.
create table reservation (reservation tsrange);
insert into reservation values
('[2012-11-14 09:00:00,2012-11-14 10:00:00)'),
('[2012-11-14 10:00:00,2012-11-14 11:30:00)'),
('[2012-11-14 12:00:00,2012-11-14 14:00:00)'),
('[2012-11-14 16:00:00,2012-11-14 18:30:00)');
ALTER TABLE reservation ADD EXCLUDE USING gist (reservation WITH &&);
"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):
with gaps as (
select
upper(reservation) as start,
lead(lower(reservation),1,upper(reservation)) over (ORDER BY reservation) - upper(reservation) as gap
from (
select *
from reservation
union all values
('[2012-11-14 00:00:00, 2012-11-14 08:00:00)'::tsrange),
('[2012-11-14 18:00:00, 2012-11-15 00:00:00)'::tsrange)
) as x
)
select * from gaps where gap > '0'::interval;
'union all values' masks out non working times hence you can make reservation between 8am and 18pm only.
Here is the result:
start | gap
---------------------+----------
2012-11-14 08:00:00 | 01:00:00
2012-11-14 11:30:00 | 00:30:00
2012-11-14 14:00:00 | 02:00:00
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
Problem
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 ?