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



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

Вы можете помочь и перевести немного средств на развитие сайта



Комментарии (101):

  1. faustxp
    /#20179712

    1. Перечисление праздников в WITH входит в понятие одного запроса?
    2. Рабочий день 9 или 8 часов?
    3. Учитывать предпраздничные укороченные дни?

    • ZaEzzz
      /#20179782

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

    • aleksandy
      /#20179786

      2. Учитывая, что стандартный рабочий день — это 8 часов, то, скорее всего, второе.

      3. В задании же написано

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


      • faustxp
        /#20179818

        В ответе за 1 день стоит 9 часов, так что верно первое.

    • bzq
      /#20179824

      Мне кажется, что удобнее задать праздники именно в CTE.
      По остальным вопросам все ответы есть в условии: рабочее время с 10:00 до 19:00, что составляет девять часов; предпраздничный день является полным.

  2. InChaos
    /#20180080

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

  3. bzq
    /#20180172

    Если условие, что работа строго с 10:00 до 19:00, то закрытие заявки не может быть в 20:00 по определению

    Почему не может? Никаким условиям не противоречит. Условия говорят, что нужно посчитать, сколько часов из интервала были рабочими.

    • InChaos
      /#20180372

      Кто закрыл заявку? Если в 19:00 все ушли.

      • bzq
        /#20180510

        Чтобы не вступать в длительную и бесплодную дискуссию, сам пользователь закрыл. (:

  4. the_unbridled_goose
    /#20180272

    Решение в лоб
    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
    ;

    • bzq
      /#20181106

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

      • the_unbridled_goose
        /#20181230

        Добавил еще шаманства для минут и секунд.

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

        • bzq
          /#20181284

          Хорошая боевая магия, но пока колдунства недостаточно. Попробуйте IDDQD.

          • the_unbridled_goose
            /#20181504

            Почему недостаточно?) Результат получен, в логике расхождений с истиной особо не вижу, что не так?

            • bzq
              /#20181654

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

              • the_unbridled_goose
                /#20181802

                Вот так вот должно быть хорошо.

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

                • bzq
                  /#20184734

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

                  • the_unbridled_goose
                    /#20184892

                    В очередной раз понял, нашел и исправил.

                    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;
                    

                    • bzq
                      /#20185916

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

                      • the_unbridled_goose
                        /#20186614

                        Ну и дабы уже закрыть гештальт и перестать себя чувствовать как провинившийся студент…

                        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;
                        

                        • bzq
                          /#20191802

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

                          • the_unbridled_goose
                            /#20192138

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

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

  5. XareH
    /#20180316

    Скрытый текст
    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
    

    • XareH
      /#20180674

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

      • bzq
        /#20181102

        Да, так лучше.

        • XareH
          /#20181518

          минуты и секунды
          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 

          • XareH
            /#20182576

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

          • bzq
            /#20184896

            Бинго! Этот вариант запроса уже дал на моих тестовых данных правильный результат.

  6. uaggster
    /#20180388

    ИМХО, всё просто.
    Через рекурсивное 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).

    Как то так.

  7. Tatikoma
    /#20180814

    Про сокращенные рабочие дни как-то забыли. Недостаточно реалистично :-)

    • bzq
      /#20185836

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

      Буду рад, если хоть одно из решений позволит учесть укороченные предпраздничные дни. Когда я эту задачу решал в реальной жизни, я на это заложился, но не пригодилось.

  8. bzq
    /#20180854

    Пожалуйста прячьте код под спойлер!

  9. bzq
    /#20180878

    Что-то я смотрю, что рабочие часы воспринимаются почему-то исключительно без рабочих минут. Минуты не забывайте! Я поправил исходные интервалы и ожидаемый ответ, чтобы подчеркнуть наличие минут.

  10. bzq
    /#20181084

    Решение от 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

  11. vav180480_2
    /#20181158 / -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
    

  12. StrangerInTheKy
    /#20181436

    Я этот ваш постгрес уважаю, конечно, но по жизни работаю с ораклом. Такшта простите, если чо. Нагуглил какие-то функции для работы с интервалами и генерации последовательностей, взял первое, что попалось.

    У меня в 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;
    
    
    
    

    • bzq
      /#20181570

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

  13. Envek
    /#20181450 / +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;

    • bzq
      /#20181624

      Согласен, интервалы здесь очень в тему.

  14. mi888
    /#20182158

    Скрытый текст

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

    • bzq
      /#20184926

      Да, алгоритм-то несложен — посчитать количество рабочих дней, правильно учесть первый и последний дни, всё сложить. Осталось это выразить на SQL.
      PS умножить надо на 9

  15. puyol_dev2
    /#20182440

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

    • vav180480_2
      /#20182472

      Не надо мне ничего говорить, просто покажи свой код (с) не помню

      • puyol_dev2
        /#20182482

        Кодом пусть занимаются программисты ))

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

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

        + конечно забыл про неполные недели для интервалов. Полагаю нужно вычислить день недели начала и конца интервалов, исключить эти дни и посчитать по 5 дневке

        • vav180480_2
          /#20183282

          Зачем так много говорить? Просто покажи код… хотя бы нагугленный:)

          • puyol_dev2
            /#20184642

            Смотри под спойлером SQL

            • vav180480
              /#20184990

              Посмотрел, занятно, а с минутами что? Там под id=3 результат не верный.

              • puyol_dev2
                /#20188662 / -1

                Верный. Если ты отработал 13:07:12, значит в часах это 14, а не 13

    • findoff
      /#20182764

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


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

      • puyol_dev2
        /#20184624

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

        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
        

        • bzq
          /#20185194

          Вроде работает, но без минут нехорошо.

  16. vav180480_2
    /#20183426

    удалено

  17. vav180480
    /#20183442

    Итерации по дням посекундная точность
    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
    

    • vav180480
      /#20183624

      Решение с исправленными исходными данными

      итерации по дням с посекундной точностью
      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
      

      • puyol_dev2
        /#20184636

        Твой код не работает

        SQL Error [42P01]: ОШИБКА: отношение «periods» не существует
        Позиция: 162

        • vav180480
          /#20184828

          1) мой код работает
          2) правильно писать «твой код не работает в ...» далее нужно конкретно написать где
          3) я делал в этой песочнице: rextester.com
          Language: PostgreSQL
          усё робит
          4) где ты поджигал?
          там табличные выражения поддерживаются?

          • puyol_dev2
            /#20184908

            Ты уже исправил. Молодец. Я тебе код ошибки выложил предыдущего твоего нерабочего варианта

            • vav180480
              /#20184946

              1) Предыдущий работает так же как и первый, исправлены только и исключительно исходные данные.
              2) Я не могу редактировать сообщения через полчаса после их опубликования, я их опубликовал несколько часов назад.
              3) Я не могу редактировать сообщения после того как на них ответили, а ты ответил.
              4) Врать и говнокодить — не хорошо.

              • puyol_dev2
                /#20184980

                Я не знаю, что ты можешь, а что нет. Но у твоего сообщения стоит метка, что ты его редактировал

                • vav180480
                  /#20185016

                  1) Не знаешь, не делай предположений и не озвучивай этих предположений, в зависимости от ситуации это может выглядеть например обидно или например смешно
                  2) Попробуй отредактировать СВОЕ сообщение которое ты написал более получаса назад

      • bzq
        /#20185286

        Решение не учитывает праздничные дни.

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

        • vav180480
          /#20185324

          какие конкретно праздничные дни не учтены? у меня ответ не верен? мой ответ не совпал с вашим?

          • bzq
            /#20185526

            Да, в условии интервалы могут быть из 2018-2019 гг., а Вы учли только новогодние праздники 2019 года.

            • vav180480
              /#20185566

              Зачем загромождать решение? Лично мне лениво заниматься мартышкиным трудом и расписывать все праздники за два года, когда нужно только 8 дней одного года. Оно будет нагляднее если вместо одной строки в holidays будет двадцать две? Как это повлияет на результат? Как это повлияет на алгоритм? Или вам мой алгоритм кажется уж слишком лаконичным?:) Ведь в реале будут не табличные выражения periods и holidays, а таблички базы данных periods и hilidays. Я добавил только те праздники которые попадают в заданные интервалы.

              • bzq
                /#20185754

                Я отчасти с Вами согласен, но тем не менее в условии я просил учесть праздники за два года и мне сперва пришлось «при необходимости решение можно будет легко дополнить», чтобы погонять на тестовых данных. Я бы не стал проявлять излишний формализм, если бы это был единственный нюанс Вашего решения. Но у Вас не учитываются дополнительные рабочие дни, например, 2018-04-28 был рабочим днём. Также потерялись в выводе периоды, которые совсем не попали на рабочее время.
                А вот компактность Вашего решения мне очень нравится.

                • vav180480
                  /#20185876

                  ниже привел решение с учетом рабочих дней на выходных

  18. OrmEugensson
    /#20185552

    К сожалению, на 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)
    

    • bzq
      /#20185624

      Интересный подход, пока из опубликованных решений никто не пробовал так считать, но есть ошибка в реализации. Запустите на интервале ('2019-01-01 21:00:00', '2019-01-01 21:00:00'), эффект неожиданный.

      • OrmEugensson
        /#20185922

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

      • OrmEugensson
        /#20186074

        Не выдержал, поправил, добавил учёт перенесённых выходных (добавил только пару дней в 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)
        

        • bzq
          /#20186682

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

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

          • OrmEugensson
            /#20191570

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

            • bzq
              /#20192006

              У Вас 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)
              ), ...

              • OrmEugensson
                /#20193044

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

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

  19. vav180480
    /#20185864

    Решение с учетом праздничных дней и с учетом рабочих выходных дней
    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
    

    • bzq
      /#20185936

      Круто. Но потерялись периоды, совсем не попадающие на рабочее время.

      • vav180480
        /#20186084

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

        • valery1707
          /#20186254

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


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

          • vav180480
            /#20186294

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

            • valery1707
              /#20186670

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

        • bzq
          /#20192888

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

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

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

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

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

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

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

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

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

  20. valery1707
    /#20185976

    Вот мой вариант
    -- 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
    ;

    • bzq
      /#20189900

      На короткой выборке работает правильно, но на боевых даёт ошибку.

      На каких данных некорректно работает пока не понял. Чуть позже отвечу, где ошибка.

  21. eranthis
    /#20187422

    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)

    • eranthis
      /#20187570

      прошу прощения, в первом ответе не закрепил под спойлер.

      либо то же самое приклеить к периодам контекстно
      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

      • bzq
        /#20189734

        Круто, оба решения работают. Использование tsrange, all, any позволило сделать очень компактный запрос.

        • eranthis
          /#20191516

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

          • bzq
            /#20194408

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

      • OrmEugensson
        /#20193862 / +1

        Красиво применили функцию-тип «timestamp», я и не знал, что так можно в Postgres

  22. vav180480
    /#20188232

    Решение с итерациями по дням, посекундной точностью, с учетом перенесенных на выходные рабочих дней и показываются заявки вообще не попавшие в рабочее время

    Код
    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 
    

    • bzq
      /#20189876

      Бинго! Логика верная, но из чьего-то запроса выше Вы скопировали неправильные данные для праздников и не указали все дополнительные рабочие дни. После того, как я поправил, результаты на моих тестовых выборках стали сходиться с моими результатами.

      • vav180480
        /#20194322

        Мне понравилась идея высказанная где то выше, не итерировать по дням, итерировать по исключениям (праздникам и переносам, которых заведомо меньше) в каждой заявке, потому как я заради теста, в дате окончания указал вместо 2019 — 22019 год (ну ну а чо) — песочница сдохла, и я догадываюсь почему:) На выходных мобыть подумкаю.

  23. Megacinder
    /#20192010

    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

    • bzq
      /#20192182

      Табличка otrsuser.stat_holidays не найдена.

      • Megacinder
        /#20194656

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


        Заголовок спойлера
        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

        • bzq
          /#20195102

          Не учитываете никак дополнительные рабочие дни. Например, 2018-04-28 был рабочим днём. Поэтому на моих выборках результаты не сходятся.

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

  24. TelepnevDmitriy
    /#20192012

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

  25. nikotin77
    /#20192014

    Так вижу
    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
    
    

  26. Vadim-n
    /#20192154

    У меня вот что получилось. Таблица 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
    

  27. uaggster
    /#20192410

    Я понимаю, что здесь тусовка постгрес.
    Я этим диалектом 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)