5

Select from great answer in How to find first free time in reservations table in PostgreSql

create table reservation (during tsrange, EXCLUDE USING gist (during WITH &&) );

is used to find gaps in schedule starting at given date and hour (2012-11-17 8: in sample below) It finds saturday, sunday and public holidays also. Public holidays are defined in table

create table pyha ( pyha date primary key)

How to exclude weekends and public holidays also?

Hard-coding free time as reserved to query like

with gaps as ( select upper(during) as start, lead(lower(during),1,upper(during)) over (ORDER BY during) - upper(during) as gap from ( select during from reservation union all values ('(,2012-11-17 8:)'::tsrange), -- given date and hour from which to find free work time ('[2012-11-17 0:,2012-11-18 24:)'::tsrange), -- exclude saturday ('[2012-11-18 0:,2012-11-19 8:)'::tsrange), -- exclude sunday ('[2012-11-19 18:,2012-11-20 8:)'::tsrange), ('[2012-11-20 18:,2012-11-21 8:)'::tsrange), ('[2012-11-21 18:,2012-11-22 8:)'::tsrange), ('[2012-11-22 18:,2012-11-23 8:)'::tsrange), ('[2012-11-23 18:,2012-11-24 24:)'::tsrange), ('[2012-11-24 0:,2012-11-25 24:)'::tsrange), -- exclude saturday ('[2012-11-25 0:,2012-11-26 8:)'::tsrange) -- exclude sunday ) as x ) select * from gaps where gap > '0'::interval order by start

requires separate row in union for every free time range.

Which is best way to return free time in work days and work hours ( 8:00 .. 18:00 ) starting from given date and hour ?

Update

Select in answer returns free time at 8:00 always. How to return free time not before specified start hour in specified start date, for example not before 2012-11-19 9:00 if start hour is 9 ? Start hour may have only values 8,9,10,11,12,13,14,15,16 or 17

Even if 2012-11-19 8:00 if free it should return 2012-11-19 9:00. It should return 8:00 only if there is no free time in 2012-11-19 at 9:00 and 8:00 is first free in succeeding work days.

I tried to fix this by adding 2012-11-19 9: to two places as shown in query below but this query still returns free time at 2012-11-19 8:00. How to fix this so it returns free time at 2012-11-19 9:00 ?

create table reservation (during tsrange, EXCLUDE USING gist (during WITH &&) ); create table pyha ( pyha date primary key); with gaps as ( select upper(during) as start, lead(lower(during),1,upper(during)) over (ORDER BY during) - upper(during) as gap from ( select during from reservation where upper(during)>= '2012-11-19 9:' union all values ('(,2012-11-19 9:)'::tsrange) union all select unnest(case when pyha is not null then array[tsrange(d, d + interval '1 day')] when date_part('dow', d) in (0, 6) then array[tsrange(d, d + interval '1 day')] else array[tsrange(d, d + interval '8 hours'), tsrange(d + interval '18 hours', d + interval '1 day')] end) from generate_series( '2012-11-19'::timestamp without time zone, '2012-11-19'::timestamp without time zone+ interval '3 month', interval '1 day' ) as s(d) left join pyha on pyha = d::date ) as x ) select start, date_part('epoch', gap) / (60*60) as hours from gaps where gap > '0'::interval order by start

Update2

I tried updated answer but it returns wrong data. Complete testcase is:

create temp table reservation ( during tsrange ) on commit drop; insert into reservation values( '[2012-11-19 11:00:00,2012-11-19 11:30:00)'::tsrange ); with gaps as ( select upper(during) as start, lead(lower(during),1,upper(during)) over (ORDER BY during) - upper(during) as gap from ( select during from reservation union all select unnest(case when pyha is not null then array[tsrange(d, d + interval '1 day')] when date_part('dow', d) in (0, 6) then array[tsrange(d, d + interval '1 day')] when d::date = DATE'2012-11-19' then array[ tsrange(d, '2012-11-19 12:'), -- must return starting at 12:00 tsrange(d + interval '18 hours', d + interval '1 day')] else array[tsrange(d, d + interval '8 hours'), tsrange(d + interval '18 hours', d + interval '1 day')] end) from generate_series( DATE'2012-11-19'::timestamp without time zone, DATE'2012-11-19'::timestamp without time zone+ interval '3 month', interval '1 day' ) as s(d) left join pyha on pyha = d::date ) as x ) select start, date_part('epoch', gap) / (60*60) as tunde from gaps where gap > '0'::interval order by start

Observed first row:

"2012-11-19 11:30:00"

Expected :

"2012-11-19 12:00:00"

how to fix ?

Improve this question

1 Answer 1

Reset to default
2

You can use generate_series() function in order to mask-out non business hours:

with gaps as ( select upper(during) as start, lead(lower(during),1,upper(during)) over (ORDER BY during) - upper(during) as gap from ( select during from reservation union all select unnest(case when pyha is not null then array[tsrange(d, d + interval '1 day')] when date_part('dow', d) in (0, 6) then array[tsrange(d, d + interval '1 day')] when d::date = '2012-11-14' then array[tsrange(d, d + interval '9 hours'), tsrange(d + interval '18 hours', d + interval '1 day')] else array[tsrange(d, d + interval '8 hours'), tsrange(d + interval '18 hours', d + interval '1 day')] end) from generate_series( '2012-11-14'::timestamp without time zone, '2012-11-14'::timestamp without time zone + interval '2 week', interval '1 day' ) as s(d) left join pyha on pyha = d::date ) as x ) select * from gaps where gap > '0'::interval order by start

Let me explain some tricky parts:

  • you dont have to insert dates for sat/sun into pyha table because you can use date_part('dow', d) function. Use pyha table for public holidays only. 'dow' returns 0 or 6 for Sun or Sat respectively.
  • public holidays and sat/sun can be represented as single interval (0..24). Weekdays have to be represented by two intervals (0..8) and (18..24) hence unnest() and array[]
  • you can specify start date and length in generate_series() function

Based on your update to the question I added another when to case:

when d::date = '2012-11-14' then array[tsrange(d, d + interval '9 hours'), tsrange(d + interval '18 hours', d + interval '1 day')]

The idea is to produce different interval(s) for starting date (d::date = '2012-11-14'): (0..9) and (18..24)

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you very much. This returns free time at 8:00. How to change so that free time is not returned before specified starting work hour in specified start date ? I tried to add this but failed. I updated question.
Updated select statement returns wrong data. I updated question and added complete testcase to reproduce the issue. It looks like overlapping ranges are present in select which cause invalid result. How to fix ?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.