4

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 ?

Improve this question
1
  • Cross-posted to PostgreSQL mailing list here: archives.postgresql.org/message-id/… . Not downvoting because the author did link from the Pg list post to this question. Commented Nov 15, 2012 at 1:26

2 Answers 2

Reset to default
9

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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

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 ?
I posted this as separate question in stackoverflow.com/questions/13433863/…
I'm trying to create a same 'gap' for stock orders, perhaps you could have a look? stackoverflow.com/questions/34053936/…
1

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:

  1. It will be better not to separate time and data of the event. If you have to, then use standard types;
  2. If it is not possible to go with standard types, create function to convert numeric hours into the time format.
Improve this answer

1 Comment

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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.