How to find first free time in reservations table in PostgreSql

Question

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 ?

Cross-posted to PostgreSQL mailing list here: http://archives.postgresql.org/message-id/C4548EC84E8746E9B4BAA92310F1A4FE@dell2 . Not downvoting because the author did link from the Pg list post to this question. — Craig Ringer, Nov 15 '12 at 01:26

mys · Accepted Answer · 2012-11-15T02:52:18.590

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

Than you very much. This finds gaps in single day only. How to find gaps in any work day (excluding saturdays and sundays and public holidays in holiday table) in work hours 8:00 .. 18:00 ? — Andrus, Nov 16 '12 at 18:20
I posted this as separate question in http://stackoverflow.com/questions/13433863/how-to-exclude-weekends-and-public-holidays-from-schedule-in-postgresql — Andrus, Nov 17 '12 at 19:19
I'm trying to create a same 'gap' for stock orders, perhaps you could have a look? http://stackoverflow.com/questions/34053936/getting-unavailable-dates-for-renting-a-product-that-has-stocks — CularBytes, Dec 03 '15 at 16:01

score 1 · Answer 2 · answered Nov 14 '12 at 21:38

Maybe not the best query, 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 will be better not to separate time and data of the event. If you have to, then use standard types;
If it is not possible to go with standard types, create function to convert numeric hours into the time format.

Agreed - use `TIME` if you must store times separately from dates. Don't use `NUMERIC` for this; as you can see from the above query it makes working with times and intervals convoluted. — Craig Ringer, Nov 14 '12 at 23:26

How to find first free time in reservations table in PostgreSql

2 Answers2

Linked