SQL: задача о рабочем времени

    Здравствуйте, в эфире снова Радио SQL! Разминайте ганглии, расправляйте псевдоподии (или наоборот?) и настраивайтесь на нашу гравитационную волну!


    В прошлый раз меня чуть было не подвергли остракизму за разбор (https://habr.com/ru/post/359064/) олимпиадной задачи на SQL, якобы она была недостаточно приближена к жизни. Как будто теги «ненормальное программирование» и «олимпиада» не говорят сами за себя. Но, очевидно, теги никто не читает! И тем не менее, я всё равно продолжу тему разбора задачек на замечательном языке программирования SQL. Потому что лапки (чешутся).

    Сегодня нас ждёт задача исключительно жЫзненная, и даже практически рабочая. С ней я столкнулся, пытаясь посчитать выполнение SLA по заявкам от любивных пользователей. Суть исходной проблемы в следующем: надо было посчитать продолжительность работы по каждой заявке и сравнить с тем, что мы обещали. Всё бы ничего, но время в обязательствах было декларировано рабочее, а из изменений статусов в заявках я мог получить только календарное. И тут – мысль! Вот же она, задачечка! Не слишком сложная, но и не совсем тривиальная. Как раз чтобы размять центральные отделы ваших вегетативных нервных систем, сделав их более симпатическими!

    Итак, сформулирую условие.

    Есть несколько временных интервалов, заданных датой-временем своего начала и конца (пример в синтаксисе PostgreSQL):

    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)
    )

    Требуется в один SQL-запрос (ц) вычислить продолжительность каждого интервала в рабочих часах. Считаем, что рабочими у нас являются будние дни с понедельника по пятницу, рабочее время всегда с 10:00 до 19:00. Кроме того, в соответствии с производственным календарём РФ существует некоторое количество официальных праздничных дней, которые рабочими не являются, а какие-то из выходных дней, наоборот, являются рабочими из-за переноса тех самых праздников. Укороченность предпраздничных дней учитывать не надо, считаем их полными. Так как праздничные дни год от года меняются, то есть задаются явным перечислением, то ограничимся датами только из 2018 и 2019 годов. Уверен, что при необходимости решение можно будет легко дополнить.

    Надо к исходным периодам из periods добавить один столбец с продолжительностью в рабочих часах. Вот какой должен получиться результат:

     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

    Исходные данные на корректность не проверяем, считаем всегда start_time <= stop_time.

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

    Через месяц будет разбор задачи. Решения не привожу, чтобы был стимул порешать самостоятельно. Убедительная просьба — код в комментариях размещайте под спойлерами!

    Last but not least. Если уж меня угораздило разместить эту статью в корпоративном блоге Postgres Professional, то воспользуемся некоторыми корпоративными плюшками: за самое интересное решение этой задачи разыграем бесплатный поход на PGConf.Russia 2020. Критерии интересности будут лично мои, плюс тех из коллег, с кем я сочту нужным посоветоваться. Удачи!

    UPDATE! Что-то я смотрю, что рабочие часы воспринимаются почему-то исключительно без рабочих минут. Минуты не забывайте! Я поправил исходные интервалы и ожидаемый ответ, чтобы подчеркнуть наличие минут.
    Postgres Professional
    366,06
    Разработчик СУБД Postgres Pro
    Поделиться публикацией

    Комментарии 145

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

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


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

              Почему не может? Никаким условиям не противоречит. Условия говорят, что нужно посчитать, сколько часов из интервала были рабочими.
                0
                Кто закрыл заявку? Если в 19:00 все ушли.
                  0
                  Чтобы не вступать в длительную и бесплодную дискуссию, сам пользователь закрыл. (:
                0
                Решение в лоб
                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
                ;

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

                    Еще больше загадочности
                    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;
                    

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

                            Просветление достигнуто
                            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;

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

                                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;
                                

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

                                    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;
                                    

                                      0
                                      Не, не выходит каменный цветок. На вот таких периодах некорректно считает:
                                      ('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 — неправильные данные, я у себя для тестов исправлял.
                                        0
                                        Какое-то ну очень вымученное решение, потерявшее всю легкость, непринужденность и элегантность.

                                        ...
                                        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;
                                        

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

                                          Наверное бросайте развивать этот подход, явно что-то пошло не туда, не надо насилия над собой.
                  0
                  Скрытый текст
                  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
                  

                    0
                    Учел заявки, которые сами закрылись в нерабочее время
                     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


                      0
                      Да, так лучше.
                        0
                        минуты и секунды
                        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 

                          0
                          Проверил скрипт на большом объеме данных и понял, что не правильно использую 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;

                            0
                            Бинго! Этот вариант запроса уже дал на моих тестовых данных правильный результат.
                      0
                      ИМХО, всё просто.
                      Через рекурсивное 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).

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

                          Буду рад, если хоть одно из решений позволит учесть укороченные предпраздничные дни. Когда я эту задачу решал в реальной жизни, я на это заложился, но не пригодилось.
                          0
                          Пожалуйста прячьте код под спойлер!
                            0
                            Что-то я смотрю, что рабочие часы воспринимаются почему-то исключительно без рабочих минут. Минуты не забывайте! Я поправил исходные интервалы и ожидаемый ответ, чтобы подчеркнуть наличие минут.
                              0
                              Решение от 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

                                –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:00:00'::timestamp),
                                        (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
                                )
                                
                                select id, count(*) - (case when extract(hour from stop_time) between 10 and 19 then 1 else 0 end) hours
                                  from periods p, generate_series(start_time, stop_time, interval '1 hour') as i
                                 where to_char(i, 'D') not in ('1', '7') and extract(HOUR FROM i) between 10 and 18
                                 group by id, stop_time
                                

                                  0
                                  Я этот ваш постгрес уважаю, конечно, но по жизни работаю с ораклом. Такшта простите, если чо. Нагуглил какие-то функции для работы с интервалами и генерации последовательностей, взял первое, что попалось.
                                  У меня в 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;
                                  
                                  
                                  
                                  

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

                                    Каюсь, я слегка подсмотрел в первые два решения (в частности, стырил оттуда график выходных, ибо самому его делать было лень), но они мне не понравились, потому что при подсчёте времени сам собой напрашивается встроенный в 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;
                                      0
                                      Согласен, интервалы здесь очень в тему.
                                        0
                                        На вот таком периоде неправильно считает:
                                        ('2019-05-23 16:51:40', '2019-05-24 02:55:50')
                                          0
                                          Добавил пару условий на начало/конец рабочего дня
                                          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;
                                            0
                                            Длинные строчки покоцались.

                                            На вот таком периоде показывает 8 часов вместо 8 минут:
                                            ('2018-06-09 11:00:00', '2018-06-09 11:08:00')
                                              0
                                              Теперь я понял, что условие-то можно было сильно упростить и обойтись без 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;
                                                0
                                                Бинго! Работает правильно на моих выборках. Кстати, в результате Ваше решение стало похожим почти дословно на уже имеющиеся.
                                        0
                                        Скрытый текст

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

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

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

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

                                              + конечно забыл про неполные недели для интервалов. Полагаю нужно вычислить день недели начала и конца интервалов, исключить эти дни и посчитать по 5 дневке
                                                0
                                                Зачем так много говорить? Просто покажи код… хотя бы нагугленный:)
                                                  0
                                                  Смотри под спойлером SQL
                                                    0
                                                    Посмотрел, занятно, а с минутами что? Там под id=3 результат не верный.
                                                      –1
                                                      Верный. Если ты отработал 13:07:12, значит в часах это 14, а не 13
                                              0

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


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

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

                                                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
                                                

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

                                                    И хотелось бы минут-секунд.
                                                0
                                                удалено
                                                  0
                                                  Итерации по дням посекундная точность
                                                  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 (start_time, stop_time) as (
                                                    values('2019-01-01'::timestamp, '2019-01-08'::timestamp) 
                                                      )
                                                  
                                                  select id, start_time, stop_time
                                                        ,sum(least(i + interval '19 hour', stop_time)
                                                           - greatest(i + interval '10 hour', start_time)) work_hrs
                                                    from periods p
                                                        ,generate_series(start_time::date, stop_time, interval '1 day') as i
                                                   where to_char(i, 'D') not in ('1', '7')
                                                     and not exists (
                                                          select 1 from holidays where i between start_time and stop_time)
                                                   group by id, start_time, stop_time
                                                   order by id
                                                  

                                                    0
                                                    Решение с исправленными исходными данными
                                                    итерации по дням с посекундной точностью
                                                    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 (start_time, stop_time) as (
                                                      values('2019-01-01'::timestamp, '2019-01-08'::timestamp) 
                                                        )
                                                    
                                                    select id, start_time, stop_time
                                                          ,sum(least(i + interval '19 hour', stop_time)
                                                             - greatest(i + interval '10 hour', start_time)) work_hrs
                                                      from periods p
                                                          ,generate_series(start_time::date, stop_time, interval '1 day') as i
                                                     where to_char(i, 'D') not in ('1', '7')
                                                       and not exists (
                                                            select 1 from holidays where i between start_time and stop_time)
                                                     group by id, start_time, stop_time
                                                     order by id
                                                    


                                                    Кстати есть более интересная задачка, у вас есть несколько подсистем, есть наборы интервалов работы этих подсистем, эти интервалы могут пересекаться (две три и более подсистем работают одновременно) нужно в заданном интервале определить количество времени когда ни одна из подсистем не работала.
                                                      0
                                                      Твой код не работает

                                                      SQL Error [42P01]: ОШИБКА: отношение «periods» не существует
                                                      Позиция: 162
                                                        0
                                                        1) мой код работает
                                                        2) правильно писать «твой код не работает в ...» далее нужно конкретно написать где
                                                        3) я делал в этой песочнице: rextester.com
                                                        Language: PostgreSQL
                                                        усё робит
                                                        4) где ты поджигал?
                                                        там табличные выражения поддерживаются?
                                                          0
                                                          Ты уже исправил. Молодец. Я тебе код ошибки выложил предыдущего твоего нерабочего варианта
                                                            0
                                                            1) Предыдущий работает так же как и первый, исправлены только и исключительно исходные данные.
                                                            2) Я не могу редактировать сообщения через полчаса после их опубликования, я их опубликовал несколько часов назад.
                                                            3) Я не могу редактировать сообщения после того как на них ответили, а ты ответил.
                                                            4) Врать и говнокодить — не хорошо.
                                                              –1
                                                              Я не знаю, что ты можешь, а что нет. Но у твоего сообщения стоит метка, что ты его редактировал
                                                                –1
                                                                1) Не знаешь, не делай предположений и не озвучивай этих предположений, в зависимости от ситуации это может выглядеть например обидно или например смешно
                                                                2) Попробуй отредактировать СВОЕ сообщение которое ты написал более получаса назад
                                                        0
                                                        Решение не учитывает праздничные дни.

                                                        Про задачку с интервалами сформулируйте более формально, чтобы было понятно какими входными данными оперировать и в какой форме результат получить. С удовольствием порешаю на досуге.
                                                          0
                                                          какие конкретно праздничные дни не учтены? у меня ответ не верен? мой ответ не совпал с вашим?
                                                            0
                                                            Да, в условии интервалы могут быть из 2018-2019 гг., а Вы учли только новогодние праздники 2019 года.
                                                              0
                                                              Зачем загромождать решение? Лично мне лениво заниматься мартышкиным трудом и расписывать все праздники за два года, когда нужно только 8 дней одного года. Оно будет нагляднее если вместо одной строки в holidays будет двадцать две? Как это повлияет на результат? Как это повлияет на алгоритм? Или вам мой алгоритм кажется уж слишком лаконичным?:) Ведь в реале будут не табличные выражения periods и holidays, а таблички базы данных periods и hilidays. Я добавил только те праздники которые попадают в заданные интервалы.
                                                                0
                                                                Я отчасти с Вами согласен, но тем не менее в условии я просил учесть праздники за два года и мне сперва пришлось «при необходимости решение можно будет легко дополнить», чтобы погонять на тестовых данных. Я бы не стал проявлять излишний формализм, если бы это был единственный нюанс Вашего решения. Но у Вас не учитываются дополнительные рабочие дни, например, 2018-04-28 был рабочим днём. Также потерялись в выводе периоды, которые совсем не попали на рабочее время.
                                                                А вот компактность Вашего решения мне очень нравится.
                                                                  0
                                                                  ниже привел решение с учетом рабочих дней на выходных
                                                            0
                                                            Кстати есть более интересная задачка, у вас есть несколько подсистем, есть наборы интервалов работы этих подсистем, эти интервалы могут пересекаться (две три и более подсистем работают одновременно) нужно в заданном интервале определить количество времени когда ни одна из подсистем не работала.

                                                            Про задачку с интервалами сформулируйте более формально, чтобы было понятно какими входными данными оперировать и в какой форме результат получить. С удовольствием порешаю на досуге.


                                                            Я далек от всей этой SQL темы, когда-то давно что-то краем уха слышал, но хотелось бы продвинуться. Для этого установил MS (извиняйте) SQL Server Express 2012 и Management Studio.

                                                            Я так думаю на входе должна быть одна таблица по интервалам работы указанных трех сервисов (подсистем). Поля, я так понимаю — время события (старт или стоп сервиса), номер сервиса и тип события (старт или стоп). Всего 3 поля. Ключ, или как оно там называется — два поля — время события и номер сервиса.

                                                            На выходе, рискну предположить, — другая таблица, но с той же структурой для хранения интервалов — когда ни один сервис не работает. Начало такого интервала — событие «стоп» некоторого сервиса, конец интервала — событие «старт» некоторого сервиса. По ходу считается сумма интервалов.

                                                            Идея решения, как бы примитивная, как по мне: из входной таблицы кросс джойном (правильно ли я выражаюсь?) делаем таблицу, в которой уже два поля времени, причем в первое поле идут времена событий «Стоп», а во второе поле — времена событий «Старт», при этом из событий «Стоп» — только последнее из непрерывной временной цепочки таких событий, а в «Старт» — только первое из непрерывной временной цепочки таких событий «Старт». Эти записи — потенциальные времена искомого бездействия сервисов, надо только отсеять ненужные, то есть те, которые принадлежат хоть какому интервалу любого сервиса.

                                                            Затем эта таблица преобразуется в выходную, впрочем она и сама вполне могла бы быть выходной. Как все вышесказанное реализовать в одном запросе SQL, that's the question.
                                                              0
                                                              Накидал пример данных. Так?
                                                              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)
                                                        0
                                                        К сожалению, на 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)
                                                        

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

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

                                                                У меня сошлись результаты запроса на небольшой тестовой выборке, но не сошлись на большой по реальным данным. Сходу ошибку не вижу, посмотрю ещё попозже.
                                                                  0
                                                                  Если можете предоставить тестовую запись на которой не сходится, я посмотрю в чём может быть проблема
                                                                    0
                                                                    У Вас 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)
                                                                    ), ...

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

                                                                      Код без итерации по дням + 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)
                                                                      

                                                                        0
                                                                        Падает с ошибкой:
                                                                        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')
                                                                          0
                                                                          Да, логично, что падает, поправил

                                                                          Код без итерации по дням
                                                                          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)
                                                                          

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

                                                                              Код без итерации по дням
                                                                              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)
                                                                              

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

                                                                                Про тестирование. Я никаких тестовых данных не давал, это ж задачка на «размять мозги». Я продемонстрировал только как выглядят входные данные, и как должен выглядеть результат. Программист сам должен знать, на каких данных следует проверить заложенные в решение допущения.
                                                                                  0
                                                                                  Программист сам должен знать, на каких данных следует проверить заложенные в решение допущения
                                                                                  — это бесспорно, к сожалению, не готов тратить достаточное количество времени на создание хорошей тестовой выборки (и продумывать возможны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);
                                                                                  

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

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

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

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

                                                                                    Надо мне, наверное, больше использовать смаликов, а то заминусуют. (:
                                                                                      0
                                                                                      я не воспринял как наезд, больше как совет, в целом верный.
                                                            0
                                                            Решение с учетом праздничных дней и с учетом рабочих выходных дней
                                                            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 (holiday) as (
                                                                     values ('2018-01-01'::timestamp),
                                                                            ('2018-01-02'::timestamp),
                                                                            ('2018-01-03'::timestamp),
                                                                            ('2018-01-04'::timestamp),
                                                                            ('2018-01-05'::timestamp),
                                                                            ('2018-01-07'::timestamp),
                                                                            ('2018-01-08'::timestamp),
                                                                            ('2018-02-23'::timestamp),
                                                                            ('2018-03-08'::timestamp),
                                                                            ('2018-05-01'::timestamp),
                                                                            ('2018-05-09'::timestamp),
                                                                            ('2018-06-12'::timestamp),
                                                                            ('2018-11-04'::timestamp),
                                                                            ('2019-01-01'::timestamp),
                                                                            ('2019-01-02'::timestamp),
                                                                            ('2019-01-03'::timestamp),
                                                                            ('2019-01-04'::timestamp),
                                                                            ('2019-01-05'::timestamp),
                                                                            ('2019-01-07'::timestamp),
                                                                            ('2019-01-08'::timestamp),
                                                                            ('2019-02-23'::timestamp),
                                                                            ('2019-03-08'::timestamp),
                                                                            ('2019-05-01'::timestamp),
                                                                            ('2019-05-09'::timestamp),
                                                                            ('2019-06-12'::timestamp),
                                                                            ('2019-11-04'::timestamp)    
                                                                )
                                                            ,work_holidays (work_holiday) as (
                                                                     values ('2018-04-28'::timestamp)
                                                                )    
                                                            
                                                            select id, start_time, stop_time
                                                                  ,sum(least(i + interval '19 hour', stop_time)
                                                                     - greatest(i + interval '10 hour', start_time)) work_hrs
                                                              from periods p
                                                                  ,generate_series(start_time::date, stop_time, interval '1 day') as i
                                                             where (to_char(i, 'D') not in ('1', '7')
                                                                    or exists (
                                                                        select 1 from work_holidays where i = work_holiday)
                                                                    )
                                                               and not exists (
                                                                    select 1 from holidays where i = holiday)
                                                             group by id, start_time, stop_time
                                                             order by id
                                                            

                                                              0
                                                              Круто. Но потерялись периоды, совсем не попадающие на рабочее время.
                                                                0
                                                                Я не понял это как? В нерабочее время завели заявку (вечером после работы по пьяни) и в нерабочее время закрыли (утром следующего дня до работы когда протрезвели) вообще не работая?:) Напишите интервальчик для примера и жизненную ситуацию по этому интервальчику.
                                                                  0

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


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

                                                                    0
                                                                    Домой приду — попробую, там придется все условия из раздела where в раздел select тащить, а сие некрасиво просто.
                                                                      0

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

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

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

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

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

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

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

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

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

                                                                    Ну короче не только по пьяни в нерабочее время что-то происходит на работе. Надеюсь, достаточно привёл жизненных примеров. (:
                                                                0
                                                                Вот мой вариант
                                                                -- 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.

                                                                  0
                                                                  Залил на rextester
                                                                    0
                                                                    Вот на таких данных:
                                                                    ('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

                                                                      0

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


                                                                      Запрос с модифицированным 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
                                                                        0
                                                                        Почти. Но ('2018-06-11 19:58:40', '2018-06-11 20:02:01') даёт 00:03:21, а должно быть 00:00:00.
                                                                    0
                                                                    На короткой выборке работает правильно, но на боевых даёт ошибку.

                                                                    На каких данных некорректно работает пока не понял. Чуть позже отвечу, где ошибка.
                                                                    0
                                                                    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)
                                                                      0
                                                                      прошу прощения, в первом ответе не закрепил под спойлер.
                                                                      либо то же самое приклеить к периодам контекстно
                                                                      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

                                                                        0
                                                                        Круто, оба решения работают. Использование tsrange, all, any позволило сделать очень компактный запрос.
                                                                          0
                                                                          тут в идеале бы ещё знать, как представлен календарь праздников и рабочих уикендов, так как параметрически их вряд ли передают в запрос. задача была бы более приближена к реальной, а так решение выглядит несколько искусственно.
                                                                            0
                                                                            В реальной жизни может быть и так, и эдак. В зависимости от того, разовый ли это запрос или регулярный. Я специально не стал конкретизировать представление календаря, чтобы посмотреть на полёт мысли. Вот, например, Ваше представление мне очень понравилось. Компактнее ни у кого не получилось.
                                                                          +1
                                                                          Красиво применили функцию-тип «timestamp», я и не знал, что так можно в Postgres
                                                                            0
                                                                            Это неявный 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. Это бы ещё и облегчило запрос. Но… хорошая мысля приходит опосля. :)
                                                                        0
                                                                        Решение с итерациями по дням, посекундной точностью, с учетом перенесенных на выходные рабочих дней и показываются заявки вообще не попавшие в рабочее время
                                                                        Код
                                                                        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-12-28 20:00:00'::timestamp, '2018-12-29 04:00:00'::timestamp)   
                                                                        )
                                                                        ,holidays (holiday) as (
                                                                                 values ('2018-01-01'::timestamp),
                                                                                        ('2018-01-02'::timestamp),
                                                                                        ('2018-01-03'::timestamp),
                                                                                        ('2018-01-04'::timestamp),
                                                                                        ('2018-01-05'::timestamp),
                                                                                        ('2018-01-07'::timestamp),
                                                                                        ('2018-01-08'::timestamp),
                                                                                        ('2018-02-23'::timestamp),
                                                                                        ('2018-03-08'::timestamp),
                                                                                        ('2018-05-01'::timestamp),
                                                                                        ('2018-05-09'::timestamp),
                                                                                        ('2018-06-12'::timestamp),
                                                                                        ('2018-11-04'::timestamp),
                                                                                        ('2019-01-01'::timestamp),
                                                                                        ('2019-01-02'::timestamp),
                                                                                        ('2019-01-03'::timestamp),
                                                                                        ('2019-01-04'::timestamp),
                                                                                        ('2019-01-05'::timestamp),
                                                                                        ('2019-01-07'::timestamp),
                                                                                        ('2019-01-08'::timestamp),
                                                                                        ('2019-02-23'::timestamp),
                                                                                        ('2019-03-08'::timestamp),
                                                                                        ('2019-05-01'::timestamp),
                                                                                        ('2019-05-09'::timestamp),
                                                                                        ('2019-06-12'::timestamp),
                                                                                        ('2019-11-04'::timestamp)    
                                                                            )
                                                                        ,work_holidays (work_holiday) as (
                                                                                 values ('2018-04-28'::timestamp)
                                                                            )    
                                                                        
                                                                        select id, start_time, stop_time
                                                                              ,sum(case when (to_char(i, 'D') not in ('1', '7') or w.work_holiday is not null)
                                                                                         and h.holiday is null
                                                                                        then greatest(least(i + interval '19 hour', stop_time)
                                                                                            -greatest(i + interval '10 hour', start_time),interval '0 hour')
                                                                                        else interval '0 hour'
                                                                                        end) work_hrs
                                                                          from periods p
                                                                              ,generate_series(start_time::date, stop_time, interval '1 day') as i
                                                                          left join holidays h on h.holiday = i
                                                                          left join work_holidays w on w.work_holiday = i
                                                                         group by id, start_time, stop_time
                                                                         order by id 
                                                                        

                                                                          0
                                                                          Бинго! Логика верная, но из чьего-то запроса выше Вы скопировали неправильные данные для праздников и не указали все дополнительные рабочие дни. После того, как я поправил, результаты на моих тестовых выборках стали сходиться с моими результатами.
                                                                            0
                                                                            Мне понравилась идея высказанная где то выше, не итерировать по дням, итерировать по исключениям (праздникам и переносам, которых заведомо меньше) в каждой заявке, потому как я заради теста, в дате окончания указал вместо 2019 — 22019 год (ну ну а чо) — песочница сдохла, и я догадываюсь почему:) На выходных мобыть подумкаю.
                                                                          0
                                                                          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
                                                                            0
                                                                            Табличка otrsuser.stat_holidays не найдена.
                                                                              0

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


                                                                              Заголовок спойлера
                                                                              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
                                                                                0
                                                                                Не учитываете никак дополнительные рабочие дни. Например, 2018-04-28 был рабочим днём. Поэтому на моих выборках результаты не сходятся.

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

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


                                                                                  Заголовок спойлера
                                                                                  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
                                                                                    0
                                                                                    Бинго! Теперь на моих тестах сходится.

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

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

                                                                            0
                                                                            решение с 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
                                                                            

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

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

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


                                                                                Заголовок спойлера
                                                                                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
                                                                                  0
                                                                                  Бинго! Сошлось на моих данных. Забавно, что получилось на части периодов, которые не попали на рабочее время, насчиталось рабочих часов 00:00:00, а на части NULL.
                                                                                    0

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

                                                                                      0
                                                                                      Например, на таком ('2019-05-18 11:30:13', '2019-05-19 09:09:01') даёт NULL. Похоже, что это когда start_time и stop_time выпадают на одни и те же выходные, и тогда подзапрос в итоговом select-е не возвращает ни одной записи.
                                                                              0
                                                                              Так вижу
                                                                              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
                                                                              
                                                                              

                                                                                0
                                                                                Близко! Но показывает странноватый результат, например, на таком периоде: ('2019-05-23 19:00:57', '2019-05-23 21:45:09') — минус 57 секунд.
                                                                                  0
                                                                                  Да, действительно упустил один момент.
                                                                                  Протер глаза
                                                                                  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
                                                                                  

                                                                                    0
                                                                                    Бинго! Сошлось на моих выборках!
                                                                                0
                                                                                У меня вот что получилось. Таблица 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
                                                                                

                                                                                  0
                                                                                  Собрал подзапрос для 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') потерялся.
                                                                                  0
                                                                                  Я понимаю, что здесь тусовка постгрес.
                                                                                  Я этим диалектом 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 рабочих часов.
                                                                                    0
                                                                                    Допущения правильные, обед не учитывается, называется в быту режим 5х9 (в отличие от 24х7). Это вполне нормальный режим, просто обедать в разнобой ходить, не всем сразу.

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

                                                                                    Победителей объявлю, у меня же приз победителю заявлен.
                                                                                      0
                                                                                      Но погонять на предмет ошибок не могу, не на чем.

                                                                                      тут?
                                                                                        0
                                                                                        Спасибо, конечно. Но хотелось бы не запустить в принципе, а погонять на своих данных.
                                                                                    0
                                                                                    Вообще, в таких задачах неплохо сразу оговаривать таймзоны, а то мало ли что. ;-) Исходил из того, что на дворе Москва.

                                                                                    Получилось 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;
                                                                                    

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

                                                                                      Попытка №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;
                                                                                      

                                                                                        0
                                                                                        Бинго! Работает правильно на моих выборках. И это с 1.05 раза!
                                                                                      0
                                                                                      Версия под 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
                                                                                      

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

                                                                                        Оракловый запрос нормально погонять не на чем, а постгресовый на многих периодах сильно занижает. Например, на ('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') наоборот насчитал лишнего.

                                                                                        ЗЫ Даты праздников у Вас неверные. А дополнительные рабочие дни похоже совсем не учитываются, и это тоже даёт неверные результаты.
                                                                                        0
                                                                                        Исправил даты праздников, добавил дополнительные рабочие дни.
                                                                                        Версия под 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 результат в долях (цифры)
                                                                                          0
                                                                                          Про 54 часа буду анализировать, почему у нас работает по-разному. А вот с 00:32:08 — это точно ошибка. 10 июня был выходной, а 11 июня — праздничный день.
                                                                                            0
                                                                                            54 часа работает, не знаю, что я за глюк поймал, а 00:32:08 некорректная работа так и осталась.

                                                                                        Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                                                                                        Самое читаемое