Habr_448368_valery1707_2, PostgreSQL

Language: Layout:

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

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

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

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

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


absolute service time: 0,62 sec