On 28 October 2014 15:10, Andrus <kobrule...@hot.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