How To Find The First Postgres Free Booking Start Times

Question

People work from 10:00 to 21:00, except Sundays and holidays.

Assignments for them are reserved at 15-minute intervals. Duration of work - from 15 minutes to 4 hours. All work should correspond to one day.

How to find the first coming free start times that are not reserved for a given duration in Postgres 9.3, starting from the current date and time?

For example, Mary already reserves at 12:30 .. 16:00 and John already makes an order at 12:00 to 13:00.

The reservation table contains reservations, the yksus2 table contains works and the Piha table contains holidays. Below are the table structures. The reservation structure may change if this helps.

A request for the oldest startup times of 1.5 hours should return

John 2014-10-28 10:00 Mary 2014-10-28 10:00 John 2014-10-28 10:15 Mary 2014-10-28 10:15 John 2014-10-28 10:30 Mary 2014-10-28 10:30 Mary 2014-10-28 11:00 John 2014-10-28 13:00 Mary 2014-10-28 16:00 Mary 2014-10-28 16:15 Mary 2014-10-28 16:30 ... etc and also starting from next days 

I tried a query based on the answer in How to return only working hours from a reservation in PostgreSql? below, but it returns an incorrect result:

 MARY 2014-10-28 13:00:00 MARY 2014-10-29 22:34:40.850255 JOHN 2014-10-30 22:34:40.850255 MARY 2014-10-31 22:34:40.850255 MARY 2014-11-03 22:34:40.850255 

Also the moving start time is 10:00, 10:30, etc. not returning.
How to get the right first reservations?

A query that returns an invalid result:

 insert into reservat (objekt2, during) values ('MARY', '[2014-10-28 11:30:00,2014-10-28 13:00:00)'), ('JOHN', '[2014-10-28 10:00:00,2014-10-28 11:30:00)'); with gaps as ( select yksus, upper(during) as start, lead(lower(during),1,upper(during)) over (ORDER BY during) - upper(during) as gap from ( select yksus2.yksus, during from reservat join yksus2 on reservat.objekt2=yksus2.yksus where upper(during)>= current_date union all select yksus2.yksus, unnest(case when pyha is not null then array[tsrange1(d, d + interval '1 day')] when date_part('dow', d) in (0, 6) then array[tsrange1(d, d + interval '1 day')] when d::date = current_Date then array[ tsrange1(d, current_timestamp ), tsrange1(d + interval '20 hours', d + interval '1 day')] else array[tsrange1(d, d + interval '8 hours'), tsrange1(d + interval '20 hours', d + interval '1 day')] end) from yksus2, generate_series( current_timestamp, current_timestamp + interval '1 month', interval '1 day' ) as s(d) left join pyha on pyha = d::date ) as x ) select yksus, start from gaps where gap >= interval'1hour 30 minutes' order by start limit 30 

Scheme:

 CREATE EXTENSION btree_gist; CREATE TABLE Reservat ( id serial primary key, objekt2 char(10) not null references yksus2 on update cascade deferrable, during tsrange not null check( lower(during)::date = upper(during)::date and lower(during) between current_date and current_date+ interval'1 month' and (lower(during)::time >= '10:00'::time and upper(during)::time < '21:00'::time) AND EXTRACT(MINUTE FROM lower(during)) IN (0, 15, 30,45) AND EXTRACT(MINUTE FROM upper(during)) IN (0, 15, 30, 45) and (date_part('dow', lower(during)) in (1,2,3,4,5,6) and date_part('dow', upper(during)) in (1,2,3,4,5,6)) ), EXCLUDE USING gist (objekt2 WITH =, during WITH &&) ); create or replace function holiday_check() returns trigger language plpgsql stable as $$ begin if exists (select * from pyha where pyha in (lower(NEW.during)::date, upper(NEW.during)::date)) then raise exception 'public holiday %', lower(NEW.during) ; else return NEW; end if; end; $$; create trigger holiday_check_i before insert or update on Reservat for each row execute procedure holiday_check(); CREATE OR REPLACE FUNCTION public.tsrange1(start timestamp with time zone, finish timestamp with time zone ) RETURNS tsrange AS $BODY$ SELECT tsrange(start::timestamp without time zone, finish::timestamp without time zone ); $BODY$ language sql immutable; -- Workers create table yksus2( yksus char(10) primary key); insert into yksus2 values ('JOHN'), ('MARY'); -- public holidays create table pyha( pyha date primary key); 

Also posted to the pgsql-general mailing list .

Answer 1

Adapted circuit

 CREATE EXTENSION btree_gist; CREATE TYPE timerange AS RANGE (subtype = time); -- create type once -- Workers CREATE TABLE worker( worker_id serial PRIMARY KEY , worker text NOT NULL ); INSERT INTO worker(worker) VALUES ('JOHN'), ('MARY'); -- Holidays CREATE TABLE pyha(pyha date PRIMARY KEY); -- Reservations CREATE TABLE reservat ( reservat_id serial PRIMARY KEY , worker_id int NOT NULL REFERENCES worker ON UPDATE CASCADE , day date NOT NULL CHECK (EXTRACT('isodow' FROM day) < 7) , work_from time NOT NULL -- including lower bound , work_to time NOT NULL -- excluding upper bound , CHECK (work_from >= '10:00' AND work_to <= '21:00' AND work_to - work_from BETWEEN interval '15 min' AND interval '4 h' AND EXTRACT('minute' FROM work_from) IN (0, 15, 30, 45) AND EXTRACT('minute' FROM work_from) IN (0, 15, 30, 45) ) , EXCLUDE USING gist (worker_id WITH =, day WITH = , timerange(work_from, work_to) WITH &&) ); INSERT INTO reservat (worker_id, day, work_from, work_to) VALUES (1, '2014-10-28', '10:00', '11:30') -- JOHN , (2, '2014-10-28', '11:30', '13:00'); -- MARY -- Trigger for volatile checks CREATE OR REPLACE FUNCTION holiday_check() RETURNS trigger AS $func$ BEGIN IF EXISTS (SELECT 1 FROM pyha WHERE pyha = NEW.day) THEN RAISE EXCEPTION 'public holiday: %', NEW.day; ELSIF NEW.day < now()::date OR NEW.day > now()::date + 31 THEN RAISE EXCEPTION 'day out of range: %', NEW.day; END IF; RETURN NEW; END $func$ LANGUAGE plpgsql STABLE; -- can be "STABLE" CREATE TRIGGER insupbef_holiday_check BEFORE INSERT OR UPDATE ON reservat FOR EACH ROW EXECUTE PROCEDURE holiday_check(); 

Basic moments

• Do not use char(n) . Rather, varchar(n) , or better yet, varchar or just text .

  • Any disadvantages of using the "text" data type to store strings?

• Do not use the worker name as the primary key. This is not necessarily unique and may change. Instead, use a surrogate primary key, preferably a serial . Also makes reservat entries smaller, indexes smaller, queries faster, ...

• Update:. For cheaper storage (8 bytes instead of 22) and simpler processing, I save the beginning and end as time now and build the range on the fly to limit the exception:

   EXCLUDE USING gist (worker_id WITH =, day WITH = , timerange(work_from, work_to) WITH &&) 

• Since your ranges may never cross the date boundary by definition, it would be more efficient to have a separate date column ( day in my implementation) and a time range . The timerange type timerange not supplied in the default settings, but is easily created. In this way, you can greatly simplify your control restrictions.

• Use EXTRACT('isodow', ...) to simplify Sunday exception

  Day of the week like Monday (1) to Sunday (7)

• I assume you want to allow the upper bound of '21: 00 '.

• Boundaries are considered included for the lower and excluding for the upper boundary.

• Checking that new / updated days lie within a month from "now" is not IMMUTABLE . Moved it from the CHECK restriction to the trigger - otherwise you may encounter problems with a dump / restore! Details:

  • Disable all restrictions and table checks when restoring a dump

Besides
In addition to simplifying input and verification restrictions, I expected timerange to save 8 bytes of memory compared to tsrange , since time takes up only 4 bytes. But it turns out that timerange takes 22 bytes on disk (25 in RAM), just like tsrange (or tstzrange ). So you can go with tsrange . The principle of limiting queries and exceptions is the same.

Query

SQL function is included in convenient parameter management:

 CREATE OR REPLACE FUNCTION f_next_free(_start timestamp, _duration interval) RETURNS TABLE (worker_id int, worker text, day date , start_time time, end_time time) AS $func$ SELECT w.worker_id, w.worker , dd AS day , tt AS start_time ,(tt + _duration) AS end_time FROM ( SELECT _start::date + i AS d FROM generate_series(0, 31) i LEFT JOIN pyha p ON p.pyha = _start::date + i WHERE p.pyha IS NULL -- eliminate holidays ) d CROSS JOIN ( SELECT t::time FROM generate_series (timestamp '2000-1-1 10:00' , timestamp '2000-1-1 21:00' - _duration , interval '15 min') t ) t -- times CROSS JOIN worker w WHERE dd + tt > _start -- rule out past timestamps AND NOT EXISTS ( SELECT 1 FROM reservat r WHERE r.worker_id = w.worker_id AND r.day = dd AND timerange(r.work_from, r.work_to) && timerange(tt, tt + _duration) ) ORDER BY dd, tt, w.worker, w.worker_id LIMIT 30 -- could also be parameterized $func$ LANGUAGE sql STABLE; 

Call:

 SELECT * FROM f_next_free('2014-10-28 12:00'::timestamp, '1.5 h'::interval); 

SQL Fiddle in Postgres 9.3 now.

To explain

• The function takes _start timestamp as the minimum start time and _duration interval . Be careful to exclude only previous times on the start day, not next days. The easiest way is simply adding the day and time: t + d > _start .
  To book a reservation starting "now", just go now()::timestamp :

   SELECT * FROM f_next_free(`now()::timestamp`, '1.5 h'::interval); 

• Subquery d generates days starting with the input value _day . Holidays are excluded.

• Days intersect with possible time ranges generated in subquery t .
• This cross-connects with all available workers w .
• Finally, eliminate all candidates that encounter existing reservations using the anti-semi-join NOT EXISTS and, in particular, the && overlap operator.

on this topic:

• How do you do math with a date that ignores a year? (for a mathematical example of a date)
• Prevent contiguous / overlapping records with EXCLUDE in PostgreSQL
• Calculate working hours between two dates in PostgreSQL

Answer 2

Tom Brown on the psql-general mailing list recommends the following solution.

This is more readable, but Erwin's answer looks more optimized. I have 10 works and 1 month of reservation with a 15-minute discount from 8 to 20:00, so the performance is not good and will not be released. What to use?

Which solution is better?

 create table pyha (pyha date primary key); insert into pyha(pyha) values('2014-10-29'); create table yksus2(yksus char(10) primary key); insert into yksus2 values ('JOHN'),('MARY'); CREATE EXTENSION btree_gist; CREATE TABLE reservat ( reservat_id serial primary key, objekt2 char(10) not null references yksus2 on update cascade deferrable, during tstzrange not null, EXCLUDE USING gist (objekt2 WITH =, during WITH &&), CONSTRAINT same_date CHECK (lower(during)::date = upper(during)::date), CONSTRAINT max_1month_future CHECK (lower(during) between current_date and current_date+ interval'1 month' ), CONSTRAINT time_between_1000_and_2100 CHECK (lower(during)::time >= '10:00'::time and upper(during)::time < '21:00'::time), CONSTRAINT lower_bound_included CHECK (lower_inc(during)), CONSTRAINT upper_bound_excluded CHECK (not upper_inc(during)), CONSTRAINT start_time_at_15minute_offset CHECK (EXTRACT(MINUTE FROM lower(during)) IN (0, 15, 30,45)), -- or (extract(epoch from lower(during)::time)::int % (60*15) = 0) CONSTRAINT end_time_at_15minute_offset CHECK (EXTRACT(MINUTE FROM upper(during)) IN (0, 15, 30,45)), CONSTRAINT duration_between_15min_and_4hours CHECK (upper(during) - lower(during) between '15 mins'::interval and '4 hours'::interval), CONSTRAINT exclude_sundays CHECK (date_part('dow', lower(during)) in (1,2,3,4,5,6) ) ); create or replace function holiday_check() returns trigger language plpgsql stable as $$ begin if exists (select * from pyha where pyha between lower(NEW.during)::date and upper(NEW.during)::date) then raise exception 'public holiday %', lower(NEW.during) ; else return NEW; end if; end; $$; create trigger holiday_check_i before insert or update on Reservat for each row execute procedure holiday_check(); INSERT INTO reservat (objekt2, during) VALUES ('MARY','[2014-10-29 11:30+2,2014-10-29 13:00+2)'::tstzrange); INSERT INTO reservat (objekt2, during) VALUES ('JOHN','[2014-10-29 10:00+2,2014-10-29 11:30+2)'::tstzrange); SELECT yksus2.yksus, times.period FROM generate_series(now()::date::timestamptz, now()::date::timestamptz + '3 months'::interval, '15 mins'::interval) times(period) CROSS JOIN yksus2 LEFT JOIN reservat ON tstzrange(times.period,times.period + '1 hour 30 mins'::interval, '[)') && reservat.during AND yksus2.yksus = reservat.objekt2 LEFT JOIN pyha ON times.period::date = pyha.pyha::date WHERE reservat.during IS NULL AND pyha.pyha IS NULL AND times.period::timetz BETWEEN '10:00'::timetz AND '21:00'::timetz - '1 hour 30 mins'::interval AND times.period >= now() AND EXTRACT(isoDOW FROM times.period) != 7 -- exclude sundays ORDER BY 2, 1 LIMIT 300;