Re: How to find earlest possible start times for given duration excluding reservations - Mailing list pgsql-general

>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;