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

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.