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

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

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),
           (5, '2019-05-23 16:51:40'::timestamp, '2019-05-24 02:55:50'::timestamp),
           (6, '2019-05-25 19:51:40'::timestamp, '2019-05-25 23:55:50'::timestamp)
),
     holidays(period) as (
         values (tsrange('2018-01-01 00:00:00', '2018-01-08 23:59:59')),
                (tsrange('2018-02-23 00:00:00', '2018-02-23 23:59:59')),
                (tsrange('2018-03-08 00:00:00', '2018-03-09 23:59:59')),
                (tsrange('2018-04-30 00:00:00', '2018-05-02 23:59:59')),
                (tsrange('2018-05-09 00:00:00', '2018-05-09 23:59:59')),
                (tsrange('2018-06-11 00:00:00', '2018-06-12 23:59:59')),
                (tsrange('2018-11-05 00:00:00', '2018-11-05 23:59:59')),
                (tsrange('2018-12-31 00:00:00', '2019-01-08 23:59:59')),
                (tsrange('2019-03-08 00:00:00', '2019-03-08 23:59:59')),
                (tsrange('2019-05-01 00:00:00', '2019-05-03 23:59:59')),
                (tsrange('2019-05-09 00:00:00', '2019-05-10 23:59:59')),
                (tsrange('2019-06-12 00:00:00', '2019-06-12 23:59:59')),
                (tsrange('2019-11-04 00:00:00', '2019-11-04 23:59:59')),
                (tsrange('2019-11-04 00:00:00', '2019-11-04 23:59:59'))
     ),
     transfered_work_days(d) as (
         values ('2018-04-28'),
                ('2018-06-09'),
                ('2018-12-29')
     )
select p.*, sum(work_time)
from periods p
left join (
         select id,
                greatest(d::date + '10h'::interval, least(stop_time, d::date + '19h'::interval))::timestamp -
                least(d::date + '19h'::interval, greatest(start_time, d::date + '10h'::interval))::timestamp as work_time
         from (
                  select id, generate_series(start_time::date, stop_time::date, '1d'::interval) d, start_time, stop_time from periods
              ) as days
         where (exists(select true from transfered_work_days twd where twd.d::date = days.d::date limit 1) or
                extract(isodow from days.d) not in (6, 7))
           and not exists(select true from holidays where period @> (days.d::timestamp) limit 1)
     ) wt on wt.id = p.id
group by p.id, p.start_time, p.stop_time
order by p.id

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

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