Re: How to find earlest possible start times for given duration excluding reservations

Hi!

>Would you be able to adapt this to your needs?:
Thank you very much. Great solution.
I refactored it as shown below.
Query returns only dates for single day. Changing limit clause to 300 does not return next day.
How to return other day dates also, excluding sundays and public holidays in pyha table ?
Andrus.
Testcase is:
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-28 11:30+2,2014-10-28 13:00+2)'::tstzrange);
INSERT INTO reservat (objekt2, during)
VALUES ('JOHN','[2014-10-28 10:00+2,2014-10-28 11:30+2)'::tstzrange);

SELECT yksus2.yksus, times.period
FROM generate_series('2014-10-28 10:00+2'::timestamptz, '2014-10-28 21:00+2', '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
WHERE reservat.during IS NULL
ORDER BY 2, 1
LIMIT 300;