Pull to refresh

Comments 148

1. Перечисление праздников в WITH входит в понятие одного запроса?
2. Рабочий день 9 или 8 часов?
3. Учитывать предпраздничные укороченные дни?
Что-то мне подсказывает:
1) Скорее всего вылеты по праздникам и рабочим в выходные находятся в отдельной таблице и эти данные входят в рамки одного запроса.
2) Если в теле речь про SLA — скорее всего без обеда. Т.е. 9 часов.
3) Явно указано, что предпраздничные обычные.
2. Учитывая, что стандартный рабочий день — это 8 часов, то, скорее всего, второе.

3. В задании же написано
Укороченность предпраздничных дней учитывать не надо, считаем их полными.


Мне кажется, что удобнее задать праздники именно в CTE.
По остальным вопросам все ответы есть в условии: рабочее время с 10:00 до 19:00, что составляет девять часов; предпраздничный день является полным.
Ваш же ответ некорректен Вами же поставленным условиям.
Заявка 2:
Если условие, что работа строго с 10:00 до 19:00, то закрытие заявки не может быть в 20:00 по определению. Но ответ — 9 часов, что соответствует 19:00. Но если верно что заявка закрыта в 20:00, значит человек переработал, и заявка выполнялась 10 часов, но тогда опять же по условиям она должна быть закрыта на следующий день 2019-04-11 в 11:00.
Если условие, что работа строго с 10:00 до 19:00, то закрытие заявки не может быть в 20:00 по определению

Почему не может? Никаким условиям не противоречит. Условия говорят, что нужно посчитать, сколько часов из интервала были рабочими.
Кто закрыл заявку? Если в 19:00 все ушли.
Чтобы не вступать в длительную и бесплодную дискуссию, сам пользователь закрыл. (:
Решение в лоб
with periods(id, start_time, stop_time) as (
    values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
           (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
           (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:00:00'::timestamp),
           (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
     holidays (h_date) as (
         values ('2018-01-01'),
                ('2018-01-02'),
                ('2018-01-03'),
                ('2018-01-04'),
                ('2018-01-05'),
                ('2018-01-07'),
                ('2018-01-08'),
                ('2018-02-23'),
                ('2018-03-08'),
                ('2018-05-01'),
                ('2018-05-09'),
                ('2018-06-12'),
                ('2018-11-04'),
                ('2019-01-01'),
                ('2019-01-02'),
                ('2019-01-03'),
                ('2019-01-04'),
                ('2019-01-05'),
                ('2019-01-07'),
                ('2019-01-08'),
                ('2019-02-23'),
                ('2019-03-08'),
                ('2019-05-01'),
                ('2019-05-09'),
                ('2019-06-12'),
                ('2019-11-04')
     ),
     magic as (
         select id,
                start_time,
                stop_time,
                dd
         from periods,
              generate_series(start_time, stop_time - interval '1 hour', '1 hour') dd
         where extract(dow from dd) between 1 and 5
           and extract(hours from dd) >= 10
           and extract(hours from dd) < 19
           and not exists(select 0 from holidays where h_date::date = dd::date))
select id, start_time, stop_time, count(1) as work_hrs
from magic
group by id, start_time, stop_time
order by id
;

Идея с разбивкой по часам понравилась, получилось очень компактно, но потерялись минуты и секунды.
Добавил еще шаманства для минут и секунд.

Еще больше загадочности
with periods(id, start_time, stop_time) as (
    values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
           (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
           (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
           (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
     holidays (h_date) as (
         values ('2018-01-01'),
                ('2018-01-02'),
                ('2018-01-03'),
                ('2018-01-04'),
                ('2018-01-05'),
                ('2018-01-07'),
                ('2018-01-08'),
                ('2018-02-23'),
                ('2018-03-08'),
                ('2018-05-01'),
                ('2018-05-09'),
                ('2018-06-12'),
                ('2018-11-04'),
                ('2019-01-01'),
                ('2019-01-02'),
                ('2019-01-03'),
                ('2019-01-04'),
                ('2019-01-05'),
                ('2019-01-07'),
                ('2019-01-08'),
                ('2019-02-23'),
                ('2019-03-08'),
                ('2019-05-01'),
                ('2019-05-09'),
                ('2019-06-12'),
                ('2019-11-04')
     ),
     magic as (
         select id,
                start_time,
                stop_time,
                dd,
                case
                    when dd = last_value(dd) over (partition by id, start_time, stop_time) and
                         extract(hours from stop_time) between 10 and 19
                        then stop_time - last_value(dd) over (partition by id, start_time, stop_time)
                    else interval '1 hour' end as w_hours
         from periods,
              generate_series(start_time, stop_time, '1 hour') dd
         where extract(dow from dd) between 1 and 5
           and extract(hours from dd) >= 10
           and extract(hours from dd) < 19
           and not exists(select 0 from holidays where h_date::date = dd::date)
     )
select id, start_time, stop_time, to_char(sum(w_hours), 'HH24:MI:SS') as work_hrs
from magic
group by id, start_time, stop_time
order by id;

Хорошая боевая магия, но пока колдунства недостаточно. Попробуйте IDDQD.
Почему недостаточно?) Результат получен, в логике расхождений с истиной особо не вижу, что не так?
Я боюсь своими комментариями дать Вам слишком много подсказок, поэтому приходится быть не слишком многословным. На моей тестовой выборке расхождения есть.
Вот так вот должно быть хорошо.

Просветление достигнуто
with periods(id, start_time, stop_time) as (
    values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
           (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
           (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
           (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
     holidays (h_date) as (
         values ('2018-01-01'),
                ('2018-01-02'),
                ('2018-01-03'),
                ('2018-01-04'),
                ('2018-01-05'),
                ('2018-01-07'),
                ('2018-01-08'),
                ('2018-02-23'),
                ('2018-03-08'),
                ('2018-05-01'),
                ('2018-05-09'),
                ('2018-06-12'),
                ('2018-11-04'),
                ('2019-01-01'),
                ('2019-01-02'),
                ('2019-01-03'),
                ('2019-01-04'),
                ('2019-01-05'),
                ('2019-01-07'),
                ('2019-01-08'),
                ('2019-02-23'),
                ('2019-03-08'),
                ('2019-05-01'),
                ('2019-05-09'),
                ('2019-06-12'),
                ('2019-11-04')
     ),
     magic as (
         select id,
                to_char(start_time, 'yyyy-mm-dd HH24:MI:SS') as start_time,
                to_char(stop_time, 'yyyy-mm-dd HH24:MI:SS')  as stop_time,
                dd,
                case
                    when dd = last_value(dd) over (partition by id, start_time, stop_time) and
                         extract(hours from stop_time) between 10 and 18
                        then stop_time - last_value(dd) over (partition by id, start_time, stop_time)
                    else interval '1 hour' end               as w_hours
         from periods,
              generate_series(start_time, stop_time, '1 hour') dd
         where extract(dow from dd) between 1 and 5
           and extract(hours from dd) >= 10
           and extract(hours from dd) < 19
           and not exists(select 0 from holidays where h_date::date = dd::date)
     )
select id, start_time, stop_time, to_char(sum(w_hours), 'HH24:MI:SS') as work_hrs
from magic
group by id, start_time, stop_time
order by id;

Не вижу существенной разницы в результатах от предыдущей попытки. Не в том направлении усложняете.
В очередной раз понял, нашел и исправил.

n + 1
with periods(id, start_time, stop_time) as (
    values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
           (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
           (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
           (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
     holidays (h_date) as (
         values ('2018-01-01'),
                ('2018-01-02'),
                ('2018-01-03'),
                ('2018-01-04'),
                ('2018-01-05'),
                ('2018-01-07'),
                ('2018-01-08'),
                ('2018-02-23'),
                ('2018-03-08'),
                ('2018-05-01'),
                ('2018-05-09'),
                ('2018-06-12'),
                ('2018-11-04'),
                ('2019-01-01'),
                ('2019-01-02'),
                ('2019-01-03'),
                ('2019-01-04'),
                ('2019-01-05'),
                ('2019-01-07'),
                ('2019-01-08'),
                ('2019-02-23'),
                ('2019-03-08'),
                ('2019-05-01'),
                ('2019-05-09'),
                ('2019-06-12'),
                ('2019-11-04')
     ),
     magic as (
         select id,
                to_char(start_time, 'yyyy-mm-dd HH24:MI:SS') as start_time,
                to_char(stop_time, 'yyyy-mm-dd HH24:MI:SS')  as stop_time,
                dd,
                first_value(dd) over (partition by id, start_time, stop_time),
                last_value(dd) over (partition by id, start_time, stop_time),
                case
                    when dd = last_value(dd) over (partition by id, start_time, stop_time) and
                         extract(hours from stop_time) between 10 and 18
                        then stop_time - last_value(dd) over (partition by id, start_time, stop_time)
                    when dd = first_value(dd) over (partition by id, start_time, stop_time) and
                         extract(hours from start_time) between 10 and 18
                        then first_value(dd) over (partition by id, start_time, stop_time) + '1 hour' - start_time
                    else interval '1 hour' end               as w_hours
         from periods,
              generate_series(date_trunc('hour', start_time), date_trunc('hour', stop_time), '1 hour') dd
         where extract(dow from dd) between 1 and 5
           and extract(hours from dd) >= 10
           and extract(hours from dd) < 19
           and not exists(select 0 from holidays where h_date::date = dd::date)
     )
select id, start_time, stop_time, to_char(sum(w_hours), 'HH24:MI:SS') as work_hrs
from magic
group by id, start_time, stop_time
order by id;

Потерялись периоды с пустым рабочим временем, нет учёта дополнительных рабочих дней. И что-то как-то распухла магия-то. (:
Ну и дабы уже закрыть гештальт и перестать себя чувствовать как провинившийся студент…

finally
with periods(id, start_time, stop_time) as (
    values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
           (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
           (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
           (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
     holidays (h_date) as (
         values ('2018-01-01'),
                ('2018-01-02'),
                ('2018-01-03'),
                ('2018-01-04'),
                ('2018-01-05'),
                ('2018-01-07'),
                ('2018-01-08'),
                ('2018-02-23'),
                ('2018-03-08'),
                ('2018-05-01'),
                ('2018-05-09'),
                ('2018-06-12'),
                ('2018-11-04'),
                ('2018-12-31'),
                ('2019-01-01'),
                ('2019-01-02'),
                ('2019-01-03'),
                ('2019-01-04'),
                ('2019-01-05'),
                ('2019-01-07'),
                ('2019-01-08'),
                ('2019-02-23'),
                ('2019-03-08'),
                ('2019-05-01'),
                ('2019-05-09'),
                ('2019-06-12'),
                ('2019-11-04')
     ),
     subst_days (s_date) as (
         values ('2018-04-28'),
                ('2018-06-09'),
                ('2018-12-29')
     ),
     magic as (
         select id,
                to_char(start_time, 'yyyy-mm-dd HH24:MI:SS') as start_time,
                to_char(stop_time, 'yyyy-mm-dd HH24:MI:SS')  as stop_time,
                case
                    when dd = last_value(dd) over (partition by id, start_time, stop_time) and
                         extract(hours from stop_time) between 10 and 18
                        then stop_time - last_value(dd) over (partition by id, start_time, stop_time)
                    when dd = first_value(dd) over (partition by id, start_time, stop_time) and
                         extract(hours from start_time) between 10 and 18
                        then first_value(dd) over (partition by id, start_time, stop_time) + '1 hour' - start_time
                    when extract(hours from dd) < 10
                        or extract(hours from dd) >= 19 then interval '0 hours'
                    else interval '1 hour' end               as w_hours
         from periods,
              generate_series(date_trunc('hour', start_time), date_trunc('hour', stop_time), '1 hour') dd
         where (exists(select 1 from subst_days where dd::date = s_date::date) or extract(dow from dd) between 1 and 5)
           and not exists(select 0
                          from holidays
                          where h_date::date = dd::date)
     )
select id, start_time, stop_time, to_char(sum(w_hours), 'HH24:MI:SS') as work_hrs
from magic
group by id, start_time, stop_time
order by id;

Не, не выходит каменный цветок. На вот таких периодах некорректно считает:
('2018-06-10 18:46:10', '2018-06-13 10:18:18'),
('2019-04-28 21:00:00', '2019-04-28 21:00:00').
На первом неверное значение (18:32:08 вместо 18:18), второй совсем теряет.

PS некритично, но всё же выходные в holidays — неправильные данные, я у себя для тестов исправлял.
Какое-то ну очень вымученное решение, потерявшее всю легкость, непринужденность и элегантность.

...
with periods(id, start_time, stop_time) as (
    values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
           (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
           (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
           (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp),
           (5, '2018-06-10 18:46:10'::timestamp, '2018-06-13 10:18:18'::timestamp),
           (6, '2019-04-28 21:00:00'::timestamp, '2019-04-28 21:00:00'::timestamp)
),
     holidays (h_date) as (
         values ('2018-01-01'),
                ('2018-01-02'),
                ('2018-01-03'),
                ('2018-01-04'),
                ('2018-01-05'),
                ('2018-01-07'),
                ('2018-01-08'),
                ('2018-02-23'),
                ('2018-03-08'),
                ('2018-05-01'),
                ('2018-05-09'),
                ('2018-06-12'),
                ('2018-11-04'),
                ('2018-12-31'),
                ('2019-01-01'),
                ('2019-01-02'),
                ('2019-01-03'),
                ('2019-01-04'),
                ('2019-01-05'),
                ('2019-01-07'),
                ('2019-01-08'),
                ('2019-02-23'),
                ('2019-03-08'),
                ('2019-05-01'),
                ('2019-05-09'),
                ('2019-06-12'),
                ('2019-11-04')
     ),
     subst_days (s_date) as (
         values ('2018-04-28'),
                ('2018-06-09'),
                ('2018-12-29')
     ),
     magic as (
         select id,
                to_char(start_time, 'yyyy-mm-dd HH24:MI:SS') as start_time,
                to_char(stop_time, 'yyyy-mm-dd HH24:MI:SS')  as stop_time,
                case
                    when dd = last_value(dd) over (partition by id, start_time, stop_time) and
                         extract(hours from stop_time) between 10 and 18
                        then stop_time - last_value(dd) over (partition by id, start_time, stop_time)
                    when extract(hours from dd) < 10
                        or extract(hours from dd) >= 19 then interval '0 hours'
                    when dd = first_value(dd) over (partition by id, start_time, stop_time) and
                         extract(hours from start_time) between 10 and 18
                        then first_value(dd) over (partition by id, start_time, stop_time) + '1 hour' - start_time
                    else interval '1 hour' end               as w_hours
         from periods,
              generate_series(date_trunc('hour', start_time), date_trunc('hour', stop_time), '1 hour') dd
         where (exists(select 1 from subst_days where dd::date = s_date::date) or extract(dow from dd) between 1 and 5)
           and not exists(select 0
                          from holidays
                          where h_date::date = dd::date)
         union all
         select id,
                to_char(start_time, 'yyyy-mm-dd HH24:MI:SS') as start_time,
                to_char(stop_time, 'yyyy-mm-dd HH24:MI:SS')  as stop_time,
                interval '0 hours'
         from periods,
              generate_series(date_trunc('hour', start_time), date_trunc('hour', stop_time), '1 hour') dd
         where not exists(select 1
                          from subst_days
                          where dd::date = s_date::date
                             or extract(dow from dd) between 6 and 7
                             or exists(select 0
                                       from holidays
                                       where h_date::date = dd::date)
             ))
select id, start_time, stop_time, to_char(sum(w_hours), 'HH24:MI:SS') as work_hrs
from magic
group by id, start_time, stop_time
order by id;

Опять не совсем то. Период ('2018-04-28 18:00:01', '2018-04-28 18:05:00') даёт длительность '00:05:00', а нужно на секунду меньше.

Наверное бросайте развивать этот подход, явно что-то пошло не туда, не надо насилия над собой.
Скрытый текст
with periods (id,start_time, stop_time) as(
  VALUES(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
        (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
        (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:00:00'::timestamp),
        (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
minimax AS (select min(start_time),max(stop_time) FROM periods),
days AS(SELECT gs.d,CASE WHEN extract(isodow from gs.d) IN (6,7) THEN  coalesce(holy.iswrk,false) ELSE coalesce(holy.iswrk,true) END iswrk
FROM minimax
JOIN LATERAL  generate_series(min::date,max::date,'24:00') gs(d) ON 1=1
left join (values ('2019-01-01'::date,false),('2019-01-02',false),('2019-01-03',false),('2019-01-04',false),('2019-01-07',false),('2019-01-08',false),('2019-03-08',false),
('2019-05-01',false),('2019-05-02',false),('2019-05-03',false),('2019-05-09',false),('2019-05-10',false),('2019-06-12',false),('2019-11-04',false),
('2018-01-01',false),('2018-01-02',false),('2018-01-03',false),('2018-01-04',false),('2018-01-05',false),('2018-01-08',false),('2018-02-23',false),
('2018-03-08',false),('2018-03-09',false),('2018-04-28',true),('2018-04-30',false),('2018-05-01',false),('2018-05-02',false),('2018-05-09',false),
('2018-06-09',true),('2018-06-11',false),('2018-06-12',false),('2018-11-05',false),('2018-12-29',true),('2018-12-31',false)) as holy(d,iswrk)
on gs.d=holy.d)

SELECT id,start_time,stop_time, (count(*)-2)*9+
  CASE when extract(hour from start_time)>=19 THEN 0
       when extract(hour from start_time)<=10 THEN 9
       ELSE 19-extract(hour from start_time)
  END+
  CASE WHEN extract(hour from stop_time)>=19 THEN 9
       WHEN extract(hour from stop_time)<=10 THEN 0
       ELSE extract(hour from stop_time)-10
  END
FROM periods JOIN days 
ON 
  d>=start_time::date
  AND d<=stop_time::date
  AND iswrk
GROUP BY id,start_time,stop_time
ORDER BY id

Учел заявки, которые сами закрылись в нерабочее время
 SELECT id,start_time,stop_time, (count(d))*9
  -CASE
       WHEN count(d)=0 OR start_time::date<min(d) THEN 0
       when extract(hour from start_time)>=19 THEN 9
       when extract(hour from start_time)<=10 THEN 0
       ELSE extract(hour from start_time)-10
  END
  -CASE WHEN count(d)=0 OR stop_time::date>max(d) THEN 0 
       WHEN extract(hour from stop_time)>=19 THEN 0
       WHEN extract(hour from stop_time)<=10 THEN 9
       ELSE 19-extract(hour from stop_time)
  END
FROM periods LEFT JOIN days 
ON 
  d>=start_time::date
  AND d<=stop_time::date
  AND iswrk
GROUP BY id,start_time,stop_time
ORDER BY id


Да, так лучше.
минуты и секунды
SELECT id,start_time,stop_time, (count(d))*interval '9 hour'
  -CASE
       WHEN count(d)=0 OR start_time::date<min(d) THEN interval '0 hour'
       when start_time - min(d)>=interval '19 hour' THEN interval '9 hour'
       when  start_time-min(d)<=interval '10 hour' THEN interval '0 hour'
       ELSE start_time -min(d) - interval '10 hour'
  END
  -CASE WHEN count(d)=0 OR stop_time::date>max(d) THEN interval '0 hour' 
       WHEN  stop_time - max(d)>=interval '19 hour' THEN interval '0 hour'
       WHEN stop_time - max(d)<=interval '10 hour' THEN interval '9 hour'
       ELSE  interval '19 hour' - ( stop_time - max(d))
  END
FROM periods LEFT JOIN days 
ON 
  d>=start_time::date
  AND d<=stop_time::date
  AND iswrk
GROUP BY id,start_time,stop_time
ORDER BY id 

Проверил скрипт на большом объеме данных и понял, что не правильно использую LATERAL
Так будет быстрее
with periods(id, start_time, stop_time) as (
  values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp), 
        (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp), 
        (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
        (4, '2018-12-29 20:00:00'::timestamp, '2019-01-3 16:00:00'::timestamp)

), holy(dd,iswrk) AS (values ('2019-01-01'::date,false),('2019-01-02',false),('2019-01-03',false),('2019-01-04',false),('2019-01-07',false),
('2019-01-08',false),('2019-03-08',false),('2019-05-01',false),('2019-05-02',false),('2019-05-03',false),('2019-05-09',false),('2019-05-10',false),
('2019-06-12',false),('2019-11-04',false),('2018-01-01',false),('2018-01-02',false),('2018-01-03',false),('2018-01-04',false),('2018-01-05',false),
('2018-01-08',false),('2018-02-23',false),('2018-03-08',false),('2018-03-09',false),('2018-04-28',true),('2018-04-30',false),('2018-05-01',false),
('2018-05-02',false),('2018-05-09',false),('2018-06-09',true),('2018-06-11',false),('2018-06-12',false),('2018-11-05',false),('2018-12-29',true),
('2018-12-31',false)) 

SELECT  id,start_time,stop_time, (count(gs.d))*interval '9 hour'
  -CASE
       WHEN count(d)=0 OR start_time::date<min(d) THEN interval '0 hour'
       when start_time - min(d)>=interval '19 hour' THEN interval '9 hour'
       when start_time - min(d)<=interval '10 hour' THEN interval '0 hour'
       ELSE start_time-min(d)-interval '10 hour'
  END
  -CASE WHEN count(d)=0 OR stop_time::date>max(d) THEN interval '0 hour'
       WHEN stop_time-max(d)>=interval '19 hour' THEN interval '0 hour'
       WHEN stop_time-max(d)<=interval '10 hour' THEN interval '9 hour'
       ELSE interval '19 hour'- (stop_time-max(d))
  END
FROM periods 
LEFT JOIN LATERAL  (SELECT * FROM generate_series(start_time::date,stop_time::date,'1 day') gs(d) left join holy ON gs.d=holy.dd )gs(d,dd,iswrk) ON 
CASE WHEN extract(isodow from gs.d) IN (6,7) THEN coalesce(iswrk,false) ELSE coalesce(iswrk,true) END 

GROUP BY id,start_time,stop_time
ORDER BY id;

Бинго! Этот вариант запроса уже дал на моих тестовых данных правильный результат.
ИМХО, всё просто.
Через рекурсивное CTE воссоздадим список дней от, включительно, 01.01.2018 до 31.12.2019.
Производственный календарь берём вот тут:
data.gov.ru/opendata/resource/8ba5011a-233e-4e01-a1d2-ff5598d0f34f#2/0.0/0.0
Это текстовый файл, и легко, также в CTE, может быть развернут в линейный список.
Ну и далее Select из periods, outer apply (Select SUM(дни) from первое СТЕ inner join второе СТЕ, со списком рабочих дней, если день — рабочий, и день >= стартовой даты и <=конечной даты из periods).

Как то так.
Про сокращенные рабочие дни как-то забыли. Недостаточно реалистично :-)
Почему-то ни разу не видел, чтобы в SLA указывали, что предпраздничные рабочие дни на час короче. В SLA обычно пишут «бла-бла-бла за N часов в рабочее время» и рабочее время определено «с/по в рабочие дни кроме выходных и официальных праздников», всё.

Буду рад, если хоть одно из решений позволит учесть укороченные предпраздничные дни. Когда я эту задачу решал в реальной жизни, я на это заложился, но не пригодилось.
Пожалуйста прячьте код под спойлер!
Что-то я смотрю, что рабочие часы воспринимаются почему-то исключительно без рабочих минут. Минуты не забывайте! Я поправил исходные интервалы и ожидаемый ответ, чтобы подчеркнуть наличие минут.
Решение от DanStopka, прислано мне в личку:
спойлер
Добрый день! Для решения задачи зарегистрировался на хабре, комменты оставлять не дает.
Мое решение:
with periods(id, start_time, stop_time) as (
values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:00:00'::timestamp),
(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),

hollydays(dt) as (values ('2019-03-29'))

select p.id, start_time, stop_time, cnt work_hrs from periods
join (
select
id, count(1) cnt
from (select id, generate_series(start_time, stop_time — interval '1 hour', '1 hour') tm from periods) p
where
to_char(tm, 'hh24')::int between 10 and 19 — 1 and
extract(dow from tm) between 1 and 5 and
tm::date not in (select dt::date from hollydays)
group by id
) p on p.id = periods.id

UFO just landed and posted this here
Я этот ваш постгрес уважаю, конечно, но по жизни работаю с ораклом. Такшта простите, если чо. Нагуглил какие-то функции для работы с интервалами и генерации последовательностей, взял первое, что попалось.
У меня в 9.5 все работает.
with periods(id, start_time, stop_time) as (
  values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp), 
        (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp), 
        (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
        (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
    ),
  wd as ( -- это все дни 2018 и 2019 годов
         select workday + '10 hour'::interval st, workday + '19 hour'::interval en -- начало и конец раб. дня
           from (select '2018-01-01'::timestamp + i * '1 day'::interval workday
		   from generate_series(0,729) i
		  where mod(i, 7) not in (5, 6)  -- минус выходные
		  -- плюс рабочие выходные
		  union all select '2018-04-28'::timestamp
		  union all select '2018-06-09'::timestamp
		  union all select '2018-12-29'::timestamp
                 -- минус праздничные будни
		 except select '2018-01-01'::timestamp
		 except select '2018-01-02'::timestamp
		 except select '2018-01-03'::timestamp
		 except select '2018-01-04'::timestamp
		 except select '2018-01-05'::timestamp
		 except select '2018-01-08'::timestamp
		 except select '2018-02-23'::timestamp
		 except select '2018-03-08'::timestamp
		 except select '2018-03-09'::timestamp
		 except select '2018-04-30'::timestamp
		 except select '2018-05-01'::timestamp
		 except select '2018-05-02'::timestamp
		 except select '2018-05-09'::timestamp
		 except select '2018-06-11'::timestamp
		 except select '2018-06-12'::timestamp
		 except select '2018-11-05'::timestamp
		 except select '2018-12-31'::timestamp
		 except select '2019-01-01'::timestamp
		 except select '2019-01-02'::timestamp
		 except select '2019-01-03'::timestamp
		 except select '2019-01-04'::timestamp
		 except select '2019-01-07'::timestamp
		 except select '2019-01-08'::timestamp
		 except select '2019-03-08'::timestamp
		 except select '2019-05-01'::timestamp
		 except select '2019-05-02'::timestamp
		 except select '2019-05-03'::timestamp
		 except select '2019-05-09'::timestamp
		 except select '2019-05-10'::timestamp
		 except select '2019-06-12'::timestamp
		 except select '2019-11-04'::timestamp) t
	)
-- собсна решение начинается тут:
select id, start_time, stop_time, sum(work_end - work_start) work_hrs 
  from (select p.id, p.start_time, p.stop_time,
	       case when p.start_time > wd.st then p.start_time else wd.st end work_start,
	       case when p.stop_time < wd.en then p.stop_time else wd.en end work_end
	from periods p join wd 
	     on p.start_time < wd.en and p.stop_time > wd.st
	) tt
 group by id, start_time, stop_time
 order by id;




Респект, работает! Для «неродной» системы вообще супер. Хотя generate_series можно использовать более прямо, сразу даты генерировать.

Каюсь, я слегка подсмотрел в первые два решения (в частности, стырил оттуда график выходных, ибо самому его делать было лень), но они мне не понравились, потому что при подсчёте времени сам собой напрашивается встроенный в PostgreSQL тип interval (и я его очень сильно люблю, хоть сам почти и не пользуюсь). Поэтому я пошёл и сделал всё по своему. Поэтому, вот:


Решение на типе interval
with periods(id, start_time, stop_time) as (
  values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp), 
        (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp), 
        (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
        (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
holiday_overrides(day, working) AS (
  values ('2019-01-01'::date,false),('2019-01-02',false),('2019-01-03',false),('2019-01-04',false),('2019-01-07',false),('2019-01-08',false),('2019-03-08',false),('2019-05-01',false),('2019-05-02',false),('2019-05-03',false),('2019-05-09',false),('2019-05-10',false),('2019-06-12',false),('2019-11-04',false),('2018-01-01',false),('2018-01-02',false),('2018-01-03',false),('2018-01-04',false),('2018-01-05',false),('2018-01-08',false),('2018-02-23',false),('2018-03-08',false),('2018-03-09',false),('2018-04-28',true),('2018-04-30',false),('2018-05-01',false),('2018-05-02',false),('2018-05-09',false),('2018-06-09',true),('2018-06-11',false),('2018-06-12',false),('2018-11-05',false),('2018-12-29',true),('2018-12-31',false)
),
period_worktime(period_id, worktime) AS (
  SELECT 
    periods.id AS period_id,
    SUM(
      CASE 
        WHEN days.day::date = periods.start_time::date THEN (days.day::date + 'PT19H'::interval) - greatest(periods.start_time, days.day::date + 'PT10H'::interval)
        WHEN days.day::date = periods.stop_time::date  THEN least(periods.stop_time, days.day::date + 'PT19H'::interval) - (days.day::date + 'PT10H'::interval)
        ELSE 'PT9H'::interval
      END
    ) AS worktime
  FROM periods JOIN LATERAL generate_series(start_time::date, stop_time::date, '1 day'::interval) days(day) ON 1=1
  WHERE
    NOT EXISTS (SELECT * FROM holiday_overrides ho WHERE ho.day = days.day::date AND working=false)
    AND (
         extract(isodow from days.day) NOT IN (6,7) 
      OR EXISTS (SELECT * FROM holiday_overrides ho WHERE ho.day = days.day::date AND working=true)
    )
  GROUP BY period_id
)
SELECT periods.*, period_worktime.worktime
FROM periods JOIN period_worktime ON periods.id = period_worktime.period_id;
Согласен, интервалы здесь очень в тему.
На вот таком периоде неправильно считает:
('2019-05-23 16:51:40', '2019-05-24 02:55:50')
Добавил пару условий на начало/конец рабочего дня
with periods(id, start_time, stop_time) as (
  values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
        (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
        (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
        (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp),
        (5, '2019-05-23 16:51:40'::timestamp, '2019-05-24 02:55:50'::timestamp)
),
holiday_overrides(day, working) AS (
  values ('2019-01-01'::date,false),('2019-01-02',false),('2019-01-03',false),('2019-01-04',false),('2019-01-07',false),('2019-01-08',false),('2019-03-08',false),('2019-05-01',false),('2019-05-02',false)$
),
period_worktime(period_id, worktime) AS (
  SELECT
    periods.id AS period_id,
    SUM(
      CASE
        WHEN days.day::date = periods.start_time::date THEN (days.day::date + 'PT19H'::interval) - greatest(least(periods.start_time, days.day::date + 'PT19H'::interval), days.day::date + 'PT10H'::interv$
        WHEN days.day::date = periods.stop_time::date  THEN least(greatest(periods.stop_time, days.day::date + 'PT10H'::interval), days.day::date + 'PT19H'::interval) - (days.day::date + 'PT10H'::interva$
        ELSE 'PT9H'::interval
      END
    ) AS worktime
  FROM periods JOIN LATERAL generate_series(start_time::date, stop_time::date, '1 day'::interval) days(day) ON 1=1
  WHERE
    NOT EXISTS (SELECT * FROM holiday_overrides ho WHERE ho.day = days.day::date AND working=false)
    AND (
         extract(isodow from days.day) NOT IN (6,7) 
      OR EXISTS (SELECT * FROM holiday_overrides ho WHERE ho.day = days.day::date AND working=true)
    )
  GROUP BY period_id
)
SELECT periods.*, period_worktime.worktime
FROM periods JOIN period_worktime ON periods.id = period_worktime.period_id;
Длинные строчки покоцались.

На вот таком периоде показывает 8 часов вместо 8 минут:
('2018-06-09 11:00:00', '2018-06-09 11:08:00')
Теперь я понял, что условие-то можно было сильно упростить и обойтись без CASE
with periods(id, start_time, stop_time) as (
  values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
        (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
        (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
        (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp),
        (5, '2019-05-23 16:51:40'::timestamp, '2019-05-24 02:55:50'::timestamp),
        (6, '2018-06-09 11:00:00'::timestamp, '2018-06-09 11:08:00'::timestamp)
),
holiday_overrides(day, working) AS (
  values ('2019-01-01'::date,false),('2019-01-02',false),('2019-01-03',false),('2019-01-04',false),('2019-01-07',false),('2019-01-08',false),('2019-03-08',false),('2019-05-01',false),('2019-05-02',false),('2019-05-03',false),('2019-05-09',false),('2019-05-10',false),('2019-06-12',false),('2019-11-04',false),('2018-01-01',false),('2018-01-02',false),('2018-01-03',false),('2018-01-04',false),('2018-01-05',false),('2018-01-08',false),('2018-02-23',false),('2018-03-08',false),('2018-03-09',false),('2018-04-28',true),('2018-04-30',false),('2018-05-01',false),('2018-05-02',false),('2018-05-09',false),('2018-06-09',true),('2018-06-11',false),('2018-06-12',false),('2018-11-05',false),('2018-12-29',true),('2018-12-31',false)
),
period_worktime(period_id, worktime) AS (
  SELECT
    periods.id AS period_id,
    SUM(
     least(greatest(periods.stop_time, days.day::date + 'PT10H'::interval), days.day::date + 'PT19H'::interval) 
     -
     least(greatest(periods.start_time, days.day::date + 'PT10H'::interval), days.day::date + 'PT19H'::interval)
    ) AS worktime
  FROM periods JOIN LATERAL generate_series(start_time::date, stop_time::date, '1 day'::interval) days(day) ON 1=1
  WHERE
    NOT EXISTS (SELECT * FROM holiday_overrides ho WHERE ho.day = days.day::date AND working=false)
    AND (
         extract(isodow from days.day) NOT IN (6,7) 
      OR EXISTS (SELECT * FROM holiday_overrides ho WHERE ho.day = days.day::date AND working=true)
    )
  GROUP BY period_id
)
SELECT periods.*, period_worktime.worktime
FROM periods JOIN period_worktime ON periods.id = period_worktime.period_id;
Бинго! Работает правильно на моих выборках. Кстати, в результате Ваше решение стало похожим почти дословно на уже имеющиеся.
Скрытый текст

Пишу с телефона, потому не код а слова напишу:
Как насчет просто посчитать время рабочее краев диапазона плюс( (всего_дней минус празничных_или_выходных) умножить на 8)

Да, алгоритм-то несложен — посчитать количество рабочих дней, правильно учесть первый и последний дни, всё сложить. Осталось это выразить на SQL.
PS умножить надо на 9
Вообще по логике в каждом интервале отсекается день начала и день окончания. Считается количество рабочих дней в каждом интервале (для каждой недели 5 дней * на количество недель + доп рабочие дни — праздники) и умножается на 8. Потом прибавляются рабочие часы начала интервала (19-00 минус время начала) и конца интервала (10-00 плюс время окончания). То есть по сути задача сводится, на мой взгляд, к расчету количества рабочих недель
UFO just landed and posted this here
Кодом пусть занимаются программисты ))

Вообще расчет количества недель довольно быстро гуглится

www.sqlines.com/postgresql/how-to/datediff

+ конечно забыл про неполные недели для интервалов. Полагаю нужно вычислить день недели начала и конца интервалов, исключить эти дни и посчитать по 5 дневке
UFO just landed and posted this here
UFO just landed and posted this here
Верный. Если ты отработал 13:07:12, значит в часах это 14, а не 13

Начал писать с такой же логикой, закончил когда понял насколько это усложняет решение, и что в реальных кейсах (с интервалами допустим в пределах года), мы получаем увеличение O сложности в 1-4 раза по сравнению с оптимизированным, за счет сильного увеличения сложности алгоритма и ухудшения читаемости.
А практической необходимости такой оптимизации и вовсе придумать не смог.


Сложность оценивал как O(periods * holidays + periods + periods * holidays_overrides) для оптимального, и O(periods * (holidays + periods_weekdays - holidays_overrides)) для решения с генерацией всех выходных. Исходя из того что нам нужно обрабатывать много периодов, но не столетия с кучей выходных.

Ну вот что получилось с округлением до часов

SQL
with periods(id,
start_time,
stop_time) as (
values(1,
'2019-03-29 07:00:00'::timestamp,
'2019-04-08 14:00:00'::timestamp),
(2,
'2019-04-10 07:00:00'::timestamp,
'2019-04-10 20:00:00'::timestamp),
(3,
'2019-04-11 12:00:00'::timestamp,
'2019-04-12 16:07:12'::timestamp),
(4,
'2018-12-28 12:00:00'::timestamp,
'2019-01-16 16:00:00'::timestamp) ) ,
calc_table as (
 select 
 	id,
 	date_part('day', date_trunc('day',stop_time) - date_trunc('day',start_time))::int as date_diff,
 	date_trunc('day',start_time)+ interval '10 hour' as start_work_time,
 	date_trunc('day',stop_time)+ interval '19 hour' as end_work_time
 from 
 	periods
) -- промежуточная таблица вычислений
select
	p.*,
	trunc(c.date_diff/7)*5*9 -- количество полных недель в рабочих часах
	+
	c.date_diff%7*9 -- дробная часть в рабочих часах
	-
	case 
		when trunc(c.date_diff/7) > 0 and c.date_diff%7 > 0  
		then
		(extract(isodow from start_time) - c.date_diff%7)*9
		else
		0
	end -- корректировка дробной части на выходные дни
	+ 
	(9 -
	case 
		when c.end_work_time > stop_time
		then date_part('hour', c.end_work_time - stop_time)
		else 0
	end
	-
	case 
		when start_time > c.start_work_time
		then date_part('hour', start_time - c.start_work_time)
		else 0
	end) -- корректировка рабочих часов начала и конца интервала
	-
	case
		when p.id = 4 
		then 9 * 9  
		else 0
	end -- количество праздничных дней в 4м интервале
	+
	case
		when p.id = 4 
		then 1 * 9 
		else 0
	end	-- 1 дополнительный рабочий день в 4м интервале
	as work_hrs
from
	periods as p
	inner join calc_table as c
	on p.id = c.id

Вроде работает, но без минут нехорошо.
Неправильно работает на периоде, показывает отрицательное время:
('2019-05-23 16:51:40', '2019-05-24 02:55:50')

И хотелось бы минут-секунд.
UFO just landed and posted this here
UFO just landed and posted this here
UFO just landed and posted this here
Твой код не работает

SQL Error [42P01]: ОШИБКА: отношение «periods» не существует
Позиция: 162
UFO just landed and posted this here
Ты уже исправил. Молодец. Я тебе код ошибки выложил предыдущего твоего нерабочего варианта
UFO just landed and posted this here
Я не знаю, что ты можешь, а что нет. Но у твоего сообщения стоит метка, что ты его редактировал
UFO just landed and posted this here
Решение не учитывает праздничные дни.

Про задачку с интервалами сформулируйте более формально, чтобы было понятно какими входными данными оперировать и в какой форме результат получить. С удовольствием порешаю на досуге.
UFO just landed and posted this here
Да, в условии интервалы могут быть из 2018-2019 гг., а Вы учли только новогодние праздники 2019 года.
UFO just landed and posted this here
Я отчасти с Вами согласен, но тем не менее в условии я просил учесть праздники за два года и мне сперва пришлось «при необходимости решение можно будет легко дополнить», чтобы погонять на тестовых данных. Я бы не стал проявлять излишний формализм, если бы это был единственный нюанс Вашего решения. Но у Вас не учитываются дополнительные рабочие дни, например, 2018-04-28 был рабочим днём. Также потерялись в выводе периоды, которые совсем не попали на рабочее время.
А вот компактность Вашего решения мне очень нравится.
UFO just landed and posted this here
UFO just landed and posted this here
Накидал пример данных. Так?
with events as (num, event_time, event_type) as (
values
(1, '2019-01-01', 'start'),
(1, '2019-01-02', 'stop'),
(1, '2019-01-03', 'start'),
(2, '2019-01-04', 'start'),
(1, '2019-01-05', 'stop'),
(3, '2019-01-06', 'start'),
(3, '2019-01-07', 'stop'),
(2, '2019-01-08', 'stop'),
(3, '2019-01-09', 'start') )


Если допустить, что данные всегда корректны, то можно значительно проще получить решение. Сперва считаем для каждой временной отметки количество работающих сервисов, прибавляя единичку на событие start и вычитая на stop. Нужные нам интервалы будут те, когда количество сервисов равно нулю. С оконными функциями это можно посчитать в один проход:
with events as (num, event_time, event_type) as (
values
(1, '2019-01-01', 'start'),
(1, '2019-01-02', 'stop'),
(1, '2019-01-03', 'start'),
(2, '2019-01-04', 'start'),
(1, '2019-01-05', 'stop'),
(3, '2019-01-06', 'start'),
(3, '2019-01-07', 'stop'),
(2, '2019-01-08', 'stop'),
(3, '2019-01-09', 'start') ),

events_qty_per_time as (
select lag(event_time) over (order by event_time
                             range between unbounded preceding
                                       and current row) as start_time
     , event_time as stop_time
     , sum( case when event_type = 'start' then 1
                                          else -1 end) over (order by event_time
                                                             range between unbounded preceding
                                                                       and current row) as qty
  from events
order by event_time
)

select *
  from events_qty_per_time
 where qty = 0


Результат:
 start_time | stop_time  | qty 
------------+------------+-----
 2019-01-01 | 2019-01-02 |   0
 2019-01-07 | 2019-01-08 |   0
(2 rows)
К сожалению, на Postgres пишу только для развлечения (основная RDBMS — MS Sql Server), поэтому код может быть не идеален с точки зрения использования Postgres специфического синтаксиса.

Код без итерации по дням
with cte_periods(id, start_time, stop_time) as (
    values
		(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
        (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
        (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
        (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
), cte_holidays as (
   select
		h.date::date
   from (values
		('2018-01-01'),
		('2018-01-02'),
		('2018-01-03'),
		('2018-01-04'),
		('2018-01-05'),
		('2018-01-07'),
		('2018-01-08'),
		('2018-02-23'),
		('2018-03-08'),
		('2018-05-01'),
		('2018-05-09'),
		('2018-06-12'),
		('2018-11-04'),
		('2019-01-01'),
		('2019-01-02'),
		('2019-01-03'),
		('2019-01-04'),
		('2019-01-05'),
		('2019-01-07'),
		('2019-01-08'),
		('2019-02-23'),
		('2019-03-08'),
		('2019-05-01'),
		('2019-05-09'),
		('2019-06-12'),
		('2019-11-04')
	) as h(date)
), cte_holidays_no_weekends as (
	select h.date
	from cte_holidays as h
    where
 		extract(isodow from h.date) not in (6,7)
), cte_periods_extended as (
    select
    	p.id,
        case when p.start_time::time < '10:00' then '10:00' else p.start_time::time end as start_time,
        case when p.stop_time::time > '19:00' then '19:00' else p.stop_time::time end as stop_time,
        p.start_time::date as start_date,
        p.stop_time::date as stop_date,
        p.stop_time::date - p.start_time::date - 1 as days_count,
        (p.stop_time::date - '2017-01-07'::date) / 7 - (p.start_time::date - '2017-01-07'::date) / 7 as saturdays_count,
        (p.stop_time::date - '2017-01-08'::date) / 7 - (p.start_time::date - '2017-01-08'::date) / 7 as sundays_count
    from cte_periods as p
)
select
	p.id,
	p.start_date + p.start_time as start_time,
	p.stop_date + p.stop_time as stop_time,
	case
	    when p.start_date = p.stop_date then
	        p.stop_time - p.start_time
		else
		    p.stop_time - p.start_time + '9:00' +
			(p.days_count - p.saturdays_count - p.sundays_count - h.holidays_count) * 9 * interval '1 hour'
	end as work_hours
from cte_periods_extended as p
    cross join lateral (
        select count(*)
        from cte_holidays_no_weekends as c
        where
        	c.date >= p.start_date and
            c.date <= p.stop_date
	) as h(holidays_count)

Интересный подход, пока из опубликованных решений никто не пробовал так считать, но есть ошибка в реализации. Запустите на интервале ('2019-01-01 21:00:00', '2019-01-01 21:00:00'), эффект неожиданный.
уверен, что есть неучтенные кейсы, в целом я протестировал только на данной выборке (и не проверял на отрицательные величины). Проверю сегодня вечером. Что мне нравится в таком решении, так это то, что оно не зависит от длины периодов — меньше вероятность, что оно перестанет работать с течением времени.
Не выдержал, поправил, добавил учёт перенесённых выходных (добавил только пару дней в 2018 году для тестирования)

Код без итерации по дням + учет перенесенных выходных дней
with cte_periods(id, start_time, stop_time) as (
    values
		(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
        (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
        (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
        (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp),
        (5, '2019-01-01 21:00:00'::timestamp, '2019-01-01 21:00:00'::timestamp),
        (6, '2018-04-27 09:00:00'::timestamp, '2018-05-03 15:01:00'::timestamp),
        (7, '2019-01-01 09:00:00'::timestamp, '2019-01-01 09:15:00'::timestamp)
), cte_holidays as (
   select
		h.date::date
   from (values
		('2018-01-01'),
		('2018-01-02'),
		('2018-01-03'),
		('2018-01-04'),
		('2018-01-05'),
		('2018-01-07'),
		('2018-01-08'),
		('2018-02-23'),
		('2018-03-08'),
		('2018-04-30'),
		('2018-05-01'),
        ('2018-05-02'),
		('2018-05-09'),
		('2018-06-12'),
		('2018-11-04'),
		('2019-01-01'),
		('2019-01-02'),
		('2019-01-03'),
		('2019-01-04'),
		('2019-01-05'),
		('2019-01-07'),
		('2019-01-08'),
		('2019-02-23'),
		('2019-03-08'),
		('2019-05-01'),
		('2019-05-09'),
		('2019-06-12'),
		('2019-11-04')
	) as h(date)
), cte_working_weekend as (
   select
		h.date::date
   from (values
		('2018-04-28')
	) as h(date)
), cte_holidays_no_weekends as (
	select h.date
	from cte_holidays as h
    where
 		extract(isodow from h.date) not in (6,7)
), cte_periods_extended as (
    select
    	p.id,
        p.start_time as original_start_time,
        p.stop_time as original_stop_time,
        case
            when p.start_time::time < '10:00' then '10:00'
            when p.start_time::time > '19:00' then '19:00'
            else p.start_time::time
        end as start_time,
        case
            when p.stop_time::time < '10:00' then '10:00'
            when p.stop_time::time > '19:00' then '19:00'
            else p.stop_time::time
        end as stop_time,
        p.start_time::date as start_date,
        p.stop_time::date as stop_date,
        p.stop_time::date - p.start_time::date - 1 as days_count,
        (p.stop_time::date - '2017-01-07'::date) / 7 - (p.start_time::date - '2017-01-07'::date) / 7 as saturdays_count,
        (p.stop_time::date - '2017-01-08'::date) / 7 - (p.start_time::date - '2017-01-08'::date) / 7 as sundays_count
    from cte_periods as p
)
select
	p.id,
	p.original_start_time as start_time,
	p.original_stop_time as stop_time,
	case
	    when p.start_date = p.stop_date then
	        p.stop_time - p.start_time
		else
		    p.stop_time - p.start_time + '9:00' +
			(p.days_count - p.saturdays_count - p.sundays_count - h.holidays_count + ww.working_weekends_count) * 9 * interval '1 hour'
	end as work_hours
from cte_periods_extended as p
    cross join lateral (
        select count(*) from cte_holidays_no_weekends as tt where tt.date between p.start_date and p.stop_date
    ) as h(holidays_count)
    cross join lateral (
        select count(*) from cte_working_weekend as tt where tt.date between p.start_date and p.stop_date
	) as ww(working_weekends_count)

Что ж не добавили в рабочие дни ещё 2018-06-09 и 2018-12-29? Не так уж это и утомительно.

У меня сошлись результаты запроса на небольшой тестовой выборке, но не сошлись на большой по реальным данным. Сходу ошибку не вижу, посмотрю ещё попозже.
Если можете предоставить тестовую запись на которой не сходится, я посмотрю в чём может быть проблема
У Вас 2018-06-10 подхватывается рабочим днём. Вот пример данных, где неверно считается:
('2018-06-09 16:51:40', '2018-06-10 10:55:50')
Предварительно я Ваш запрос скорректировал правильными праздниками и доп. рабочими днями:
Заголовок спойлера
with ...
cte_holidays as (
select h.date::date
from (values('2018-01-01'), -- 2018
('2018-01-02'),
('2018-01-03'),
('2018-01-04'),
('2018-01-05'),
('2018-01-08'),
('2018-02-23'),
('2018-03-08'),
('2018-03-09'),
('2018-05-01'),
('2018-05-02'),
('2018-05-09'),
('2018-06-11'),
('2018-06-12'),
('2018-11-05'),
('2018-12-31'),
('2019-01-01'), -- 2019
('2019-01-02'),
('2019-01-03'),
('2019-01-04'),
('2019-01-07'),
('2019-01-08'),
('2019-03-08'),
('2019-05-01'),
('2019-05-02'),
('2019-05-03'),
('2019-05-09'),
('2019-05-10'),
('2019-06-12'),
('2019-11-04') ) as h(date)
), cte_working_weekend as (
select h.date::date
from (values ('2018-04-28'),
('2018-06-09'),
('2018-12-29') ) as h(date)
), ...

Переделал логику немного. Считаются отдельно полные рабочие дни + время начала и конца периода. Главное достоинство этого подхода — он гораздо меньше зависит от длины периода (по сравнению с решениями, которые множат периоды на дни). Возможно, конкретно в этой задаче это не так уж важно (сомнительно, что в системе заявок будут заявки, которые открыты годами)

Код без итерации по дням + tsrange для остатка
with cte_periods(id, start_time, stop_time) as (
    values
		(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
		(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
		(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
		(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp),
		(5, '2019-01-01 21:00:00'::timestamp, '2019-01-01 21:00:00'::timestamp),
		(6, '2018-06-09 16:51:40'::timestamp, '2018-06-10 10:55:50'::timestamp)
), cte_holidays as (
   select
		h.date::date
   from (values
		('2018-01-01'), -- 2018
		('2018-01-02'),
		('2018-01-03'),
		('2018-01-04'),
		('2018-01-05'),
		('2018-01-08'),
		('2018-02-23'),
		('2018-03-08'),
		('2018-03-09'),
		('2018-05-01'),
		('2018-05-02'),
		('2018-05-09'),
		('2018-06-11'),
		('2018-06-12'),
		('2018-11-05'),
		('2018-12-31'),
		('2019-01-01'), -- 2019
		('2019-01-02'),
		('2019-01-03'),
		('2019-01-04'),
		('2019-01-07'),
		('2019-01-08'),
		('2019-03-08'),
		('2019-05-01'),
		('2019-05-02'),
		('2019-05-03'),
		('2019-05-09'),
		('2019-05-10'),
		('2019-06-12'),
		('2019-11-04')
	) as h(date)
), cte_working_weekend as (
   select
		h.date::date
   from (values
		('2018-04-28'),
		('2018-06-09'),
		('2018-12-29')
	) as h(date)
), cte_holidays_improved as (
	select h.date
	from cte_holidays as h
	where
		extract(isodow from h.date) not in (6,7)
)
select
	p.id,
	p.start_time,
	p.stop_time,
	coalesce(a1.days, 0) * interval '9 hour' + coalesce(a2.hours, interval '0 hour')
from cte_periods as p
	cross join lateral (select p.start_time::date, p.stop_time::date) as d(start_date, stop_date)
	cross join lateral (select d.stop_date - d.start_date) as k(date_diff)
	left join lateral (
		select
			k.date_diff - 2 -
			((d.stop_date - '0001-01-07'::date) / 7 - (d.start_date - '0001-01-05'::date) / 7) -
 			((d.stop_date - '0001-01-08'::date) / 7 - (d.start_date - '0001-01-06'::date) / 7) -
 			(select count(*) from cte_holidays_improved as tt where tt.date > d.start_date and tt.date < d.stop_date) +
			(select count(*) from cte_working_weekend as tt where tt.date > d.start_date and tt.date < d.stop_date)
 		where
 			k.date_diff > 1
	) as a1(days) on true
	cross join lateral (
		select sum((upper(b.hours) - lower(b.hours)))
		from (
			select d.start_date, p.start_time, d.start_date + '19:00'::time where k.date_diff >= 1 union all
			select d.stop_date, d.stop_date + '10:00'::time, p.stop_time where k.date_diff >= 1 union all
			select d.start_date, p.start_time, p.stop_time where k.date_diff = 0
		) as a(date, start_time, stop_time)
			cross join lateral (select
				tsrange(a.start_time, a.stop_time, '[]') *
				tsrange(a.date + '10:00'::time, a.date + '19:00'::time, '[]')
			) as b(hours)
		where
			not exists (select * from cte_holidays_improved as tt where tt.date = a.date) and
			(extract(isodow from a.date) not in (6,7) or exists (select * from cte_working_weekend as tt where tt.date = a.date))
	) as a2(hours)

Падает с ошибкой:
ERROR: range lower bound must be less than or equal to range upper bound

на данных:
('2019-05-23 16:51:40', '2019-05-24 02:55:50')
Да, логично, что падает, поправил

Код без итерации по дням
with cte_periods(id, start_time, stop_time) as (
    values
		(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
		(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
		(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
		(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp),
		(5, '2019-01-01 21:00:00'::timestamp, '2019-01-01 21:00:00'::timestamp),
		(6, '2018-06-09 16:51:40'::timestamp, '2018-06-10 10:55:50'::timestamp),
        (7, '2019-05-23 16:51:40'::timestamp, '2019-05-24 02:55:50'::timestamp)
), cte_holidays as (
   select
		h.date::date
   from (values
		('2018-01-01'), -- 2018
		('2018-01-02'),
		('2018-01-03'),
		('2018-01-04'),
		('2018-01-05'),
		('2018-01-08'),
		('2018-02-23'),
		('2018-03-08'),
		('2018-03-09'),
		('2018-05-01'),
		('2018-05-02'),
		('2018-05-09'),
		('2018-06-11'),
		('2018-06-12'),
		('2018-11-05'),
		('2018-12-31'),
		('2019-01-01'), -- 2019
		('2019-01-02'),
		('2019-01-03'),
		('2019-01-04'),
		('2019-01-07'),
		('2019-01-08'),
		('2019-03-08'),
		('2019-05-01'),
		('2019-05-02'),
		('2019-05-03'),
		('2019-05-09'),
		('2019-05-10'),
		('2019-06-12'),
		('2019-11-04')
	) as h(date)
), cte_working_weekend as (
   select
		h.date::date
   from (values
		('2018-04-28'),
		('2018-06-09'),
		('2018-12-29')
	) as h(date)
), cte_holidays_improved as (
	select h.date
	from cte_holidays as h
	where
		extract(isodow from h.date) not in (6,7)
)
select
	p.id,
	p.start_time,
	p.stop_time,
	coalesce(a1.days, 0) * interval '9 hour' + coalesce(a2.hours, interval '0 hour')
from cte_periods as p
	cross join lateral (select p.start_time::date, p.stop_time::date) as d(start_date, stop_date)
	cross join lateral (select d.stop_date - d.start_date) as k(date_diff)
	left join lateral (
		select
			k.date_diff - 2 -
			((d.stop_date - '0001-01-07'::date) / 7 - (d.start_date - '0001-01-05'::date) / 7) -
 			((d.stop_date - '0001-01-08'::date) / 7 - (d.start_date - '0001-01-06'::date) / 7) -
 			(select count(*) from cte_holidays_improved as tt where tt.date > d.start_date and tt.date < d.stop_date) +
			(select count(*) from cte_working_weekend as tt where tt.date > d.start_date and tt.date < d.stop_date)
 		where
 			k.date_diff > 1
	) as a1(days) on true
	cross join lateral (
		select sum((upper(b.hours) - lower(b.hours)))
		from (
			select d.start_date, p.start_time, d.start_date + '19:00'::time where k.date_diff >= 1 union all
			select d.stop_date, d.stop_date + '10:00'::time, p.stop_time where k.date_diff >= 1 union all
			select d.start_date, p.start_time, p.stop_time where k.date_diff = 0
		) as a(date, start_time, stop_time)
			cross join lateral (select
				tsrange(a.start_time, a.stop_time, '[]') *
				tsrange(a.date + '10:00'::time, a.date + '19:00'::time, '[]')
			) as b(hours)
		where
		    a.start_time <= a.stop_time and
			not exists (select * from cte_holidays_improved as tt where tt.date = a.date) and
			(extract(isodow from a.date) not in (6,7) or exists (select * from cte_working_weekend as tt where tt.date = a.date))
	) as a2(hours)

Близко, но ещё не всё исправлено. Падать перестало, но на периоде ('2018-06-10 18:46:10', '2018-06-13 10:18:18') запрос показал отрицательное время -08:41:42.
И то верно. Было бы, конечно, проще тестировать, если бы была хорошая тестовая выборка.

Код без итерации по дням
with cte_periods(id, start_time, stop_time) as (
    values
		(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
		(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
		(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
		(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp),
		(5, '2019-01-01 21:00:00'::timestamp, '2019-01-01 21:00:00'::timestamp),
		(6, '2018-06-09 16:51:40'::timestamp, '2018-06-10 10:55:50'::timestamp),
        (7, '2019-05-23 16:51:40'::timestamp, '2019-05-24 02:55:50'::timestamp),
        (8, '2018-06-10 18:46:10'::timestamp, '2018-06-13 10:18:18'::timestamp)
), cte_holidays as (
   select
		h.date::date
   from (values
		('2018-01-01'), -- 2018
		('2018-01-02'),
		('2018-01-03'),
		('2018-01-04'),
		('2018-01-05'),
		('2018-01-08'),
		('2018-02-23'),
		('2018-03-08'),
		('2018-03-09'),
		('2018-05-01'),
		('2018-05-02'),
		('2018-05-09'),
		('2018-06-11'),
		('2018-06-12'),
		('2018-11-05'),
		('2018-12-31'),
		('2019-01-01'), -- 2019
		('2019-01-02'),
		('2019-01-03'),
		('2019-01-04'),
		('2019-01-07'),
		('2019-01-08'),
		('2019-03-08'),
		('2019-05-01'),
		('2019-05-02'),
		('2019-05-03'),
		('2019-05-09'),
		('2019-05-10'),
		('2019-06-12'),
		('2019-11-04')
	) as h(date)
), cte_working_weekend as (
   select
		h.date::date
   from (values
		('2018-04-28'),
		('2018-06-09'),
		('2018-12-29')
	) as h(date)
), cte_holidays_improved as (
	select h.date
	from cte_holidays as h
	where
		extract(isodow from h.date) not in (6,7)
)
select
	p.id,
	p.start_time,
	p.stop_time,
    case when a1.days > 0 then a1.days else 0 end * interval '9 hour' + coalesce(a2.hours, interval '0 hour')
from cte_periods as p
	cross join lateral (select p.start_time::date, p.stop_time::date) as d(start_date, stop_date)
	cross join lateral (select d.stop_date - d.start_date) as k(date_diff)
	left join lateral (
		select
			k.date_diff - 2 -
			((d.stop_date - '0001-01-07'::date) / 7 - (d.start_date - '0001-01-05'::date) / 7) -
 			((d.stop_date - '0001-01-08'::date) / 7 - (d.start_date - '0001-01-06'::date) / 7) -
 			(select count(*) from cte_holidays_improved as tt where tt.date > d.start_date and tt.date < d.stop_date) +
			(select count(*) from cte_working_weekend as tt where tt.date > d.start_date and tt.date < d.stop_date)
	) as a1(days) on true
	cross join lateral (
		select sum((upper(b.hours) - lower(b.hours)))
		from (
			select d.start_date, p.start_time, d.start_date + '19:00'::time where k.date_diff >= 1 union all
			select d.stop_date, d.stop_date + '10:00'::time, p.stop_time where k.date_diff >= 1 union all
			select d.start_date, p.start_time, p.stop_time where k.date_diff = 0
		) as a(date, start_time, stop_time)
			cross join lateral (select
				tsrange(a.start_time, a.stop_time, '[]') *
				tsrange(a.date + '10:00'::time, a.date + '19:00'::time, '[]')
			) as b(hours)
		where
		    a.start_time <= a.stop_time and
			not exists (select * from cte_holidays_improved as tt where tt.date = a.date) and
			(extract(isodow from a.date) not in (6,7) or exists (select * from cte_working_weekend as tt where tt.date = a.date))
	) as a2(hours)

На вот таких данных ('2018-05-24 17:31:12', '2018-05-26 17:59:39') даёт 01:28:48, а нужно на один день, то есть на 9 часов больше.

Про тестирование. Я никаких тестовых данных не давал, это ж задачка на «размять мозги». Я продемонстрировал только как выглядят входные данные, и как должен выглядеть результат. Программист сам должен знать, на каких данных следует проверить заложенные в решение допущения.
Программист сам должен знать, на каких данных следует проверить заложенные в решение допущения
— это бесспорно, к сожалению, не готов тратить достаточное количество времени на создание хорошей тестовой выборки (и продумывать возможныe edge case). Взялся писать, потому что люблю Postgres и никто не предложил решения с подобной логикой, было интересно, насколько компактным можно сделать решение с данным подходом. Пока получается длиннее чем хотелось бы.

Код без итерации по дням
with cte_periods(id, start_time, stop_time) as (
    values
		(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
		(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
		(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
		(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp),
		(5, '2019-01-01 21:00:00'::timestamp, '2019-01-01 21:00:00'::timestamp),
		(6, '2018-06-09 16:51:40'::timestamp, '2018-06-10 10:55:50'::timestamp),
        (7, '2019-05-23 16:51:40'::timestamp, '2019-05-24 02:55:50'::timestamp),
        (8, '2018-06-10 18:46:10'::timestamp, '2018-06-13 10:18:18'::timestamp),
        (9, '2018-05-24 17:31:12'::timestamp, '2018-05-26 17:59:39'::timestamp),
        (10, '2018-05-23 17:31:12'::timestamp, '2018-05-26 17:59:39'::timestamp),
        (11, '2019-03-30 07:00:00'::timestamp, '2019-04-07 14:00:00'::timestamp)
), cte_holidays as (
   select
		h.date::date
   from (values
		('2018-01-01'), -- 2018
		('2018-01-02'),
		('2018-01-03'),
		('2018-01-04'),
		('2018-01-05'),
		('2018-01-08'),
		('2018-02-23'),
		('2018-03-08'),
		('2018-03-09'),
		('2018-05-01'),
		('2018-05-02'),
		('2018-05-09'),
		('2018-06-11'),
		('2018-06-12'),
		('2018-11-05'),
		('2018-12-31'),
		('2019-01-01'), -- 2019
		('2019-01-02'),
		('2019-01-03'),
		('2019-01-04'),
		('2019-01-07'),
		('2019-01-08'),
		('2019-03-08'),
		('2019-05-01'),
		('2019-05-02'),
		('2019-05-03'),
		('2019-05-09'),
		('2019-05-10'),
		('2019-06-12'),
		('2019-11-04')
	) as h(date)
), cte_working_weekend as (
   select
		h.date::date
   from (values
		('2018-04-28'),
		('2018-06-09'),
		('2018-12-29')
	) as h(date)
), cte_holidays_improved as (
	select h.date
	from cte_holidays as h
	where
		extract(isodow from h.date) not in (6,7)
)
select
	p.id,
	p.start_time,
	p.stop_time,
    (select count(*) from cte_holidays_improved as tt where tt.date > d.start_date and tt.date < d.stop_date),
    (select count(*) from cte_working_weekend as tt where tt.date > d.start_date and tt.date < d.stop_date),
    case when a1.days > 0 then a1.days else 0 end * interval '9 hour' + coalesce(a2.hours, interval '0 hour')
from cte_periods as p
	cross join lateral (select p.start_time::date, p.stop_time::date) as d(start_date, stop_date)
	cross join lateral (select d.stop_date - d.start_date) as k(date_diff)
	left join lateral (
		select
			k.date_diff - 1 -
			-- amount of Saturdays between 2 days:
			-- we know that '0001-01-06' is a Saturday, so number of Saturdays between 2 days will be
			-- number of Saturdays between stop_date and '0001-01-06' - number of Saturdays between (start_date - 1) and '0001-01-06'
			-- we calculate here amount of Saturdays / Sundays between start_date + 1 and stop_date - 1
			((d.stop_date - '0001-01-07'::date) / 7 - (d.start_date - '0001-01-06'::date) / 7) -
 			((d.stop_date - '0001-01-08'::date) / 7 - (d.start_date - '0001-01-07'::date) / 7) -
 			(select count(*) from cte_holidays_improved as tt where tt.date > d.start_date and tt.date < d.stop_date) +
			(select count(*) from cte_working_weekend as tt where tt.date > d.start_date and tt.date < d.stop_date)
	) as a1(days) on true
	cross join lateral (
		select sum((upper(b.hours) - lower(b.hours)))
		from (
			select d.start_date, p.start_time, d.start_date + '19:00'::time where k.date_diff >= 1 union all
			select d.stop_date, d.stop_date + '10:00'::time, p.stop_time where k.date_diff >= 1 union all
			select d.start_date, p.start_time, p.stop_time where k.date_diff = 0
		) as a(date, start_time, stop_time)
			cross join lateral (select
				tsrange(a.start_time, a.stop_time, '[]') *
				tsrange(a.date + '10:00'::time, a.date + '19:00'::time, '[]')
			) as b(hours)
		where
		    a.start_time <= a.stop_time and
			not exists (select * from cte_holidays_improved as tt where tt.date = a.date) and
			(extract(isodow from a.date) not in (6,7) or exists (select * from cte_working_weekend as tt where tt.date = a.date))
	) as a2(hours);

Бинго! Теперь сошлось на моих выборках.

Взялся писать, потому что люблю Postgres и никто не предложил решения с подобной логикой, было интересно, насколько компактным можно сделать решение с данным подходом.

… И это замечательно. Полностью Вас поддерживаю, мне тоже было интересно, что получится из этого подхода.

«Программист сам должен ...» — это был ни разу не наезд, это как раз была ирония. А то ведь следующим шагом понадобится оформить ТЗ…

Надо мне, наверное, больше использовать смаликов, а то заминусуют. (:
я не воспринял как наезд, больше как совет, в целом верный.
UFO just landed and posted this here
Круто. Но потерялись периоды, совсем не попадающие на рабочее время.
UFO just landed and posted this here

Заявку заводит человек внешний к тому кто работает в рамках рабочих часов SLA.
Клиент тех поддержки написал что у него ничего не работает, а потом понял что у него локальная проблема и закрыл её.


А сам пример, с моей точки зрения, намекает что нужно для таких интервалов строку в результате всё же показывать.
А вот, например, изначально вообще не выводил такой интервал в результате.

UFO just landed and posted this here

У меня в select условий нет.
А самый сложный where — для рассчёта рабочих дней. В реальном проекте я бы материализовал таблицу с рабочими днями, так что этот where ушёл бы в другое место.

Во-первых, в SLA указывают время, когда предоставляется сервис, а не рабочее время исполнителя. Это может совсем не совпадать, хотя удобнее конечно, чтобы графики рабочего времени если не совпадали, то по крайней мере покрывали время в SLA, а то работать будет некому. Но никто не мешает нам оказывать какие-то услуги, например, только в первой половине дня.

Во-вторых, у нас может быть гибкий график. Или в рабочее время человек убежал по своим делам, а вечером задержался (или утром пораньше начал), чтобы успеть доделать всё вовремя.

В-третьих, у нас могут быть офисы в разных часовых поясах. Или в разных городах. В городах поменьше любят график работы с 09 до 18, а в Москве-Питере с 10 до 19.

В-четвёртых, ночной дежурный скучал, от нечего делать подчистил всякие недозакрытые задачки. А может и какую-то свою работу доделал и закрыл.

В-пятых, клиент обратился в нерабочее время, а потом понял, что ложная тревога, или сам справился и закрыл.

В-шестых, бывают запланированные работы в нерабочее время. Каждый dba знает, что кое-что надо делать в отсутствие пользователей.

В-седьмых, иногда в IT бывают авралы, когда просто нужно сделать. Лучше, когда это редко (а ещё лучше если никогда), но иногда бывает.

В-восьмых, пришёл робот и автоматически поменял статусы, просроченное по таймаутам закрыл.

Ну короче не только по пьяни в нерабочее время что-то происходит на работе. Надеюсь, достаточно привёл жизненных примеров. (:
Вот мой вариант
-- https://habr.com/ru/company/postgrespro/blog/448368/
with periods(id, start_time, stop_time) as (
  values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
        (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
        (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
        (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),

-- http://data.gov.ru/opendata/resource/8ba5011a-233e-4e01-a1d2-ff5598d0f34f#2/0.0/0.0
-- All this block can be persisted in real table
holidays_raw(year, jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec) as (
  values(2018,'1,2,3,4,5,6,7,8,13,14,20,21,27,28','3,4,10,11,17,18,22*,23,24,25','3,4,7*,8,9,10,11,17,18,24,25,31','1,7,8,14,15,21,22,28*,29,30','1,2,5,6,8*,9,12,13,19,20,26,27','2,3,9*,10,11,12,16,17,23,24,30','1,7,8,14,15,21,22,28,29','4,5,11,12,18,19,25,26','1,2,8,9,15,16,22,23,29,30','6,7,13,14,20,21,27,28','3,4,5,10,11,17,18,24,25','1,2,8,9,15,16,22,23,29*,30,31'),
        (2019,'1,2,3,4,5,6,7,8,12,13,19,20,26,27','2,3,9,10,16,17,22*,23,24','2,3,7*,8,9,10,16,17,23,24,30,31','6,7,13,14,20,21,27,28,30*','1,2,3,4,5,8*,9,10,11,12,18,19,25,26','1,2,8,9,11*,12,15,16,22,23,29,30','6,7,13,14,20,21,27,28','3,4,10,11,17,18,24,25,31','1,7,8,14,15,21,22,28,29','5,6,12,13,19,20,26,27','2,3,4,9,10,16,17,23,24,30','1,7,8,14,15,21,22,28,29,31*')
),
holidays_by_month(year, month, day) as (
  select year, 1, unnest(string_to_array(jan, ',')) from holidays_raw
  union all
  select year, 2, unnest(string_to_array(feb, ',')) from holidays_raw
  union all
  select year, 3, unnest(string_to_array(mar, ',')) from holidays_raw
  union all
  select year, 4, unnest(string_to_array(apr, ',')) from holidays_raw
  union all
  select year, 5, unnest(string_to_array(may, ',')) from holidays_raw
  union all
  select year, 6, unnest(string_to_array(jun, ',')) from holidays_raw
  union all
  select year, 7, unnest(string_to_array(jul, ',')) from holidays_raw
  union all
  select year, 8, unnest(string_to_array(aug, ',')) from holidays_raw
  union all
  select year, 9, unnest(string_to_array(sep, ',')) from holidays_raw
  union all
  select year, 10, unnest(string_to_array(oct, ',')) from holidays_raw
  union all
  select year, 11, unnest(string_to_array(nov, ',')) from holidays_raw
  union all
  select year, 12, unnest(string_to_array(dec, ',')) from holidays_raw
),
holidays(day) as (
  select concat(year, '-', month, '-', day)::date
  from holidays_by_month
  -- Звёздочкой помечены сокращённые дни, но по условию задачи они считаются рабочими
  where day NOT like '%*'
),

-- Календарь всех дней на нужный период
calendar(day) as (
  select generate_series(min(start_time), max(stop_time), '1 day')::date from periods
),

-- Только рабочие дни в рамках нашего календаря
workdays(day, begin, until) as (
  select C.day, C.day + '10 hours'::interval, C.day + '19 hours'::interval
  from calendar AS C
  -- todo Тут явно можно сделать оптимальнее
  where (C.day NOT IN (select day from holidays AS H where H.day = C.day))
    and (
      (EXTRACT(DOW FROM C.day) between 1 and 5)
      or
      -- Сокращённый день считается полностью рабочим даже если это суббота
      exists (
        select 1
        from holidays_by_month AS HH
        where HH.year = EXTRACT(YEAR FROM C.day)
          and HH.month = EXTRACT(MONTH FROM C.day)
          and HH.day = concat(EXTRACT(DAY FROM C.day), '*')
      )
    )
)

select P.id
-- , P.start_time, P.stop_time
-- , W.*
-- , LEAST(P.stop_time, W.until) - GREATEST(W.begin, P.start_time)
, to_char(max(P.start_time), 'YYYY-MM-DD HH24:MI:SS') as start_time
, to_char(max(P.stop_time), 'YYYY-MM-DD HH24:MI:SS') as stop_time
, to_char(sum(LEAST(P.stop_time, W.until) - GREATEST(W.begin, P.start_time)), 'HH24:MI:SS') as work_hrs
from periods AS P
left outer join workdays AS W ON (W.day between P.start_time::date AND P.stop_time::date)
group by P.id
order by 1, 4
;

Уверен что есть куда оптимизировать :)
Ну и календарь выходных, по хорошему, лучше персистить в БД чем рассчитывать при каждом запросе.


Результат
 id |     start_time      |      stop_time      | work_hrs 
----+---------------------+---------------------+----------
  1 | 2019-03-29 07:00:00 | 2019-04-08 14:00:00 | 58:00:00
  2 | 2019-04-10 07:00:00 | 2019-04-10 20:00:00 | 09:00:00
  3 | 2019-04-11 12:00:00 | 2019-04-12 16:07:12 | 13:07:12
  4 | 2018-12-28 12:00:00 | 2019-01-16 16:00:00 | 67:00:00

P.S.
Мой вариант работает на PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit.

Вот на таких данных:
('2018-08-20 17:55:57', '2018-08-21 02:45:09')


даёт неожиданный результат:
 2018-08-20 17:55:57 | 2018-08-21 02:45:09 | -06:-10:-48

Да, такой вариант не был учтён.


Запрос с модифицированным SELECT

https://rextester.com/QKMKGO22854


-- https://habr.com/ru/company/postgrespro/blog/448368/
with periods(id, start_time, stop_time) as (
  values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
        (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
        (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
        (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp),
        (15, '2019-01-01 21:00:00'::timestamp, '2019-01-01 21:00:00'::timestamp),
        (16, '2018-08-20 17:55:57'::timestamp, '2018-08-21 02:45:09'::timestamp)
),

-- http://data.gov.ru/opendata/resource/8ba5011a-233e-4e01-a1d2-ff5598d0f34f#2/0.0/0.0
-- All this block can be persisted in real table
holidays_raw(year, jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec) as (
  values(2018,'1,2,3,4,5,6,7,8,13,14,20,21,27,28','3,4,10,11,17,18,22*,23,24,25','3,4,7*,8,9,10,11,17,18,24,25,31','1,7,8,14,15,21,22,28*,29,30','1,2,5,6,8*,9,12,13,19,20,26,27','2,3,9*,10,11,12,16,17,23,24,30','1,7,8,14,15,21,22,28,29','4,5,11,12,18,19,25,26','1,2,8,9,15,16,22,23,29,30','6,7,13,14,20,21,27,28','3,4,5,10,11,17,18,24,25','1,2,8,9,15,16,22,23,29*,30,31'),
        (2019,'1,2,3,4,5,6,7,8,12,13,19,20,26,27','2,3,9,10,16,17,22*,23,24','2,3,7*,8,9,10,16,17,23,24,30,31','6,7,13,14,20,21,27,28,30*','1,2,3,4,5,8*,9,10,11,12,18,19,25,26','1,2,8,9,11*,12,15,16,22,23,29,30','6,7,13,14,20,21,27,28','3,4,10,11,17,18,24,25,31','1,7,8,14,15,21,22,28,29','5,6,12,13,19,20,26,27','2,3,4,9,10,16,17,23,24,30','1,7,8,14,15,21,22,28,29,31*')
),
holidays_by_month(year, month, day) as (
  select year, 1, unnest(string_to_array(jan, ',')) from holidays_raw
  union all
  select year, 2, unnest(string_to_array(feb, ',')) from holidays_raw
  union all
  select year, 3, unnest(string_to_array(mar, ',')) from holidays_raw
  union all
  select year, 4, unnest(string_to_array(apr, ',')) from holidays_raw
  union all
  select year, 5, unnest(string_to_array(may, ',')) from holidays_raw
  union all
  select year, 6, unnest(string_to_array(jun, ',')) from holidays_raw
  union all
  select year, 7, unnest(string_to_array(jul, ',')) from holidays_raw
  union all
  select year, 8, unnest(string_to_array(aug, ',')) from holidays_raw
  union all
  select year, 9, unnest(string_to_array(sep, ',')) from holidays_raw
  union all
  select year, 10, unnest(string_to_array(oct, ',')) from holidays_raw
  union all
  select year, 11, unnest(string_to_array(nov, ',')) from holidays_raw
  union all
  select year, 12, unnest(string_to_array(dec, ',')) from holidays_raw
),
holidays(day) as (
  select concat(year, '-', month, '-', day)::date
  from holidays_by_month
  -- Звёздочкой помечены сокращённые дни, но по условию задачи они считаются рабочими
  where day NOT like '%*'
),

-- Календарь всех дней на нужный период
calendar(day) as (
  select generate_series(min(start_time), max(stop_time), '1 day')::date from periods
),

-- Только рабочие дни в рамках нашего календаря
workdays(day, begin, until) as (
  select C.day, C.day + '10 hours'::interval, C.day + '19 hours'::interval
  from calendar AS C
  -- todo Тут явно можно сделать оптимальнее
  where (C.day NOT IN (select day from holidays AS H where H.day = C.day))
    and (
      (EXTRACT(DOW FROM C.day) between 1 and 5)
      or
      -- Сокращённый день считается полностью рабочим даже если это суббота
      exists (
        select 1
        from holidays_by_month AS HH
        where HH.year = EXTRACT(YEAR FROM C.day)
          and HH.month = EXTRACT(MONTH FROM C.day)
          and HH.day = concat(EXTRACT(DAY FROM C.day), '*')
      )
    )
)

select P.id
-- , P.start_time, P.stop_time
-- , W.*
-- , GREATEST('0'::interval, LEAST(P.stop_time, W.until) - GREATEST(W.begin, P.start_time))
, to_char(max(P.start_time), 'YYYY-MM-DD HH24:MI:SS') as start_time
, to_char(max(P.stop_time), 'YYYY-MM-DD HH24:MI:SS') as stop_time
, to_char(sum(GREATEST('0'::interval, LEAST(P.stop_time, W.until) - GREATEST(W.begin, P.start_time))), 'HH24:MI:SS') as work_hrs
from periods AS P
left outer join workdays AS W ON (W.day between P.start_time::date AND P.stop_time::date)
group by P.id
order by 1, 4

Результат
 id |     start_time      |      stop_time      | work_hrs 
----+---------------------+---------------------+----------
  1 | 2019-03-29 07:00:00 | 2019-04-08 14:00:00 | 58:00:00
  2 | 2019-04-10 07:00:00 | 2019-04-10 20:00:00 | 09:00:00
  3 | 2019-04-11 12:00:00 | 2019-04-12 16:07:12 | 07:00:00
  4 | 2018-12-28 12:00:00 | 2019-01-16 16:00:00 | 67:00:00
 15 | 2019-01-01 21:00:00 | 2019-01-01 21:00:00 | 00:00:00
 16 | 2018-08-20 17:55:57 | 2018-08-21 02:45:09 | 01:04:03
Почти. Но ('2018-06-11 19:58:40', '2018-06-11 20:02:01') даёт 00:03:21, а должно быть 00:00:00.
На короткой выборке работает правильно, но на боевых даёт ошибку.

На каких данных некорректно работает пока не понял. Чуть позже отвечу, где ошибка.
with periods (id, start_time, stop_time) as (
    values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp), 
          (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp), 
          (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
          (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
)
select periods.*, hours from (
    select id, sum(upper(hours) - lower(hours)) hours
    from (
        select working_day::timestamp from periods
        join generate_series(start_time::date, stop_time::date, '1 day') working_day on extract(isodow from working_day::timestamp) < 6
        except -- holidays
        select * from unnest('{2019-01-01,2019-01-02,2019-01-03,2019-01-04,2019-01-07,2019-01-08,2019-03-08,2019-05-01,2019-05-02,2019-05-03,2019-05-09,2019-05-10,2019-06-12,2019-11-04,2018-01-01,2018-01-02,2018-01-03,2018-01-04,2018-01-05,2018-01-08,2018-02-23,2018-03-08,2018-03-09,2018-04-30,2018-05-01,2018-05-02,2018-05-09,2018-06-11,2018-06-12,2018-11-05,2018-12-31}'::date[])
        union -- working weekends
        select * from unnest('{2018-04-28,2018-06-09,2018-12-29}'::date[])
    )_, tsrange(working_day + '10:00', working_day + '19:00') working_time
    join (periods cross join tsrange(start_time, stop_time) period_range) on period_range && working_time,
    tsrange(period_range * working_time) hours
    group by 1
)_ join periods using (id)
прошу прощения, в первом ответе не закрепил под спойлер.
либо то же самое приклеить к периодам контекстно
with periods (id, start_time, stop_time) as (
    values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp), 
          (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp), 
          (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
          (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
)
select *, (
    select sum(upper(hours) - lower(hours)) hours
    from generate_series(start_time::date, stop_time::date, '1 day')_, "timestamp"(_) period_day,
    tsrange(period_day + '10:00', period_day + '19:00') working_time
    join tsrange(start_time, stop_time) period_range on period_range && working_time,
    tsrange(period_range * working_time) hours
    where 
    	extract(isodow from period_day) < 6
        and period_day <> all('{2019-01-01,2019-01-02,2019-01-03,2019-01-04,2019-01-07,2019-01-08,2019-03-08,2019-05-01,2019-05-02,2019-05-03,2019-05-09,2019-05-10,2019-06-12,2019-11-04,2018-01-01,2018-01-02,2018-01-03,2018-01-04,2018-01-05,2018-01-08,2018-02-23,2018-03-08,2018-03-09,2018-04-30,2018-05-01,2018-05-02,2018-05-09,2018-06-11,2018-06-12,2018-11-05,2018-12-31}')
        or period_day = any('{2018-04-28,2018-06-09,2018-12-29}')
) from periods

Круто, оба решения работают. Использование tsrange, all, any позволило сделать очень компактный запрос.
тут в идеале бы ещё знать, как представлен календарь праздников и рабочих уикендов, так как параметрически их вряд ли передают в запрос. задача была бы более приближена к реальной, а так решение выглядит несколько искусственно.
В реальной жизни может быть и так, и эдак. В зависимости от того, разовый ли это запрос или регулярный. Я специально не стал конкретизировать представление календаря, чтобы посмотреть на полёт мысли. Вот, например, Ваше представление мне очень понравилось. Компактнее ни у кого не получилось.
Красиво применили функцию-тип «timestamp», я и не знал, что так можно в Postgres
Это неявный lateral cross join, ввиду того что функция приведения типа напрямую не применима на функцию, возвращающую множество. На самом деле, можно было обойтись без этого, заменив
эту часть запроса
from generate_series(start_time::date, stop_time::date, '1 day')_, "timestamp"(_) period_day,
на такой вариант
from generate_series(date_trunc('day', start_time), date_trunc('day', stop_time), '1 day') period_day,
или такой вариант
from generate_series(start_time::date::timestamp, stop_time::date::timestamp, '1 day') period_day,
чтобы функция generate_series сразу возвращала timestamp. Это бы ещё и облегчило запрос. Но… хорошая мысля приходит опосля. :)
UFO just landed and posted this here
Бинго! Логика верная, но из чьего-то запроса выше Вы скопировали неправильные данные для праздников и не указали все дополнительные рабочие дни. После того, как я поправил, результаты на моих тестовых выборках стали сходиться с моими результатами.
UFO just landed and posted this here
pg 9.5
with wt_periods(id, start_time, stop_time) as
(
values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
)

,wt_par as
(
select
id
,start_time :: timestamp as i_from_dt
,stop_time :: timestamp as i_to_dt
,10 :: numeric as i_start_slave_time
,19 :: numeric as i_stop_slave_time
from
wt_periods
)

,wt_gen_dt as
(
select
pa1.id
,pa1.i_from_dt
,pa1.i_to_dt
,pa1.i_start_slave_time
,pa1.i_stop_slave_time
,dt
,case
when extract(dow from dt) in (6, 0)
or sh1.day_off is not null
then
0
else
1
end as is_slave_day
from wt_par pa1
cross join generate_series(
date_trunc('day', i_from_dt)
, date_trunc('day', i_to_dt)
, '24 hours' :: interval
) as dt
left join otrsuser.stat_holidays sh1 --табличка с праздничными днями
on sh1.day_off = dt
)

select
id
,i_from_dt
,i_to_dt
,sum( case --дата конца раб дня
when is_slave_day = 0
then
dt + interval '0 hours'
else
greatest(
least(
dt + interval '1 hours' * i_stop_slave_time
, i_to_dt
)
, dt + interval '1 hours' * i_start_slave_time
, i_from_dt
)
end
— case --дата начала раб дня
when is_slave_day = 0
then
dt + interval '0 hours'
else
greatest(
dt + interval '1 hours' * i_start_slave_time
, i_from_dt
)
end ) as sum_slave_mi
from
wt_gen_dt
group by
id
,i_from_dt
,i_to_dt
Табличка otrsuser.stat_holidays не найдена.

Ещё и формат поехал. Переписал. Но, думаю, это решение довольно популярно


Заголовок спойлера
with wt_periods(id, start_time, stop_time) as 
    (
        values  (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp)
              , (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp)
              , (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp)
              , (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
    )

    ,wt_holyday(id, day_off, comment) as
    (
        values  (79,  '2018-01-01 00:00:00' :: timestamp, 'НГ')
              , (80,  '2018-01-02 00:00:00' :: timestamp, 'НГ')
              , (81,  '2018-01-03 00:00:00' :: timestamp, 'НГ')
              , (82,  '2018-01-04 00:00:00' :: timestamp, 'НГ')
              , (83,  '2018-01-05 00:00:00' :: timestamp, 'НГ')
              , (84,  '2018-01-08 00:00:00' :: timestamp, 'НГ')
              , (85,  '2018-02-23 00:00:00' :: timestamp, 'ДЗО')
              , (86,  '2018-03-08 00:00:00' :: timestamp, 'МЖД')
              , (87,  '2018-03-09 00:00:00' :: timestamp, 'МЖД')
              , (88,  '2018-04-30 00:00:00' :: timestamp, 'Первомай')
              , (89,  '2018-05-01 00:00:00' :: timestamp, 'Первомай')
              , (90,  '2018-05-02 00:00:00' :: timestamp, 'Первомай')
              , (91,  '2018-05-09 00:00:00' :: timestamp, 'День Победы')
              , (92,  '2018-06-11 00:00:00' :: timestamp, 'День России')
              , (93,  '2018-06-12 00:00:00' :: timestamp, 'День России')
              , (94,  '2018-11-05 00:00:00' :: timestamp, 'День народного единства')
              , (95,  '2018-12-31 00:00:00' :: timestamp, 'НГ')
              , (96,  '2019-01-01 00:00:00' :: timestamp, 'НГ')
              , (97,  '2019-01-02 00:00:00' :: timestamp, 'НГ')
              , (98,  '2019-01-03 00:00:00' :: timestamp, 'НГ')
              , (99,  '2019-01-04 00:00:00' :: timestamp, 'НГ')
              , (100, '2019-01-07 00:00:00' :: timestamp, 'НГ')
              , (101, '2019-01-08 00:00:00' :: timestamp, 'НГ')
              , (102, '2019-03-08 00:00:00' :: timestamp, 'МЖД')
              , (103, '2019-05-01 00:00:00' :: timestamp, 'Первомай')
              , (104, '2019-05-02 00:00:00' :: timestamp, 'Первомай')
              , (105, '2019-05-03 00:00:00' :: timestamp, 'Первомай')
              , (106, '2019-05-09 00:00:00' :: timestamp, 'День Победы')
              , (107, '2019-05-10 00:00:00' :: timestamp, 'День Победы')
              , (108, '2019-06-12 00:00:00' :: timestamp, 'День России')
              , (109, '2019-11-04 00:00:00' :: timestamp, 'День народного единства')
    )

    ,wt_par as
    (
        select
             id
            ,start_time :: timestamp as i_from_dt
            ,stop_time :: timestamp as i_to_dt
            ,10 :: numeric as i_start_slave_time
            ,19 :: numeric as i_stop_slave_time
        from
            wt_periods
    )

    ,wt_gen_dt as
    (
        select
            pa1.id
            ,pa1.i_from_dt
            ,pa1.i_to_dt
            ,pa1.i_start_slave_time
            ,pa1.i_stop_slave_time
            ,dt 
            ,case when extract(dow from dt) in (6, 0) or sh1.day_off is not null then 0 else 1 end  as is_slave_day
        from wt_par pa1
            cross join generate_series(
                                        date_trunc('day', i_from_dt)
                                      , date_trunc('day', i_to_dt)
                                      , '24 hours' :: interval
                                      ) as dt
            left join wt_holyday  sh1 --табличка с праздничными днями
                on sh1.day_off = dt
    )

select
     id
    ,i_from_dt
    ,i_to_dt
    ,sum( case --дата конца раб дня 
              when is_slave_day = 0
                  then
                      dt + interval '0 hours'
                  else
                      greatest(
                                least(
                                       dt + interval '1 hours' * i_stop_slave_time
                                     , i_to_dt
                                     )
                              , dt + interval '1 hours' * i_start_slave_time
                              , i_from_dt
                              )
          end
          -
          case --дата начала раб дня 
              when is_slave_day = 0
                  then
                      dt + interval '0 hours'
                  else
                      greatest(
                                dt + interval '1 hours' * i_start_slave_time
                              , i_from_dt
                              )
          end )  as sum_slave_mi
from
    wt_gen_dt
group by
     id
    ,i_from_dt
    ,i_to_dt
Не учитываете никак дополнительные рабочие дни. Например, 2018-04-28 был рабочим днём. Поэтому на моих выборках результаты не сходятся.

Подход может и не оригинален, но всё равно спасибо за участие. Не так много народу вообще показало работающие запросы.

Работу в выходные учёл. Можно ещё и обеды учесть, если надо


Заголовок спойлера
with wt_periods(id, start_time, stop_time) as 
    (
        values  (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp)
              , (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp)
              , (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp)
              , (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
    )

    ,wt_holyday(id, day_off, comment) as
    (
        values  (79,  '2018-01-01 00:00:00' :: timestamp, 'НГ')
              , (80,  '2018-01-02 00:00:00' :: timestamp, 'НГ')
              , (81,  '2018-01-03 00:00:00' :: timestamp, 'НГ')
              , (82,  '2018-01-04 00:00:00' :: timestamp, 'НГ')
              , (83,  '2018-01-05 00:00:00' :: timestamp, 'НГ')
              , (84,  '2018-01-08 00:00:00' :: timestamp, 'НГ')
              , (85,  '2018-02-23 00:00:00' :: timestamp, 'ДЗО')
              , (86,  '2018-03-08 00:00:00' :: timestamp, 'МЖД')
              , (87,  '2018-03-09 00:00:00' :: timestamp, 'МЖД')
              , (88,  '2018-04-30 00:00:00' :: timestamp, 'Первомай')
              , (89,  '2018-05-01 00:00:00' :: timestamp, 'Первомай')
              , (90,  '2018-05-02 00:00:00' :: timestamp, 'Первомай')
              , (91,  '2018-05-09 00:00:00' :: timestamp, 'День Победы')
              , (92,  '2018-06-11 00:00:00' :: timestamp, 'День России')
              , (93,  '2018-06-12 00:00:00' :: timestamp, 'День России')
              , (94,  '2018-11-05 00:00:00' :: timestamp, 'День народного единства')
              , (95,  '2018-12-31 00:00:00' :: timestamp, 'НГ')
              , (96,  '2019-01-01 00:00:00' :: timestamp, 'НГ')
              , (97,  '2019-01-02 00:00:00' :: timestamp, 'НГ')
              , (98,  '2019-01-03 00:00:00' :: timestamp, 'НГ')
              , (99,  '2019-01-04 00:00:00' :: timestamp, 'НГ')
              , (100, '2019-01-07 00:00:00' :: timestamp, 'НГ')
              , (101, '2019-01-08 00:00:00' :: timestamp, 'НГ')
              , (102, '2019-03-08 00:00:00' :: timestamp, 'МЖД')
              , (103, '2019-05-01 00:00:00' :: timestamp, 'Первомай')
              , (104, '2019-05-02 00:00:00' :: timestamp, 'Первомай')
              , (105, '2019-05-03 00:00:00' :: timestamp, 'Первомай')
              , (106, '2019-05-09 00:00:00' :: timestamp, 'День Победы')
              , (107, '2019-05-10 00:00:00' :: timestamp, 'День Победы')
              , (108, '2019-06-12 00:00:00' :: timestamp, 'День России')
              , (109, '2019-11-04 00:00:00' :: timestamp, 'День народного единства')
    )

    ,wt_slave_holyday(id, day_on, comment) as
    (
        values  (201,  '2018-04-28 00:00:00' :: timestamp, 'Из-за первомая')
              , (202,  '2018-06-09 00:00:00' :: timestamp, 'Из-за дня России')
              , (203,  '2018-12-29 00:00:00' :: timestamp, 'Из-за НГ')
    )

    ,wt_par as
    (
        select
             id
            ,start_time :: timestamp  as i_from_dt
            ,stop_time  :: timestamp  as i_to_dt
            ,10 :: numeric  as i_start_slave_time
            ,19 :: numeric  as i_stop_slave_time
        from
            wt_periods
    )

    ,wt_gen_dt as
    (
        select
             pa1.id
            ,pa1.i_from_dt
            ,pa1.i_to_dt
            ,pa1.i_start_slave_time
            ,pa1.i_stop_slave_time
            ,dt 
            ,case
                 when sh1.day_on is not null
                     then
                         1
                 when sh1.day_on is null
                  and (
                            extract(dow from dt) in (6, 0)
                        or  h1.day_off is not null
                      )
                     then
                         0
                     else
                         1
             end  as is_slave_day
        from wt_par  pa1
            cross join generate_series(
                                        date_trunc('day', pa1.i_from_dt)
                                      , date_trunc('day', pa1.i_to_dt)
                                      , '24 hours' :: interval
                                      )  as dt
            left join wt_holyday  h1 --табличка с праздничными днями
                on h1.day_off = dt

            left join wt_slave_holyday  sh1
                on sh1.day_on = dt
    )

select
     id
    ,i_from_dt
    ,i_to_dt
    ,sum( case --дата конца раб дня 
              when is_slave_day = 0
                  then
                      dt + interval '0 hours'
                  else
                      greatest(
                                least(
                                       dt + interval '1 hours' * i_stop_slave_time
                                     , i_to_dt
                                     )
                              , dt + interval '1 hours' * i_start_slave_time
                              , i_from_dt
                              )
          end
          -
          case --дата начала раб дня 
              when is_slave_day = 0
                  then
                      dt + interval '0 hours'
                  else
                      greatest(
                                dt + interval '1 hours' * i_start_slave_time
                              , i_from_dt
                              )
          end )  as slave_time_mi
from
    wt_gen_dt
group by
     id
    ,i_from_dt
    ,i_to_dt
Бинго! Теперь на моих тестах сходится.

А обедов в SLA не было. (:

У меня в расчёте SLA учитываются и обеды, и сокращённые пятницы, и сокращённые предпраздничные дни, и по разным типам (запрос или инцидент) эти условия разные.
В общем, этот запрос — это мой стандартный, не самый сложный, рабочий день

решение с timestamp range
Решение
with periods(id, start_time, stop_time) as (
    values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
           (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
           (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
           (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
     holidays(period) as (
         values (tsrange('2018-01-01 00:00:00', '2018-01-08 23:59:59')),
                (tsrange('2018-02-23 00:00:00', '2018-02-23 23:59:59')),
                (tsrange('2018-03-08 00:00:00', '2018-03-09 23:59:59')),
                (tsrange('2018-04-30 00:00:00', '2018-05-02 23:59:59')),
                (tsrange('2018-05-09 00:00:00', '2018-05-09 23:59:59')),
                (tsrange('2018-06-11 00:00:00', '2018-06-12 23:59:59')),
                (tsrange('2018-11-05 00:00:00', '2018-11-05 23:59:59')),
                (tsrange('2018-12-31 00:00:00', '2019-01-08 23:59:59')),
                (tsrange('2019-03-08 00:00:00', '2019-03-08 23:59:59')),
                (tsrange('2019-05-01 00:00:00', '2019-05-03 23:59:59')),
                (tsrange('2019-05-09 00:00:00', '2019-05-10 23:59:59')),
                (tsrange('2019-06-12 00:00:00', '2019-06-12 23:59:59')),
                (tsrange('2019-11-04 00:00:00', '2019-11-04 23:59:59')),
                (tsrange('2019-11-04 00:00:00', '2019-11-04 23:59:59'))
     ),
     transfered_work_days(d) as (
         values ('2018-04-28'),
                ('2018-06-09'),
                ('2018-12-29')
     )
select id, sum(work_time)
from (
         select id,
                least(stop_time, d::date + '19h'::interval)::timestamp -
                greatest(start_time, d::date + '10h'::interval)::timestamp as work_time
         from (
                  select id, generate_series(start_time::date, stop_time::date, '1d'::interval) d, start_time, stop_time from periods
              ) as days
         where (exists(select true from transfered_work_days twd where twd.d::date = days.d::date limit 1) or
                extract(isodow from days.d) not in (6, 7))
           and not exists(select true from holidays where period @> (days.d::timestamp) limit 1)
     ) _
group by id
order by id

Извините, что долго не отвечал на Ваше решение, что-то оно выпало из моего поля зрения.

Спасибо, за участие, но есть недочёты:
1) теряются из вывода периоды, не попадающие на рабочее время, и
2) некорректная работа на периодах типа ('2019-05-23 16:51:40', '2019-05-24 02:55:50').

Поправил по замечаниям


Заголовок спойлера
with periods(id, start_time, stop_time) as (
    values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
           (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
           (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
           (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp),
           (5, '2019-05-23 16:51:40'::timestamp, '2019-05-24 02:55:50'::timestamp),
           (6, '2019-05-25 19:51:40'::timestamp, '2019-05-25 23:55:50'::timestamp)
),
     holidays(period) as (
         values (tsrange('2018-01-01 00:00:00', '2018-01-08 23:59:59')),
                (tsrange('2018-02-23 00:00:00', '2018-02-23 23:59:59')),
                (tsrange('2018-03-08 00:00:00', '2018-03-09 23:59:59')),
                (tsrange('2018-04-30 00:00:00', '2018-05-02 23:59:59')),
                (tsrange('2018-05-09 00:00:00', '2018-05-09 23:59:59')),
                (tsrange('2018-06-11 00:00:00', '2018-06-12 23:59:59')),
                (tsrange('2018-11-05 00:00:00', '2018-11-05 23:59:59')),
                (tsrange('2018-12-31 00:00:00', '2019-01-08 23:59:59')),
                (tsrange('2019-03-08 00:00:00', '2019-03-08 23:59:59')),
                (tsrange('2019-05-01 00:00:00', '2019-05-03 23:59:59')),
                (tsrange('2019-05-09 00:00:00', '2019-05-10 23:59:59')),
                (tsrange('2019-06-12 00:00:00', '2019-06-12 23:59:59')),
                (tsrange('2019-11-04 00:00:00', '2019-11-04 23:59:59')),
                (tsrange('2019-11-04 00:00:00', '2019-11-04 23:59:59'))
     ),
     transfered_work_days(d) as (
         values ('2018-04-28'),
                ('2018-06-09'),
                ('2018-12-29')
     )
select p.*, sum(work_time)
from periods p
left join (
         select id,
                greatest(d::date + '10h'::interval, least(stop_time, d::date + '19h'::interval))::timestamp -
                least(d::date + '19h'::interval, greatest(start_time, d::date + '10h'::interval))::timestamp as work_time
         from (
                  select id, generate_series(start_time::date, stop_time::date, '1d'::interval) d, start_time, stop_time from periods
              ) as days
         where (exists(select true from transfered_work_days twd where twd.d::date = days.d::date limit 1) or
                extract(isodow from days.d) not in (6, 7))
           and not exists(select true from holidays where period @> (days.d::timestamp) limit 1)
     ) wt on wt.id = p.id
group by p.id, p.start_time, p.stop_time
order by p.id
Бинго! Сошлось на моих данных. Забавно, что получилось на части периодов, которые не попали на рабочее время, насчиталось рабочих часов 00:00:00, а на части NULL.

ХМ) А можно интервал на котором такая проблема есть? а то не хочется решать следствие коалеском)

Например, на таком ('2019-05-18 11:30:13', '2019-05-19 09:09:01') даёт NULL. Похоже, что это когда start_time и stop_time выпадают на одни и те же выходные, и тогда подзапрос в итоговом select-е не возвращает ни одной записи.
Так вижу
with 
periods(id, start_time, stop_time) as ( values
  (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
  (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
  (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
  (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)	    
),
holidays(date_key) as ( values 
  ('2018-01-01'::date), ('2018-01-02'::date), ('2018-01-03'::date), 
  ('2018-01-04'::date), ('2018-01-05'::date), ('2018-01-08'::date), 
  ('2018-02-23'::date), ('2018-03-08'::date), ('2018-03-09'::date), 
  ('2018-04-30'::date), ('2018-05-01'::date), ('2018-05-02'::date), 
  ('2018-05-09'::date), ('2018-06-11'::date), ('2018-06-12'::date), 
  ('2018-11-05'::date), ('2018-12-31'::date), 
  ('2019-01-01'::date), ('2019-01-02'::date), ('2019-01-03'::date), 
  ('2019-01-04'::date), ('2019-01-07'::date), ('2019-01-08'::date), 
  ('2019-03-08'::date), ('2019-05-01'::date), ('2019-05-02'::date), 
  ('2019-05-03'::date), ('2019-05-09'::date), ('2019-05-10'::date), 
  ('2019-06-12'::date), ('2019-11-04'::date)
),
work_holidays(date_key) as ( values 
  ('2018-04-28'::date), ('2018-06-09'::date), ('2018-12-29'::date)
)
select p.id, p.start_time, p.stop_time,        
       -- hours from start_time
       case 
         when start_time::date = min(h.time_key)  then '00:00:00'::interval
         when start_time::date = stop_time::date  then '00:00:00'::interval
	 when start_time::time > '19:00:00'::time then '00:00:00'::interval
	 when start_time::time < '10:00:00'::time then '09:00:00'::interval
	 else '19:00:00'::time - start_time::time
       end +
       -- hours from stop_time
       case 	        
         when stop_time::date = max(h.time_key)  then '00:00:00'::interval
	 when stop_time::date = start_time::date then least('19:00:00'::time,  stop_time::time) - greatest('10:00:00'::time, start_time::time)
	 when stop_time::time > '19:00:00'::time then '09:00:00'::interval
         when stop_time::time < '10:00:00'::time then '00:00:00'::interval
	 else stop_time::time - '10:00:00'::time
       end +
        -- 
       (
         -- count all days
	 greatest(p.stop_time::date - p.start_time::date - 1, 0) 
         -- count holidays 
	 -sum(case when h.time_key > p.start_time::date and h.time_key < p.stop_time::date then 1 else 0 end)
       )*'09:00:00'::interval as answer
from periods p
left outer join
(
  select time_key::date
  from generate_series(
    (select min(start_time)::date from periods), 
    (select max(stop_time)::date from periods), 
    interval '1 day'
  ) as time_key
  where to_char(time_key, 'D') in ('1', '7')
  union 
  select date_key
  from holidays
  except
  select date_key
  from work_holidays
) h	 
on h.time_key between p.start_time::date and p.stop_time::date
group by p.id, p.start_time, p.stop_time
order by p.id


Близко! Но показывает странноватый результат, например, на таком периоде: ('2019-05-23 19:00:57', '2019-05-23 21:45:09') — минус 57 секунд.
Да, действительно упустил один момент.
Протер глаза
with 
periods(id, start_time, stop_time) as ( values
  (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
  (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
  (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
  (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
holidays(date_key) as ( values 
  ('2018-01-01'::date), ('2018-01-02'::date), ('2018-01-03'::date), ('2018-01-04'::date), ('2018-01-05'::date), ('2018-01-08'::date), ('2018-02-23'::date), ('2018-03-08'::date), ('2018-03-09'::date), ('2018-04-30'::date), ('2018-05-01'::date), ('2018-05-02'::date), ('2018-05-09'::date), ('2018-06-11'::date), ('2018-06-12'::date), ('2018-11-05'::date), ('2018-12-31'::date), 
  ('2019-01-01'::date), ('2019-01-02'::date), ('2019-01-03'::date), ('2019-01-04'::date), ('2019-01-07'::date), ('2019-01-08'::date), ('2019-03-08'::date), ('2019-05-01'::date), ('2019-05-02'::date), ('2019-05-03'::date), ('2019-05-09'::date), ('2019-05-10'::date), ('2019-06-12'::date), ('2019-11-04'::date)
),
work_holidays(date_key) as ( values 
  ('2018-04-28'::date), ('2018-06-09'::date), ('2018-12-29'::date)
)
select p.id, p.start_time, p.stop_time,  
       -- hours from start_time
       case 
         when start_time::date = stop_time::date or start_time::date = min(h.time_key) then '00:00:00'::interval      
         else '19:00:00'::time - greatest(least(start_time::time, '19:00:00'::time), '10:00:00'::time)      
       end +
       -- hours from stop_time
       case           
         when stop_time::date = max(h.time_key)  then '00:00:00'::interval
         when stop_time::date = start_time::date then greatest(least('19:00:00'::time, stop_time::time) - greatest('10:00:00'::time, start_time::time), '00:00:00'::interval)      
         else greatest(least(stop_time::time, '19:00:00'::time), '10:00:00'::time) - '10:00:00'::time
       end +
       -- 
       (
          greatest(p.stop_time::date - p.start_time::date - 1, 0) 
        - sum(case when h.time_key > p.start_time::date and h.time_key < p.stop_time::date then 1 else 0 end)
       )*'09:00:00'::interval as answer
from periods p
left outer join
(   -- all free days
  select time_key::date
  from generate_series(
    (select min(start_time)::date from periods), 
    (select max(stop_time)::date from periods), 
    interval '1 day'
  ) as time_key
  where to_char(time_key, 'D') in ('1', '7')
  union 
  select date_key
  from holidays
  except
  select date_key
  from work_holidays
) h   
on h.time_key between p.start_time::date and p.stop_time::date
group by p.id, p.start_time, p.stop_time

Бинго! Сошлось на моих выборках!
У меня вот что получилось. Таблица industrial_calendar имеет вид (id, date), где date — рабочие дни. Данные забираются с портала открытых данных РФ (data.gov.ru).
Код
with periods(id, start_time, stop_time) as (
  values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
        (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
        (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
        (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
)
select p.id,
       p.start_time as start_p,
       p.stop_time as end_p,
       sum((least((c.date || ' 19:00:00')::timestamp, p.stop_time) -
            greatest((c.date || ' 10:00:00')::timestamp, p.start_time))::time) as work_time
from periods p
       join industrial_calendar c on p.start_time::date <= c.date and p.stop_time::date >= c.date
group by p.id, p.start_time, p.stop_time

Собрал подзапрос для industrial_calendar, чтобы проверить решение. Есть ошибки в реализации. Например, на периоде ('2019-05-23 19:00:57', '2019-05-23 21:45:09') даёт 23:59:03, а должно быть 00:00:00. А период ('2019-04-28 21:00:00', '2019-04-28 21:00:00') потерялся.
Я понимаю, что здесь тусовка постгрес.
Я этим диалектом sql — не владею, и тем интереснее смотреть варианты. Познавательнее.
Победителей объявят?
Интересно будет глянуть код.
Для MSSQLSERVER, упрощенно, будет выглядеть так:
set language russian
;With [days]
as
(
	Select Cast('20180101' as date) [day]
	Union all
	Select DATEADD(day, 1, [days].[day])
	From [days]
	Where [days].[day] < '20191231'
)
,     [periods]
as
(
	Select *
	from (
	VALUES ( 1, Cast('2019-03-29 07:00:00' as datetime2), Cast('2019-04-08 14:00:00'as datetime2) )
	,      ( 2, '2019-04-10 07:00:00',                    '2019-04-10 20:00:00'                   )
	,      ( 3, '2019-04-11 12:00:00',                    '2019-04-12 16:07:12'                   )
	,      ( 4, '2018-12-28 12:00:00',                    '2019-01-16 16:00:00'                   )) t (id, start_time, stop_time)
)
,     holidays ( h_date )
as
(
	Select *
	from (
	values ( Cast('2018-01-01' as date) )
	,      ( '2018-01-02'               )
	,      ( '2018-01-03'               )
	,      ( '2018-01-04'               )
	,      ( '2018-01-05'               )
	,      ( '2018-01-07'               )
	,      ( '2018-01-08'               )
	,      ( '2018-02-23'               )
	,      ( '2018-03-08'               )
	,      ( '2018-05-01'               )
	,      ( '2018-05-09'               )
	,      ( '2018-06-12'               )
	,      ( '2018-11-04'               )
	,      ( '2019-01-01'               )
	,      ( '2019-01-02'               )
	,      ( '2019-01-03'               )
	,      ( '2019-01-04'               )
	,      ( '2019-01-05'               )
	,      ( '2019-01-07'               )
	,      ( '2019-01-08'               )
	,      ( '2019-02-23'               )
	,      ( '2019-03-08'               )
	,      ( '2019-05-01'               )
	,      ( '2019-05-09'               )
	,      ( '2019-06-12'               )
	,      ( '2019-11-04'               )
	) t(h_date)
)
Select a.*
,t.cnt * 9.0
+ Case when Cast(a.start_time as time) between '10:00:00' and '19:00:00' then Cast(datediff(minute, '19:00:00', Cast(a.start_time as time)) as float) / 60.0 
Else 0.0 End
- Case when Cast(a.stop_time as time) between '10:00:00' and '19:00:00' then Cast(datediff(minute, '19:00:00', Cast(a.stop_time as time)) as float) / 60.0 
Else 0.0 End
[work_time]
from [periods] a
	Outer apply 
		(Select count(*) 
			from [days] d
			left join holidays h on h.h_date = d.[day]			 
			Where d.[day] between Cast(a.start_time as date) and Cast(a.stop_time as date)
				and h.h_date is null
				and DATEPART(weekday, d.[day]) not in (6,7)
		) t(cnt)
Option (maxrecursion 0)


Скорее всего — не очень верно, т.к. не учтены перерывы на обед (они входят в рабочее время, интересно?)
Написал исходя из того, что рабочий день длится с 10 до 19 — 9 рабочих часов.
Допущения правильные, обед не учитывается, называется в быту режим 5х9 (в отличие от 24х7). Это вполне нормальный режим, просто обедать в разнобой ходить, не всем сразу.

Спасибо, что поделились своим вариантом. Действительно интересно сравнивать разные диалекты SQL. Но погонять на предмет ошибок не могу, не на чем.

Победителей объявлю, у меня же приз победителю заявлен.
UFO just landed and posted this here
Спасибо, конечно. Но хотелось бы не запустить в принципе, а погонять на своих данных.
Вообще, в таких задачах неплохо сразу оговаривать таймзоны, а то мало ли что. ;-) Исходил из того, что на дворе Москва.

Получилось postgres-specific, зато компактно. И почти без костылей.

Попытка №1
WITH
periods (id, start_time, stop_time) AS (
    VALUES
        (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp), 
        (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp), 
        (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
        (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
holidays (date) AS (
    VALUES
        ('2018-01-01'::DATE),
        ('2018-01-02'::DATE),
        ('2018-01-03'::DATE),
        ('2018-01-04'::DATE),
        ('2018-01-05'::DATE),
        ('2018-01-08'::DATE),
        ('2018-02-23'::DATE),
        ('2018-03-08'::DATE),
        ('2018-03-09'::DATE),
        ('2018-04-30'::DATE),
        ('2018-05-01'::DATE),
        ('2018-05-02'::DATE),
        ('2018-05-09'::DATE),
        ('2018-06-11'::DATE),
        ('2018-06-12'::DATE),
        ('2018-11-05'::DATE),
        ('2018-12-31'::DATE),
        ('2019-01-01'::DATE),
        ('2019-01-02'::DATE),
        ('2019-01-03'::DATE),
        ('2019-01-04'::DATE),
        ('2019-01-07'::DATE),
        ('2019-01-08'::DATE),
        ('2019-03-08'::DATE),
        ('2019-05-01'::DATE),
        ('2019-05-02'::DATE),
        ('2019-05-03'::DATE),
        ('2019-05-09'::DATE),
        ('2019-05-10'::DATE),
        ('2019-06-12'::DATE),
        ('2019-11-04'::DATE)
),
weekdays (date) AS (
    VALUES
        ('2018-04-28'::DATE),
        ('2018-06-09'::DATE),
        ('2018-12-29'::DATE)
),
durations AS (
    SELECT
        p.*,
        tsrange(p.start_time, p.stop_time) * tsrange(g.time + '10 HOURS'::INTERVAL, g.time + '19 HOURS'::INTERVAL) AS range
    FROM
        periods AS p
        LEFT JOIN generate_series(date_trunc('DAY', p.start_time), date_trunc('DAY', p.stop_time), '1 DAY') AS g (time) ON (TRUE)
        LEFT JOIN holidays AS h ON (h.date = g.time::DATE)
        LEFT JOIN weekdays AS w ON (w.date = g.time::DATE)
    WHERE
        (extract(DOW FROM g.time)::INT % 6 != 0 OR w.date IS NOT NULL)
        AND
        h.date IS NULL
)
SELECT
    d.id,
    d.start_time,
    d.stop_time,
    coalesce(sum(age(upper(d.range), lower(d.range))), '00:00') AS work_hrs
FROM
    durations AS d
GROUP BY
    1, 2, 3
ORDER BY
    1;

Пардон, забыл проверить на пустых диапазонах. Периоды выпадают.

Попытка №2
WITH
periods (id, start_time, stop_time) AS (
    VALUES
        (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp), 
        (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp), 
        (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
        (4, '2018-12-31 12:00:00'::timestamp, '2019-01-01 16:00:00'::timestamp)
),
holidays (date) AS (
    VALUES
        ('2018-01-01'::DATE),
        ('2018-01-02'::DATE),
        ('2018-01-03'::DATE),
        ('2018-01-04'::DATE),
        ('2018-01-05'::DATE),
        ('2018-01-08'::DATE),
        ('2018-02-23'::DATE),
        ('2018-03-08'::DATE),
        ('2018-03-09'::DATE),
        ('2018-04-30'::DATE),
        ('2018-05-01'::DATE),
        ('2018-05-02'::DATE),
        ('2018-05-09'::DATE),
        ('2018-06-11'::DATE),
        ('2018-06-12'::DATE),
        ('2018-11-05'::DATE),
        ('2018-12-31'::DATE),
        ('2019-01-01'::DATE),
        ('2019-01-02'::DATE),
        ('2019-01-03'::DATE),
        ('2019-01-04'::DATE),
        ('2019-01-07'::DATE),
        ('2019-01-08'::DATE),
        ('2019-03-08'::DATE),
        ('2019-05-01'::DATE),
        ('2019-05-02'::DATE),
        ('2019-05-03'::DATE),
        ('2019-05-09'::DATE),
        ('2019-05-10'::DATE),
        ('2019-06-12'::DATE),
        ('2019-11-04'::DATE)
),
weekdays (date) AS (
    VALUES
        ('2018-04-28'::DATE),
        ('2018-06-09'::DATE),
        ('2018-12-29'::DATE)
),
durations AS (
    SELECT
        p.id,
        tsrange(p.start_time, p.stop_time) * tsrange(g.time + '10 HOURS'::INTERVAL, g.time + '19 HOURS'::INTERVAL) AS range
    FROM
        periods AS p
        LEFT JOIN generate_series(date_trunc('DAY', p.start_time), date_trunc('DAY', p.stop_time), '1 DAY') AS g (time) ON (TRUE)
        LEFT JOIN holidays AS h ON (h.date = g.time::DATE)
        LEFT JOIN weekdays AS w ON (w.date = g.time::DATE)
    WHERE
        (extract(DOW FROM g.time)::INT % 6 != 0 OR w.date IS NOT NULL)
        AND
        h.date IS NULL
)
SELECT
    p.*,
    coalesce(sum(age(upper(d.range), lower(d.range))), '00:00') AS work_hrs
FROM
    periods AS p
    LEFT JOIN durations AS d USING (id)
GROUP BY
    1, 2, 3
ORDER BY
    1;

Бинго! Работает правильно на моих выборках. И это с 1.05 раза!
Версия под Oracle
with w_periods as
 (select 1 as id,
         to_date('2019-03-29 07:00:00', 'YYYY-MM-DD HH24:MI:SS') as start_time,
         to_date('2019-04-08 14:00:00', 'YYYY-MM-DD HH24:MI:SS') as stop_time
    from dual
  union all
  select 2 as id,
         to_date('2019-04-10 07:00:00', 'YYYY-MM-DD HH24:MI:SS') as start_time,
         to_date('2019-04-10 20:00:00', 'YYYY-MM-DD HH24:MI:SS') as stop_time
    from dual
  union all
  select 3,
         to_date('2019-04-11 12:00:00', 'YYYY-MM-DD HH24:MI:SS') as start_time,
         to_date('2019-04-12 16:07:12', 'YYYY-MM-DD HH24:MI:SS') as stop_time
    from dual
  union all
  select 4,
         to_date('2018-12-28 12:00:00', 'YYYY-MM-DD HH24:MI:SS') as start_time,
         to_date('2019-01-16 16:00:00', 'YYYY-MM-DD HH24:MI:SS') as stop_time
    from dual),
w_holidays as
 (select to_date('2018-01-01', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-01-02', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-01-03', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-01-04', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-01-05', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-01-07', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-01-08', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-02-23', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-03-08', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-04-30', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-05-01', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-05-02', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-05-09', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-06-12', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-11-04', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2019-01-01', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2019-01-02', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2019-01-03', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2019-01-04', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2019-01-05', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2019-01-07', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2019-01-08', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2019-02-23', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2019-03-08', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2019-05-01', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2019-05-09', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2019-06-12', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2019-11-04', 'YYYY-MM-DD') as dt
    from dual),
w_dates1 as
 (select v2.id, (count(v2.id) * 9) as work_hrs
    from (select v1.*
            from (select distinct (trunc(p.start_time) + level) as dt, p.id
                    from w_periods p
                   where trunc(p.stop_time - p.start_time) > 1
                  connect by level < trunc(p.stop_time - p.start_time)) v1
           where v1.dt not in (select dt from w_holidays)
             and trim(to_char(v1.dt, 'DAY')) not in ('SATURDAY', 'SUNDAY')) v2
   group by v2.id),
w_dates2 as
 (select v1.id,
         (case
           when (v1.start_time1 = v1.stop_time1) and (v1.start_time2 = v1.stop_time2) then
            (v1.start_time2 - v1.start_time1)
           else
            (v1.start_time2 - v1.start_time1) + (v1.stop_time2 - v1.stop_time1)
         end) * 24 as work_hrs
    from (select t.*,
                 (case
                   when (t.start_time < trunc_start_time + interval '10' hour) then
                    trunc_start_time + interval '10' hour
                   else
                    t.start_time
                 end) as start_time1,
                 (case
                   when (t.start_time < trunc_start_time + interval '19' hour) then
                    trunc_start_time + interval '19' hour
                   else
                    t.start_time
                 end) as start_time2,
                 (case
                   when (t.stop_time > trunc_stop_time + interval '10' hour) then
                    trunc_stop_time + interval '10' hour
                   else
                    t.stop_time
                 end) as stop_time1,
                 (case
                   when (t.stop_time > trunc_stop_time + interval '19' hour) then
                    trunc_stop_time + interval '19' hour
                   else
                    t.stop_time
                 end) as stop_time2
            from (select x.*, trunc(x.start_time) as trunc_start_time, trunc(x.stop_time) as trunc_stop_time from w_periods x) t) v1)
select p.*, v2.work_hrs
  from (select v1.id, sum(work_hrs) as work_hrs from (select * from w_dates1 union all select * from w_dates2) v1 group by v1.id) v2,
       w_periods p
 where v2.id = p.id


Версия под PostgreSQL
with w_periods(id, start_time, stop_time) as (
  values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp), 
        (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp), 
        (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
        (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
w_holidays as
 (select to_date('2018-01-01', 'YYYY-MM-DD') as dt
  union all
  select to_date('2018-01-02', 'YYYY-MM-DD')
  union all
  select to_date('2018-01-03', 'YYYY-MM-DD')
  union all
  select to_date('2018-01-04', 'YYYY-MM-DD')
  union all
  select to_date('2018-01-05', 'YYYY-MM-DD')
  union all
  select to_date('2018-01-07', 'YYYY-MM-DD')
  union all
  select to_date('2018-01-08', 'YYYY-MM-DD')
  union all
  select to_date('2018-02-23', 'YYYY-MM-DD')
  union all
  select to_date('2018-03-08', 'YYYY-MM-DD')
  union all
  select to_date('2018-04-30', 'YYYY-MM-DD')
  union all
  select to_date('2018-05-01', 'YYYY-MM-DD')
  union all
  select to_date('2018-05-02', 'YYYY-MM-DD')
  union all
  select to_date('2018-05-09', 'YYYY-MM-DD')
  union all
  select to_date('2018-06-12', 'YYYY-MM-DD')
  union all
  select to_date('2018-11-04', 'YYYY-MM-DD')
  union all
  select to_date('2019-01-01', 'YYYY-MM-DD')
  union all
  select to_date('2019-01-02', 'YYYY-MM-DD')
  union all
  select to_date('2019-01-03', 'YYYY-MM-DD')
  union all
  select to_date('2019-01-04', 'YYYY-MM-DD')
  union all
  select to_date('2019-01-05', 'YYYY-MM-DD')
  union all
  select to_date('2019-01-07', 'YYYY-MM-DD')
  union all
  select to_date('2019-01-08', 'YYYY-MM-DD')
  union all
  select to_date('2019-02-23', 'YYYY-MM-DD')
  union all
  select to_date('2019-03-08', 'YYYY-MM-DD')
  union all
  select to_date('2019-05-01', 'YYYY-MM-DD')
  union all
  select to_date('2019-05-09', 'YYYY-MM-DD')
  union all
  select to_date('2019-06-12', 'YYYY-MM-DD')
  union all
  select to_date('2019-11-04', 'YYYY-MM-DD')),
w_dates1 as
 (select v2.id, (count(v2.id) * 9) * '1 hours' ::interval as work_hrs
    from (select v1.*
            from (select id, dt from w_periods, generate_series(start_time ::date, stop_time ::date, '1 day') as dt) v1
           where v1.dt not in (select dt from w_holidays)
             and trim(to_char(v1.dt, 'DAY')) not in ('SATURDAY', 'SUNDAY')
             and v1.dt not in (select date_trunc('day', start_time) from w_periods)
             and v1.dt not in (select date_trunc('day', stop_time) from w_periods)) v2
   group by v2.id),
w_dates2 as
 (select v1.id,
         (case
           when (v1.start_time1 = v1.stop_time1) and (v1.start_time2 = v1.stop_time2) then
            (v1.start_time2 - v1.start_time1)
           else
            (v1.start_time2 - v1.start_time1) + (v1.stop_time2 - v1.stop_time1)
         end) as work_hrs
    from (select t.*,
                 (case
                   when (t.start_time < trunc_start_time + interval '10' hour) then
                    trunc_start_time + interval '10' hour
                   else
                    t.start_time
                 end) as start_time1,
                 (case
                   when (t.start_time < trunc_start_time + interval '19' hour) then
                    trunc_start_time + interval '19' hour
                   else
                    t.start_time
                 end) as start_time2,
                 (case
                   when (t.stop_time > trunc_stop_time + interval '10' hour) then
                    trunc_stop_time + interval '10' hour
                   else
                    t.stop_time
                 end) as stop_time1,
                 (case
                   when (t.stop_time > trunc_stop_time + interval '19' hour) then
                    trunc_stop_time + interval '19' hour
                   else
                    t.stop_time
                 end) as stop_time2
            from (select x.*, date_trunc('day', x.start_time) as trunc_start_time, date_trunc('day', x.stop_time) as trunc_stop_time
                    from w_periods x) t) v1)
select p.*, v2.work_hrs
  from (select v1.id, sum(work_hrs) as work_hrs from (select * from w_dates1 union all select * from w_dates2) v1 group by v1.id) v2,
       w_periods p
 where v2.id = p.id

Ого! Я так понимаю, что Вы сперва на оракле сделали, а потом на постгрес перенесли. Постгрес, кстати, позволяет более компактно выражаться.

Оракловый запрос нормально погонять не на чем, а постгресовый на многих периодах сильно занижает. Например, на ('2018-05-24 17:31:12', '2018-06-01 17:59:39') показывает рабочее время всего 09:28:27, а там целая неделя.

А вот на ('2018-06-10 18:46:10', '2018-06-11 10:18:18') наоборот насчитал лишнего.

ЗЫ Даты праздников у Вас неверные. А дополнительные рабочие дни похоже совсем не учитываются, и это тоже даёт неверные результаты.
Исправил даты праздников, добавил дополнительные рабочие дни.
Версия под Oracle
with w_periods as
 (select 1 as id,
         to_date('2019-03-29 07:00:00', 'YYYY-MM-DD HH24:MI:SS') as start_time,
         to_date('2019-04-08 14:00:00', 'YYYY-MM-DD HH24:MI:SS') as stop_time
    from dual
  union all
  select 2 as id,
         to_date('2019-04-10 07:00:00', 'YYYY-MM-DD HH24:MI:SS') as start_time,
         to_date('2019-04-10 20:00:00', 'YYYY-MM-DD HH24:MI:SS') as stop_time
    from dual
  union all
  select 3,
         to_date('2019-04-11 12:00:00', 'YYYY-MM-DD HH24:MI:SS') as start_time,
         to_date('2019-04-12 16:07:12', 'YYYY-MM-DD HH24:MI:SS') as stop_time
    from dual
  union all
  select 4,
         to_date('2018-12-28 12:00:00', 'YYYY-MM-DD HH24:MI:SS') as start_time,
         to_date('2019-01-16 16:00:00', 'YYYY-MM-DD HH24:MI:SS') as stop_time
    from dual),
w_holidays as
 (select to_date('2018-01-01', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-01-02', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-01-03', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-01-04', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-01-05', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-01-07', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-01-08', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-02-23', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-03-08', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-03-09', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-04-30', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-05-01', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-05-02', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-05-09', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-06-11', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-06-12', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-11-05', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-12-31', 'YYYY-MM-DD') as dt
	from dual
  union all
  select to_date('2019-01-01', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2019-01-02', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2019-01-03', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2019-01-04', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2019-01-07', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2019-01-08', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2019-02-23', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2019-03-08', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2019-05-01', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2019-05-02', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2019-05-03', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2019-05-09', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2019-05-10', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2019-06-12', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2019-11-04', 'YYYY-MM-DD') as dt
    from dual),
w_work_days as
 (select to_date('2018-04-28', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-06-09', 'YYYY-MM-DD') as dt
    from dual
  union all
  select to_date('2018-12-29', 'YYYY-MM-DD') as dt
    from dual),
w_dates1 as
 (select v2.id, (count(v2.id) * 9) as work_hrs
    from (select v1.*
            from (select distinct (trunc(p.start_time) + level) as dt, p.id
                    from w_periods p
                   where trunc(p.stop_time - p.start_time) > 1
                  connect by level < trunc(p.stop_time - p.start_time)) v1
           where v1.dt not in (select dt from w_holidays)
             and (trim(to_char(v1.dt, 'DAY')) not in ('SATURDAY', 'SUNDAY') or v1.dt in (select dt from w_work_days))) v2
   group by v2.id),
w_dates2 as
 (select v1.id,
         (case
           when (v1.start_time1 = v1.stop_time1) and (v1.start_time2 = v1.stop_time2) then
            (v1.start_time2 - v1.start_time1)
           else
            (v1.start_time2 - v1.start_time1) + (v1.stop_time2 - v1.stop_time1)
         end) * 24 as work_hrs
    from (select t.*,
                 (case
                   when (t.start_time < trunc_start_time + interval '10' hour) then
                    trunc_start_time + interval '10' hour
                   else
                    t.start_time
                 end) as start_time1,
                 (case
                   when (t.start_time < trunc_start_time + interval '19' hour) then
                    trunc_start_time + interval '19' hour
                   else
                    t.start_time
                 end) as start_time2,
                 (case
                   when (t.stop_time > trunc_stop_time + interval '10' hour) then
                    trunc_stop_time + interval '10' hour
                   else
                    t.stop_time
                 end) as stop_time1,
                 (case
                   when (t.stop_time > trunc_stop_time + interval '19' hour) then
                    trunc_stop_time + interval '19' hour
                   else
                    t.stop_time
                 end) as stop_time2
            from (select x.*, trunc(x.start_time) as trunc_start_time, trunc(x.stop_time) as trunc_stop_time from w_periods x) t) v1)
select p.*, v2.work_hrs
  from (select v1.id, sum(work_hrs) as work_hrs from (select * from w_dates1 union all select * from w_dates2) v1 group by v1.id) v2,
       w_periods p
 where v2.id = p.id
 order by p.id


Версия под PostgreSQL
with w_periods(id, start_time, stop_time) as (
  values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp), 
        (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp), 
        (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
        (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
w_holidays as
 (select to_date('2018-01-01', 'YYYY-MM-DD') as dt
  union all
  select to_date('2018-01-02', 'YYYY-MM-DD')
  union all
  select to_date('2018-01-03', 'YYYY-MM-DD')
  union all
  select to_date('2018-01-04', 'YYYY-MM-DD')
  union all
  select to_date('2018-01-05', 'YYYY-MM-DD')
  union all
  select to_date('2018-01-07', 'YYYY-MM-DD')
  union all
  select to_date('2018-01-08', 'YYYY-MM-DD')
  union all
  select to_date('2018-02-23', 'YYYY-MM-DD')
  union all
  select to_date('2018-03-08', 'YYYY-MM-DD')
  union all
  select to_date('2018-03-09', 'YYYY-MM-DD')
  union all
  select to_date('2018-04-30', 'YYYY-MM-DD')
  union all
  select to_date('2018-05-01', 'YYYY-MM-DD')
  union all
  select to_date('2018-05-02', 'YYYY-MM-DD')
  union all
  select to_date('2018-05-09', 'YYYY-MM-DD')
  union all
  select to_date('2018-06-11', 'YYYY-MM-DD')
  union all
  select to_date('2018-06-12', 'YYYY-MM-DD')
  union all
  select to_date('2018-11-05', 'YYYY-MM-DD')
  union all
  select to_date('2018-12-31', 'YYYY-MM-DD')
  union all
  select to_date('2019-01-01', 'YYYY-MM-DD')
  union all
  select to_date('2019-01-02', 'YYYY-MM-DD')
  union all
  select to_date('2019-01-03', 'YYYY-MM-DD')
  union all
  select to_date('2019-01-04', 'YYYY-MM-DD')
  union all
  select to_date('2019-01-07', 'YYYY-MM-DD')
  union all
  select to_date('2019-01-08', 'YYYY-MM-DD')
  union all
  select to_date('2019-02-23', 'YYYY-MM-DD')
  union all
  select to_date('2019-03-08', 'YYYY-MM-DD')
  union all
  select to_date('2019-05-01', 'YYYY-MM-DD')
  union all
  select to_date('2019-05-02', 'YYYY-MM-DD')
  union all
  select to_date('2019-05-03', 'YYYY-MM-DD')
  union all
  select to_date('2019-05-09', 'YYYY-MM-DD')
  union all
  select to_date('2019-05-10', 'YYYY-MM-DD')
  union all
  select to_date('2019-06-12', 'YYYY-MM-DD')
  union all
  select to_date('2019-11-04', 'YYYY-MM-DD')),
w_work_days as
 (select to_date('2018-04-28', 'YYYY-MM-DD') as dt
  union all
  select to_date('2018-06-09', 'YYYY-MM-DD')
  union all
  select to_date('2018-12-29', 'YYYY-MM-DD')),
w_dates1 as
 (select v2.id, (count(v2.id) * 9) * '1 hours' ::interval as work_hrs
    from (select v1.*
            from (select id, dt from w_periods, generate_series(start_time ::date, stop_time ::date, '1 day') as dt) v1
           where v1.dt not in (select dt from w_holidays)
             and (trim(to_char(v1.dt, 'DAY')) not in ('SATURDAY', 'SUNDAY') or v1.dt in (select dt from w_work_days))
             and v1.dt not in (select date_trunc('day', start_time) from w_periods)
             and v1.dt not in (select date_trunc('day', stop_time) from w_periods)) v2
   group by v2.id),
w_dates2 as
 (select v1.id,
         (case
           when (v1.start_time1 = v1.stop_time1) and (v1.start_time2 = v1.stop_time2) then
            (v1.start_time2 - v1.start_time1)
           else
            (v1.start_time2 - v1.start_time1) + (v1.stop_time2 - v1.stop_time1)
         end) as work_hrs
    from (select t.*,
                 (case
                   when (t.start_time < trunc_start_time + interval '10' hour) then
                    trunc_start_time + interval '10' hour
                   else
                    t.start_time
                 end) as start_time1,
                 (case
                   when (t.start_time < trunc_start_time + interval '19' hour) then
                    trunc_start_time + interval '19' hour
                   else
                    t.start_time
                 end) as start_time2,
                 (case
                   when (t.stop_time > trunc_stop_time + interval '10' hour) then
                    trunc_stop_time + interval '10' hour
                   else
                    t.stop_time
                 end) as stop_time1,
                 (case
                   when (t.stop_time > trunc_stop_time + interval '19' hour) then
                    trunc_stop_time + interval '19' hour
                   else
                    t.stop_time
                 end) as stop_time2
            from (select x.*, date_trunc('day', x.start_time) as trunc_start_time, date_trunc('day', x.stop_time) as trunc_stop_time
                    from w_periods x) t) v1)
select p.*, v2.work_hrs
  from (select v1.id, sum(work_hrs) as work_hrs from (select * from w_dates1 union all select * from w_dates2) v1 group by v1.id) v2,
       w_periods p
 where v2.id = p.id
order by p.id


Под postgresql в Navicat Premium у меня выводит так:
2018-05-24 17:31:12 | 2018-06-01 17:59:39 | 54:28:27
2018-06-10 18:46:10 | 2018-06-11 10:18:18 | 00:32:08
Может под разные клиенты нужно задавать форматирование? В oracle результат в долях (цифры)
Про 54 часа буду анализировать, почему у нас работает по-разному. А вот с 00:32:08 — это точно ошибка. 10 июня был выходной, а 11 июня — праздничный день.
54 часа работает, не знаю, что я за глюк поймал, а 00:32:08 некорректная работа так и осталась.
UFO just landed and posted this here
Да, месяц прошёл. Разбор обязательно будет, пишу. Просто съехал по срокам, так как написание статей на Хабр для меня — хобби, пишу в свободное время.
Sign up to leave a comment.