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

On 28 October 2014 15:10, Andrus <kobruleht2(at)hot(dot)ee> wrote:

> Hi!
>
> I'm looking for finding ealiest possible start times from reservations
> table.
>
> People work from 10:00AM to 21:00PM in every week day except Sunday and
> public holidays.
>
> Jobs for them are reserved at 15 minute intervals and whole job must fit
> to single day.
> Job duration is from 15 minutes to 4 hours.
>
> Reservat table contains reservations, yksus2 table contains workes and
> pyha table contains public holidays. Table structures are below. Reservat
> structure can changed if this helps.
>
> How to first earliest 30 possible start times considering existing
> reservations ?
>
> For example, Mary has already reservation at 12:30 .. 16:00 and
> John has already reservation at 12:00 to 13:00
>
> In this case query for job with duration of 1.5 hours should return
>
> John 2014-10-28 10:00
> Mary 2014-10-28 10:00
> 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:30
> ... etc and also starting from next days
>
> I tried query based on answer in http://stackoverflow.com/
> questions/13433863/how-to-return-only-work-time-from-
> reservations-in-postgresql below but it returns wrong 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 sliding start times 10:00, 10:30 etc are not returned.
>
> How to get proper first reservations ?
>
> Query which I tried is
>
> 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
>
>
> Schema:
>
> 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);
>
>
> I posted it also in http://stackoverflow.com/questions/26608683/how-to-
> find-first-free-start-times-from-reservations-in-postgres
>
> Andrus.

Would you be able to adapt this to your needs?:

CREATE TABLE yksus2 (yksus char(10) PRIMARY KEY);
INSERT INTO yksus2 VALUES ('JOHN'),('MARY');

CREATE TABLE reservat
(
id serial primary key,
objekt2 char(10) REFERENCES yksus2 (yksus),
during tstzrange
);

ALTER TABLE reservat ADD CONSTRAINT time_between_1000_and_2100
CHECK (lower(during) >= (lower(during)::date + '10:00'::time)::timestamptz
AND upper(during) < (upper(during)::date + '21:00+1'::time)::timestamptz);

ALTER TABLE reservat ADD CONSTRAINT time_at_15_min_offset
CHECK (extract(epoch from lower(during)::time)::int % (60*15) = 0);

ALTER TABLE reservat ADD CONSTRAINT duration_between_15min_and_4hours
CHECK (upper(during) - lower(during) between '15 mins'::interval and '4
hours'::interval);

INSERT INTO reservat (objekt2, during)
VALUES ('MARY','[2014-10-28 12:30+0,2014-10-28 16:00+0)'::tstzrange);
INSERT INTO reservat (objekt2, during)
VALUES ('JOHN','[2014-10-28 12:00+0,2014-10-28 13:00+0)'::tstzrange);

SELECT yksus2.yksus, times.period
FROM generate_series('2014-10-28 10:00+0'::timestamptz, '2014-10-28
21:00+0', '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 30;

yksus | period
------------+------------------------
JOHN | 2014-10-28 10:00:00+00
MARY | 2014-10-28 10:00:00+00
JOHN | 2014-10-28 10:15:00+00
MARY | 2014-10-28 10:15:00+00
JOHN | 2014-10-28 10:30:00+00
MARY | 2014-10-28 10:30:00+00
MARY | 2014-10-28 10:45:00+00
MARY | 2014-10-28 11:00:00+00
JOHN | 2014-10-28 13:00:00+00
JOHN | 2014-10-28 13:15:00+00
JOHN | 2014-10-28 13:30:00+00
JOHN | 2014-10-28 13:45:00+00
JOHN | 2014-10-28 14:00:00+00
JOHN | 2014-10-28 14:15:00+00
JOHN | 2014-10-28 14:30:00+00
JOHN | 2014-10-28 14:45:00+00
JOHN | 2014-10-28 15:00:00+00
JOHN | 2014-10-28 15:15:00+00
JOHN | 2014-10-28 15:30:00+00
JOHN | 2014-10-28 15:45:00+00
JOHN | 2014-10-28 16:00:00+00
MARY | 2014-10-28 16:00:00+00
JOHN | 2014-10-28 16:15:00+00
MARY | 2014-10-28 16:15:00+00
JOHN | 2014-10-28 16:30:00+00
MARY | 2014-10-28 16:30:00+00
JOHN | 2014-10-28 16:45:00+00
MARY | 2014-10-28 16:45:00+00
JOHN | 2014-10-28 17:00:00+00
MARY | 2014-10-28 17:00:00+00
(30 rows)

--
Thom