sql - How to return only work time from reservations in PostgreSql?

Question

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 ?

Answer 1

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)