How to find the first free time in the booking table in PostgreSql

Question

The reservation table contains the start dates of the reservation, the start hours and the duration. The initial hour is in half an hour increments during working hours 8:00 .. 18:00 on working days. Duration also increases by half an hour a 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 be changed if required.

How to find the first free half hour in a table that is not reserved? Eq if the table contains

 startdate starthour duration 14 9 1 -- ends at 9:59 14 10 1.5 -- ends at 11:29, eq there is 30 minute gap before next 14 12 2 14 16 2.5 

the result should be:

 starthour duration 11.5 0.5 

Probably, the PostgreSql 9.2 window function should be used to search for the first line, the beginning of which is longer than the previous line starthour + duration
How to write a select statement that returns this information?

Answer 1

Postgres 9.2 has a range type, and I would recommend using 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 USE gist" creates an index that prevents insertion from overwriting records. You can use the following query to find spaces (vyegorov query variant):

 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 non-working hours, so you can make a reservation only from 8 a.m. to 6 p.m.

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

Answer 2

This may not be the best request, but it does what you want:

 WITH times AS ( SELECT startdate sdate, startdate + (floor(starthour)||'h '|| ((starthour-floor(starthour))*60)||'min')::interval shour, startdate + (floor(starthour)||'h '|| ((starthour-floor(starthour))*60)||'min')::interval + (floor(duration)||'h '|| ((duration-floor(duration))*60)||'min')::interval ehour FROM reservation), gaps AS ( SELECT sdate,shour,ehour,lead(shour,1,ehour) OVER (PARTITION BY sdate ORDER BY shour) - ehour as gap FROM times) SELECT * FROM gaps WHERE gap > '0'::interval; 

Some notes:

• It is better not to allocate time and event data. If you need, use standard types;
• If you cannot go with standard types, create a function to convert the numeric clock to the time format.