title: How to return only work time from reservations in PostgreSql?
body: <p>Select from great answer in <a href="https://stackoverflow.com/questions/13387189/how-to-find-first-free-time-in-reservations-table-in-postgresql">How to find first free time in reservations table in PostgreSql</a></p>
  
  <pre><code>create table reservation (during tsrange,
   EXCLUDE USING gist (during WITH &amp;&amp;)
   );
  </code></pre>
  
  <p>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</p>
  
  <pre><code>create table pyha ( pyha date primary key)
  </code></pre>
  
  <p>How to exclude weekends and public holidays also?</p>
  
  <p>Hard-coding free time as reserved to query like</p>
  
  <pre><code>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 &gt; '0'::interval
  order by start
  </code></pre>
  
  <p>requires separate row in union for every free time range.</p>
  
  <p>Which is best way to return free time in work days and work hours ( 8:00 .. 18:00 ) starting from given date and hour ?</p>
  
  <p><strong>Update</strong></p>
  
  <p>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</p>
  
  <p>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.</p>
  
  <p>I tried to fix this by adding <strong><em>2012-11-19 9:</em></strong> 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 ?</p>
  
  <pre><code>create table reservation (during tsrange,
   EXCLUDE USING gist (during WITH &amp;&amp;)
   );
  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)&gt;= '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 &gt; '0'::interval
  order by start
  </code></pre>
  
  <p><strong>Update2</strong></p>
  
  <p>I tried updated answer but it returns wrong data. Complete testcase is:</p>
  
  <pre><code>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 &gt; '0'::interval
  order by start
  </code></pre>
  
  <p>Observed first row:</p>
  
  <pre><code>"2012-11-19 11:30:00"
  </code></pre>
  
  <p>Expected :</p>
  
  <pre><code>"2012-11-19 12:00:00"
  </code></pre>
  
  <p>how to fix ?</p>