On 28 October 2014 15:10, Andrus <kobruleht2@xxxxxx> wrote:
Thom
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)
--
