Comments 148
2. Рабочий день 9 или 8 часов?
3. Учитывать предпраздничные укороченные дни?
1) Скорее всего вылеты по праздникам и рабочим в выходные находятся в отдельной таблице и эти данные входят в рамки одного запроса.
2) Если в теле речь про SLA — скорее всего без обеда. Т.е. 9 часов.
3) Явно указано, что предпраздничные обычные.
3. В задании же написано
Укороченность предпраздничных дней учитывать не надо, считаем их полными.
По остальным вопросам все ответы есть в условии: рабочее время с 10:00 до 19:00, что составляет девять часов; предпраздничный день является полным.
Заявка 2:
Если условие, что работа строго с 10:00 до 19:00, то закрытие заявки не может быть в 20:00 по определению. Но ответ — 9 часов, что соответствует 19:00. Но если верно что заявка закрыта в 20:00, значит человек переработал, и заявка выполнялась 10 часов, но тогда опять же по условиям она должна быть закрыта на следующий день 2019-04-11 в 11:00.
Если условие, что работа строго с 10:00 до 19:00, то закрытие заявки не может быть в 20:00 по определению
Почему не может? Никаким условиям не противоречит. Условия говорят, что нужно посчитать, сколько часов из интервала были рабочими.
with periods(id, start_time, stop_time) as (
values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:00:00'::timestamp),
(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
holidays (h_date) as (
values ('2018-01-01'),
('2018-01-02'),
('2018-01-03'),
('2018-01-04'),
('2018-01-05'),
('2018-01-07'),
('2018-01-08'),
('2018-02-23'),
('2018-03-08'),
('2018-05-01'),
('2018-05-09'),
('2018-06-12'),
('2018-11-04'),
('2019-01-01'),
('2019-01-02'),
('2019-01-03'),
('2019-01-04'),
('2019-01-05'),
('2019-01-07'),
('2019-01-08'),
('2019-02-23'),
('2019-03-08'),
('2019-05-01'),
('2019-05-09'),
('2019-06-12'),
('2019-11-04')
),
magic as (
select id,
start_time,
stop_time,
dd
from periods,
generate_series(start_time, stop_time - interval '1 hour', '1 hour') dd
where extract(dow from dd) between 1 and 5
and extract(hours from dd) >= 10
and extract(hours from dd) < 19
and not exists(select 0 from holidays where h_date::date = dd::date))
select id, start_time, stop_time, count(1) as work_hrs
from magic
group by id, start_time, stop_time
order by id
;
with periods(id, start_time, stop_time) as (
values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
holidays (h_date) as (
values ('2018-01-01'),
('2018-01-02'),
('2018-01-03'),
('2018-01-04'),
('2018-01-05'),
('2018-01-07'),
('2018-01-08'),
('2018-02-23'),
('2018-03-08'),
('2018-05-01'),
('2018-05-09'),
('2018-06-12'),
('2018-11-04'),
('2019-01-01'),
('2019-01-02'),
('2019-01-03'),
('2019-01-04'),
('2019-01-05'),
('2019-01-07'),
('2019-01-08'),
('2019-02-23'),
('2019-03-08'),
('2019-05-01'),
('2019-05-09'),
('2019-06-12'),
('2019-11-04')
),
magic as (
select id,
start_time,
stop_time,
dd,
case
when dd = last_value(dd) over (partition by id, start_time, stop_time) and
extract(hours from stop_time) between 10 and 19
then stop_time - last_value(dd) over (partition by id, start_time, stop_time)
else interval '1 hour' end as w_hours
from periods,
generate_series(start_time, stop_time, '1 hour') dd
where extract(dow from dd) between 1 and 5
and extract(hours from dd) >= 10
and extract(hours from dd) < 19
and not exists(select 0 from holidays where h_date::date = dd::date)
)
select id, start_time, stop_time, to_char(sum(w_hours), 'HH24:MI:SS') as work_hrs
from magic
group by id, start_time, stop_time
order by id;
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;
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;
with periods(id, start_time, stop_time) as (
values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
holidays (h_date) as (
values ('2018-01-01'),
('2018-01-02'),
('2018-01-03'),
('2018-01-04'),
('2018-01-05'),
('2018-01-07'),
('2018-01-08'),
('2018-02-23'),
('2018-03-08'),
('2018-05-01'),
('2018-05-09'),
('2018-06-12'),
('2018-11-04'),
('2018-12-31'),
('2019-01-01'),
('2019-01-02'),
('2019-01-03'),
('2019-01-04'),
('2019-01-05'),
('2019-01-07'),
('2019-01-08'),
('2019-02-23'),
('2019-03-08'),
('2019-05-01'),
('2019-05-09'),
('2019-06-12'),
('2019-11-04')
),
subst_days (s_date) as (
values ('2018-04-28'),
('2018-06-09'),
('2018-12-29')
),
magic as (
select id,
to_char(start_time, 'yyyy-mm-dd HH24:MI:SS') as start_time,
to_char(stop_time, 'yyyy-mm-dd HH24:MI:SS') as stop_time,
case
when dd = last_value(dd) over (partition by id, start_time, stop_time) and
extract(hours from stop_time) between 10 and 18
then stop_time - last_value(dd) over (partition by id, start_time, stop_time)
when dd = first_value(dd) over (partition by id, start_time, stop_time) and
extract(hours from start_time) between 10 and 18
then first_value(dd) over (partition by id, start_time, stop_time) + '1 hour' - start_time
when extract(hours from dd) < 10
or extract(hours from dd) >= 19 then interval '0 hours'
else interval '1 hour' end as w_hours
from periods,
generate_series(date_trunc('hour', start_time), date_trunc('hour', stop_time), '1 hour') dd
where (exists(select 1 from subst_days where dd::date = s_date::date) or extract(dow from dd) between 1 and 5)
and not exists(select 0
from holidays
where h_date::date = dd::date)
)
select id, start_time, stop_time, to_char(sum(w_hours), 'HH24:MI:SS') as work_hrs
from magic
group by id, start_time, stop_time
order by id;
('2018-06-10 18:46:10', '2018-06-13 10:18:18'),
('2019-04-28 21:00:00', '2019-04-28 21:00:00').
На первом неверное значение (18:32:08 вместо 18:18), второй совсем теряет.
PS некритично, но всё же выходные в holidays — неправильные данные, я у себя для тестов исправлял.
with periods(id, start_time, stop_time) as (
values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp),
(5, '2018-06-10 18:46:10'::timestamp, '2018-06-13 10:18:18'::timestamp),
(6, '2019-04-28 21:00:00'::timestamp, '2019-04-28 21:00:00'::timestamp)
),
holidays (h_date) as (
values ('2018-01-01'),
('2018-01-02'),
('2018-01-03'),
('2018-01-04'),
('2018-01-05'),
('2018-01-07'),
('2018-01-08'),
('2018-02-23'),
('2018-03-08'),
('2018-05-01'),
('2018-05-09'),
('2018-06-12'),
('2018-11-04'),
('2018-12-31'),
('2019-01-01'),
('2019-01-02'),
('2019-01-03'),
('2019-01-04'),
('2019-01-05'),
('2019-01-07'),
('2019-01-08'),
('2019-02-23'),
('2019-03-08'),
('2019-05-01'),
('2019-05-09'),
('2019-06-12'),
('2019-11-04')
),
subst_days (s_date) as (
values ('2018-04-28'),
('2018-06-09'),
('2018-12-29')
),
magic as (
select id,
to_char(start_time, 'yyyy-mm-dd HH24:MI:SS') as start_time,
to_char(stop_time, 'yyyy-mm-dd HH24:MI:SS') as stop_time,
case
when dd = last_value(dd) over (partition by id, start_time, stop_time) and
extract(hours from stop_time) between 10 and 18
then stop_time - last_value(dd) over (partition by id, start_time, stop_time)
when extract(hours from dd) < 10
or extract(hours from dd) >= 19 then interval '0 hours'
when dd = first_value(dd) over (partition by id, start_time, stop_time) and
extract(hours from start_time) between 10 and 18
then first_value(dd) over (partition by id, start_time, stop_time) + '1 hour' - start_time
else interval '1 hour' end as w_hours
from periods,
generate_series(date_trunc('hour', start_time), date_trunc('hour', stop_time), '1 hour') dd
where (exists(select 1 from subst_days where dd::date = s_date::date) or extract(dow from dd) between 1 and 5)
and not exists(select 0
from holidays
where h_date::date = dd::date)
union all
select id,
to_char(start_time, 'yyyy-mm-dd HH24:MI:SS') as start_time,
to_char(stop_time, 'yyyy-mm-dd HH24:MI:SS') as stop_time,
interval '0 hours'
from periods,
generate_series(date_trunc('hour', start_time), date_trunc('hour', stop_time), '1 hour') dd
where not exists(select 1
from subst_days
where dd::date = s_date::date
or extract(dow from dd) between 6 and 7
or exists(select 0
from holidays
where h_date::date = dd::date)
))
select id, start_time, stop_time, to_char(sum(w_hours), 'HH24:MI:SS') as work_hrs
from magic
group by id, start_time, stop_time
order by id;
with periods (id,start_time, stop_time) as(
VALUES(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:00:00'::timestamp),
(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
minimax AS (select min(start_time),max(stop_time) FROM periods),
days AS(SELECT gs.d,CASE WHEN extract(isodow from gs.d) IN (6,7) THEN coalesce(holy.iswrk,false) ELSE coalesce(holy.iswrk,true) END iswrk
FROM minimax
JOIN LATERAL generate_series(min::date,max::date,'24:00') gs(d) ON 1=1
left join (values ('2019-01-01'::date,false),('2019-01-02',false),('2019-01-03',false),('2019-01-04',false),('2019-01-07',false),('2019-01-08',false),('2019-03-08',false),
('2019-05-01',false),('2019-05-02',false),('2019-05-03',false),('2019-05-09',false),('2019-05-10',false),('2019-06-12',false),('2019-11-04',false),
('2018-01-01',false),('2018-01-02',false),('2018-01-03',false),('2018-01-04',false),('2018-01-05',false),('2018-01-08',false),('2018-02-23',false),
('2018-03-08',false),('2018-03-09',false),('2018-04-28',true),('2018-04-30',false),('2018-05-01',false),('2018-05-02',false),('2018-05-09',false),
('2018-06-09',true),('2018-06-11',false),('2018-06-12',false),('2018-11-05',false),('2018-12-29',true),('2018-12-31',false)) as holy(d,iswrk)
on gs.d=holy.d)
SELECT id,start_time,stop_time, (count(*)-2)*9+
CASE when extract(hour from start_time)>=19 THEN 0
when extract(hour from start_time)<=10 THEN 9
ELSE 19-extract(hour from start_time)
END+
CASE WHEN extract(hour from stop_time)>=19 THEN 9
WHEN extract(hour from stop_time)<=10 THEN 0
ELSE extract(hour from stop_time)-10
END
FROM periods JOIN days
ON
d>=start_time::date
AND d<=stop_time::date
AND iswrk
GROUP BY id,start_time,stop_time
ORDER BY id
SELECT id,start_time,stop_time, (count(d))*9
-CASE
WHEN count(d)=0 OR start_time::date<min(d) THEN 0
when extract(hour from start_time)>=19 THEN 9
when extract(hour from start_time)<=10 THEN 0
ELSE extract(hour from start_time)-10
END
-CASE WHEN count(d)=0 OR stop_time::date>max(d) THEN 0
WHEN extract(hour from stop_time)>=19 THEN 0
WHEN extract(hour from stop_time)<=10 THEN 9
ELSE 19-extract(hour from stop_time)
END
FROM periods LEFT JOIN days
ON
d>=start_time::date
AND d<=stop_time::date
AND iswrk
GROUP BY id,start_time,stop_time
ORDER BY id
SELECT id,start_time,stop_time, (count(d))*interval '9 hour'
-CASE
WHEN count(d)=0 OR start_time::date<min(d) THEN interval '0 hour'
when start_time - min(d)>=interval '19 hour' THEN interval '9 hour'
when start_time-min(d)<=interval '10 hour' THEN interval '0 hour'
ELSE start_time -min(d) - interval '10 hour'
END
-CASE WHEN count(d)=0 OR stop_time::date>max(d) THEN interval '0 hour'
WHEN stop_time - max(d)>=interval '19 hour' THEN interval '0 hour'
WHEN stop_time - max(d)<=interval '10 hour' THEN interval '9 hour'
ELSE interval '19 hour' - ( stop_time - max(d))
END
FROM periods LEFT JOIN days
ON
d>=start_time::date
AND d<=stop_time::date
AND iswrk
GROUP BY id,start_time,stop_time
ORDER BY id
with periods(id, start_time, stop_time) as (
values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
(4, '2018-12-29 20:00:00'::timestamp, '2019-01-3 16:00:00'::timestamp)
), holy(dd,iswrk) AS (values ('2019-01-01'::date,false),('2019-01-02',false),('2019-01-03',false),('2019-01-04',false),('2019-01-07',false),
('2019-01-08',false),('2019-03-08',false),('2019-05-01',false),('2019-05-02',false),('2019-05-03',false),('2019-05-09',false),('2019-05-10',false),
('2019-06-12',false),('2019-11-04',false),('2018-01-01',false),('2018-01-02',false),('2018-01-03',false),('2018-01-04',false),('2018-01-05',false),
('2018-01-08',false),('2018-02-23',false),('2018-03-08',false),('2018-03-09',false),('2018-04-28',true),('2018-04-30',false),('2018-05-01',false),
('2018-05-02',false),('2018-05-09',false),('2018-06-09',true),('2018-06-11',false),('2018-06-12',false),('2018-11-05',false),('2018-12-29',true),
('2018-12-31',false))
SELECT id,start_time,stop_time, (count(gs.d))*interval '9 hour'
-CASE
WHEN count(d)=0 OR start_time::date<min(d) THEN interval '0 hour'
when start_time - min(d)>=interval '19 hour' THEN interval '9 hour'
when start_time - min(d)<=interval '10 hour' THEN interval '0 hour'
ELSE start_time-min(d)-interval '10 hour'
END
-CASE WHEN count(d)=0 OR stop_time::date>max(d) THEN interval '0 hour'
WHEN stop_time-max(d)>=interval '19 hour' THEN interval '0 hour'
WHEN stop_time-max(d)<=interval '10 hour' THEN interval '9 hour'
ELSE interval '19 hour'- (stop_time-max(d))
END
FROM periods
LEFT JOIN LATERAL (SELECT * FROM generate_series(start_time::date,stop_time::date,'1 day') gs(d) left join holy ON gs.d=holy.dd )gs(d,dd,iswrk) ON
CASE WHEN extract(isodow from gs.d) IN (6,7) THEN coalesce(iswrk,false) ELSE coalesce(iswrk,true) END
GROUP BY id,start_time,stop_time
ORDER BY id;
Через рекурсивное CTE воссоздадим список дней от, включительно, 01.01.2018 до 31.12.2019.
Производственный календарь берём вот тут:
data.gov.ru/opendata/resource/8ba5011a-233e-4e01-a1d2-ff5598d0f34f#2/0.0/0.0
Это текстовый файл, и легко, также в CTE, может быть развернут в линейный список.
Ну и далее Select из periods, outer apply (Select SUM(дни) from первое СТЕ inner join второе СТЕ, со списком рабочих дней, если день — рабочий, и день >= стартовой даты и <=конечной даты из periods).
Как то так.
Буду рад, если хоть одно из решений позволит учесть укороченные предпраздничные дни. Когда я эту задачу решал в реальной жизни, я на это заложился, но не пригодилось.
Мое решение:
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
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;
Каюсь, я слегка подсмотрел в первые два решения (в частности, стырил оттуда график выходных, ибо самому его делать было лень), но они мне не понравились, потому что при подсчёте времени сам собой напрашивается встроенный в PostgreSQL тип interval
(и я его очень сильно люблю, хоть сам почти и не пользуюсь). Поэтому я пошёл и сделал всё по своему. Поэтому, вот:
with periods(id, start_time, stop_time) as (
values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
holiday_overrides(day, working) AS (
values ('2019-01-01'::date,false),('2019-01-02',false),('2019-01-03',false),('2019-01-04',false),('2019-01-07',false),('2019-01-08',false),('2019-03-08',false),('2019-05-01',false),('2019-05-02',false),('2019-05-03',false),('2019-05-09',false),('2019-05-10',false),('2019-06-12',false),('2019-11-04',false),('2018-01-01',false),('2018-01-02',false),('2018-01-03',false),('2018-01-04',false),('2018-01-05',false),('2018-01-08',false),('2018-02-23',false),('2018-03-08',false),('2018-03-09',false),('2018-04-28',true),('2018-04-30',false),('2018-05-01',false),('2018-05-02',false),('2018-05-09',false),('2018-06-09',true),('2018-06-11',false),('2018-06-12',false),('2018-11-05',false),('2018-12-29',true),('2018-12-31',false)
),
period_worktime(period_id, worktime) AS (
SELECT
periods.id AS period_id,
SUM(
CASE
WHEN days.day::date = periods.start_time::date THEN (days.day::date + 'PT19H'::interval) - greatest(periods.start_time, days.day::date + 'PT10H'::interval)
WHEN days.day::date = periods.stop_time::date THEN least(periods.stop_time, days.day::date + 'PT19H'::interval) - (days.day::date + 'PT10H'::interval)
ELSE 'PT9H'::interval
END
) AS worktime
FROM periods JOIN LATERAL generate_series(start_time::date, stop_time::date, '1 day'::interval) days(day) ON 1=1
WHERE
NOT EXISTS (SELECT * FROM holiday_overrides ho WHERE ho.day = days.day::date AND working=false)
AND (
extract(isodow from days.day) NOT IN (6,7)
OR EXISTS (SELECT * FROM holiday_overrides ho WHERE ho.day = days.day::date AND working=true)
)
GROUP BY period_id
)
SELECT periods.*, period_worktime.worktime
FROM periods JOIN period_worktime ON periods.id = period_worktime.period_id;
('2019-05-23 16:51:40', '2019-05-24 02:55:50')
with periods(id, start_time, stop_time) as (
values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp),
(5, '2019-05-23 16:51:40'::timestamp, '2019-05-24 02:55:50'::timestamp)
),
holiday_overrides(day, working) AS (
values ('2019-01-01'::date,false),('2019-01-02',false),('2019-01-03',false),('2019-01-04',false),('2019-01-07',false),('2019-01-08',false),('2019-03-08',false),('2019-05-01',false),('2019-05-02',false)$
),
period_worktime(period_id, worktime) AS (
SELECT
periods.id AS period_id,
SUM(
CASE
WHEN days.day::date = periods.start_time::date THEN (days.day::date + 'PT19H'::interval) - greatest(least(periods.start_time, days.day::date + 'PT19H'::interval), days.day::date + 'PT10H'::interv$
WHEN days.day::date = periods.stop_time::date THEN least(greatest(periods.stop_time, days.day::date + 'PT10H'::interval), days.day::date + 'PT19H'::interval) - (days.day::date + 'PT10H'::interva$
ELSE 'PT9H'::interval
END
) AS worktime
FROM periods JOIN LATERAL generate_series(start_time::date, stop_time::date, '1 day'::interval) days(day) ON 1=1
WHERE
NOT EXISTS (SELECT * FROM holiday_overrides ho WHERE ho.day = days.day::date AND working=false)
AND (
extract(isodow from days.day) NOT IN (6,7)
OR EXISTS (SELECT * FROM holiday_overrides ho WHERE ho.day = days.day::date AND working=true)
)
GROUP BY period_id
)
SELECT periods.*, period_worktime.worktime
FROM periods JOIN period_worktime ON periods.id = period_worktime.period_id;
На вот таком периоде показывает 8 часов вместо 8 минут:
('2018-06-09 11:00:00', '2018-06-09 11:08:00')
with periods(id, start_time, stop_time) as (
values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp),
(5, '2019-05-23 16:51:40'::timestamp, '2019-05-24 02:55:50'::timestamp),
(6, '2018-06-09 11:00:00'::timestamp, '2018-06-09 11:08:00'::timestamp)
),
holiday_overrides(day, working) AS (
values ('2019-01-01'::date,false),('2019-01-02',false),('2019-01-03',false),('2019-01-04',false),('2019-01-07',false),('2019-01-08',false),('2019-03-08',false),('2019-05-01',false),('2019-05-02',false),('2019-05-03',false),('2019-05-09',false),('2019-05-10',false),('2019-06-12',false),('2019-11-04',false),('2018-01-01',false),('2018-01-02',false),('2018-01-03',false),('2018-01-04',false),('2018-01-05',false),('2018-01-08',false),('2018-02-23',false),('2018-03-08',false),('2018-03-09',false),('2018-04-28',true),('2018-04-30',false),('2018-05-01',false),('2018-05-02',false),('2018-05-09',false),('2018-06-09',true),('2018-06-11',false),('2018-06-12',false),('2018-11-05',false),('2018-12-29',true),('2018-12-31',false)
),
period_worktime(period_id, worktime) AS (
SELECT
periods.id AS period_id,
SUM(
least(greatest(periods.stop_time, days.day::date + 'PT10H'::interval), days.day::date + 'PT19H'::interval)
-
least(greatest(periods.start_time, days.day::date + 'PT10H'::interval), days.day::date + 'PT19H'::interval)
) AS worktime
FROM periods JOIN LATERAL generate_series(start_time::date, stop_time::date, '1 day'::interval) days(day) ON 1=1
WHERE
NOT EXISTS (SELECT * FROM holiday_overrides ho WHERE ho.day = days.day::date AND working=false)
AND (
extract(isodow from days.day) NOT IN (6,7)
OR EXISTS (SELECT * FROM holiday_overrides ho WHERE ho.day = days.day::date AND working=true)
)
GROUP BY period_id
)
SELECT periods.*, period_worktime.worktime
FROM periods JOIN period_worktime ON periods.id = period_worktime.period_id;
Пишу с телефона, потому не код а слова напишу:
Как насчет просто посчитать время рабочее краев диапазона плюс( (всего_дней минус празничных_или_выходных) умножить на 8)
Вообще расчет количества недель довольно быстро гуглится
www.sqlines.com/postgresql/how-to/datediff
+ конечно забыл про неполные недели для интервалов. Полагаю нужно вычислить день недели начала и конца интервалов, исключить эти дни и посчитать по 5 дневке
Начал писать с такой же логикой, закончил когда понял насколько это усложняет решение, и что в реальных кейсах (с интервалами допустим в пределах года), мы получаем увеличение O сложности в 1-4 раза по сравнению с оптимизированным, за счет сильного увеличения сложности алгоритма и ухудшения читаемости.
А практической необходимости такой оптимизации и вовсе придумать не смог.
Сложность оценивал как O(periods * holidays + periods + periods * holidays_overrides)
для оптимального, и O(periods * (holidays + periods_weekdays - holidays_overrides))
для решения с генерацией всех выходных. Исходя из того что нам нужно обрабатывать много периодов, но не столетия с кучей выходных.
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
SQL Error [42P01]: ОШИБКА: отношение «periods» не существует
Позиция: 162
Про задачку с интервалами сформулируйте более формально, чтобы было понятно какими входными данными оперировать и в какой форме результат получить. С удовольствием порешаю на досуге.
А вот компактность Вашего решения мне очень нравится.
with events as (num, event_time, event_type) as (
values
(1, '2019-01-01', 'start'),
(1, '2019-01-02', 'stop'),
(1, '2019-01-03', 'start'),
(2, '2019-01-04', 'start'),
(1, '2019-01-05', 'stop'),
(3, '2019-01-06', 'start'),
(3, '2019-01-07', 'stop'),
(2, '2019-01-08', 'stop'),
(3, '2019-01-09', 'start') )
Если допустить, что данные всегда корректны, то можно значительно проще получить решение. Сперва считаем для каждой временной отметки количество работающих сервисов, прибавляя единичку на событие start и вычитая на stop. Нужные нам интервалы будут те, когда количество сервисов равно нулю. С оконными функциями это можно посчитать в один проход:
with events as (num, event_time, event_type) as (
values
(1, '2019-01-01', 'start'),
(1, '2019-01-02', 'stop'),
(1, '2019-01-03', 'start'),
(2, '2019-01-04', 'start'),
(1, '2019-01-05', 'stop'),
(3, '2019-01-06', 'start'),
(3, '2019-01-07', 'stop'),
(2, '2019-01-08', 'stop'),
(3, '2019-01-09', 'start') ),
events_qty_per_time as (
select lag(event_time) over (order by event_time
range between unbounded preceding
and current row) as start_time
, event_time as stop_time
, sum( case when event_type = 'start' then 1
else -1 end) over (order by event_time
range between unbounded preceding
and current row) as qty
from events
order by event_time
)
select *
from events_qty_per_time
where qty = 0
Результат:
start_time | stop_time | qty
------------+------------+-----
2019-01-01 | 2019-01-02 | 0
2019-01-07 | 2019-01-08 | 0
(2 rows)
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)
with cte_periods(id, start_time, stop_time) as (
values
(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp),
(5, '2019-01-01 21:00:00'::timestamp, '2019-01-01 21:00:00'::timestamp),
(6, '2018-04-27 09:00:00'::timestamp, '2018-05-03 15:01:00'::timestamp),
(7, '2019-01-01 09:00:00'::timestamp, '2019-01-01 09:15:00'::timestamp)
), cte_holidays as (
select
h.date::date
from (values
('2018-01-01'),
('2018-01-02'),
('2018-01-03'),
('2018-01-04'),
('2018-01-05'),
('2018-01-07'),
('2018-01-08'),
('2018-02-23'),
('2018-03-08'),
('2018-04-30'),
('2018-05-01'),
('2018-05-02'),
('2018-05-09'),
('2018-06-12'),
('2018-11-04'),
('2019-01-01'),
('2019-01-02'),
('2019-01-03'),
('2019-01-04'),
('2019-01-05'),
('2019-01-07'),
('2019-01-08'),
('2019-02-23'),
('2019-03-08'),
('2019-05-01'),
('2019-05-09'),
('2019-06-12'),
('2019-11-04')
) as h(date)
), cte_working_weekend as (
select
h.date::date
from (values
('2018-04-28')
) as h(date)
), cte_holidays_no_weekends as (
select h.date
from cte_holidays as h
where
extract(isodow from h.date) not in (6,7)
), cte_periods_extended as (
select
p.id,
p.start_time as original_start_time,
p.stop_time as original_stop_time,
case
when p.start_time::time < '10:00' then '10:00'
when p.start_time::time > '19:00' then '19:00'
else p.start_time::time
end as start_time,
case
when p.stop_time::time < '10:00' then '10:00'
when p.stop_time::time > '19:00' then '19:00'
else p.stop_time::time
end as stop_time,
p.start_time::date as start_date,
p.stop_time::date as stop_date,
p.stop_time::date - p.start_time::date - 1 as days_count,
(p.stop_time::date - '2017-01-07'::date) / 7 - (p.start_time::date - '2017-01-07'::date) / 7 as saturdays_count,
(p.stop_time::date - '2017-01-08'::date) / 7 - (p.start_time::date - '2017-01-08'::date) / 7 as sundays_count
from cte_periods as p
)
select
p.id,
p.original_start_time as start_time,
p.original_stop_time as stop_time,
case
when p.start_date = p.stop_date then
p.stop_time - p.start_time
else
p.stop_time - p.start_time + '9:00' +
(p.days_count - p.saturdays_count - p.sundays_count - h.holidays_count + ww.working_weekends_count) * 9 * interval '1 hour'
end as work_hours
from cte_periods_extended as p
cross join lateral (
select count(*) from cte_holidays_no_weekends as tt where tt.date between p.start_date and p.stop_date
) as h(holidays_count)
cross join lateral (
select count(*) from cte_working_weekend as tt where tt.date between p.start_date and p.stop_date
) as ww(working_weekends_count)
У меня сошлись результаты запроса на небольшой тестовой выборке, но не сошлись на большой по реальным данным. Сходу ошибку не вижу, посмотрю ещё попозже.
('2018-06-09 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)
), ...
with cte_periods(id, start_time, stop_time) as (
values
(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp),
(5, '2019-01-01 21:00:00'::timestamp, '2019-01-01 21:00:00'::timestamp),
(6, '2018-06-09 16:51:40'::timestamp, '2018-06-10 10:55:50'::timestamp)
), cte_holidays as (
select
h.date::date
from (values
('2018-01-01'), -- 2018
('2018-01-02'),
('2018-01-03'),
('2018-01-04'),
('2018-01-05'),
('2018-01-08'),
('2018-02-23'),
('2018-03-08'),
('2018-03-09'),
('2018-05-01'),
('2018-05-02'),
('2018-05-09'),
('2018-06-11'),
('2018-06-12'),
('2018-11-05'),
('2018-12-31'),
('2019-01-01'), -- 2019
('2019-01-02'),
('2019-01-03'),
('2019-01-04'),
('2019-01-07'),
('2019-01-08'),
('2019-03-08'),
('2019-05-01'),
('2019-05-02'),
('2019-05-03'),
('2019-05-09'),
('2019-05-10'),
('2019-06-12'),
('2019-11-04')
) as h(date)
), cte_working_weekend as (
select
h.date::date
from (values
('2018-04-28'),
('2018-06-09'),
('2018-12-29')
) as h(date)
), cte_holidays_improved as (
select h.date
from cte_holidays as h
where
extract(isodow from h.date) not in (6,7)
)
select
p.id,
p.start_time,
p.stop_time,
coalesce(a1.days, 0) * interval '9 hour' + coalesce(a2.hours, interval '0 hour')
from cte_periods as p
cross join lateral (select p.start_time::date, p.stop_time::date) as d(start_date, stop_date)
cross join lateral (select d.stop_date - d.start_date) as k(date_diff)
left join lateral (
select
k.date_diff - 2 -
((d.stop_date - '0001-01-07'::date) / 7 - (d.start_date - '0001-01-05'::date) / 7) -
((d.stop_date - '0001-01-08'::date) / 7 - (d.start_date - '0001-01-06'::date) / 7) -
(select count(*) from cte_holidays_improved as tt where tt.date > d.start_date and tt.date < d.stop_date) +
(select count(*) from cte_working_weekend as tt where tt.date > d.start_date and tt.date < d.stop_date)
where
k.date_diff > 1
) as a1(days) on true
cross join lateral (
select sum((upper(b.hours) - lower(b.hours)))
from (
select d.start_date, p.start_time, d.start_date + '19:00'::time where k.date_diff >= 1 union all
select d.stop_date, d.stop_date + '10:00'::time, p.stop_time where k.date_diff >= 1 union all
select d.start_date, p.start_time, p.stop_time where k.date_diff = 0
) as a(date, start_time, stop_time)
cross join lateral (select
tsrange(a.start_time, a.stop_time, '[]') *
tsrange(a.date + '10:00'::time, a.date + '19:00'::time, '[]')
) as b(hours)
where
not exists (select * from cte_holidays_improved as tt where tt.date = a.date) and
(extract(isodow from a.date) not in (6,7) or exists (select * from cte_working_weekend as tt where tt.date = a.date))
) as a2(hours)
ERROR: range lower bound must be less than or equal to range upper bound
на данных:
('2019-05-23 16:51:40', '2019-05-24 02:55:50')
with cte_periods(id, start_time, stop_time) as (
values
(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp),
(5, '2019-01-01 21:00:00'::timestamp, '2019-01-01 21:00:00'::timestamp),
(6, '2018-06-09 16:51:40'::timestamp, '2018-06-10 10:55:50'::timestamp),
(7, '2019-05-23 16:51:40'::timestamp, '2019-05-24 02:55:50'::timestamp)
), cte_holidays as (
select
h.date::date
from (values
('2018-01-01'), -- 2018
('2018-01-02'),
('2018-01-03'),
('2018-01-04'),
('2018-01-05'),
('2018-01-08'),
('2018-02-23'),
('2018-03-08'),
('2018-03-09'),
('2018-05-01'),
('2018-05-02'),
('2018-05-09'),
('2018-06-11'),
('2018-06-12'),
('2018-11-05'),
('2018-12-31'),
('2019-01-01'), -- 2019
('2019-01-02'),
('2019-01-03'),
('2019-01-04'),
('2019-01-07'),
('2019-01-08'),
('2019-03-08'),
('2019-05-01'),
('2019-05-02'),
('2019-05-03'),
('2019-05-09'),
('2019-05-10'),
('2019-06-12'),
('2019-11-04')
) as h(date)
), cte_working_weekend as (
select
h.date::date
from (values
('2018-04-28'),
('2018-06-09'),
('2018-12-29')
) as h(date)
), cte_holidays_improved as (
select h.date
from cte_holidays as h
where
extract(isodow from h.date) not in (6,7)
)
select
p.id,
p.start_time,
p.stop_time,
coalesce(a1.days, 0) * interval '9 hour' + coalesce(a2.hours, interval '0 hour')
from cte_periods as p
cross join lateral (select p.start_time::date, p.stop_time::date) as d(start_date, stop_date)
cross join lateral (select d.stop_date - d.start_date) as k(date_diff)
left join lateral (
select
k.date_diff - 2 -
((d.stop_date - '0001-01-07'::date) / 7 - (d.start_date - '0001-01-05'::date) / 7) -
((d.stop_date - '0001-01-08'::date) / 7 - (d.start_date - '0001-01-06'::date) / 7) -
(select count(*) from cte_holidays_improved as tt where tt.date > d.start_date and tt.date < d.stop_date) +
(select count(*) from cte_working_weekend as tt where tt.date > d.start_date and tt.date < d.stop_date)
where
k.date_diff > 1
) as a1(days) on true
cross join lateral (
select sum((upper(b.hours) - lower(b.hours)))
from (
select d.start_date, p.start_time, d.start_date + '19:00'::time where k.date_diff >= 1 union all
select d.stop_date, d.stop_date + '10:00'::time, p.stop_time where k.date_diff >= 1 union all
select d.start_date, p.start_time, p.stop_time where k.date_diff = 0
) as a(date, start_time, stop_time)
cross join lateral (select
tsrange(a.start_time, a.stop_time, '[]') *
tsrange(a.date + '10:00'::time, a.date + '19:00'::time, '[]')
) as b(hours)
where
a.start_time <= a.stop_time and
not exists (select * from cte_holidays_improved as tt where tt.date = a.date) and
(extract(isodow from a.date) not in (6,7) or exists (select * from cte_working_weekend as tt where tt.date = a.date))
) as a2(hours)
with cte_periods(id, start_time, stop_time) as (
values
(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp),
(5, '2019-01-01 21:00:00'::timestamp, '2019-01-01 21:00:00'::timestamp),
(6, '2018-06-09 16:51:40'::timestamp, '2018-06-10 10:55:50'::timestamp),
(7, '2019-05-23 16:51:40'::timestamp, '2019-05-24 02:55:50'::timestamp),
(8, '2018-06-10 18:46:10'::timestamp, '2018-06-13 10:18:18'::timestamp)
), cte_holidays as (
select
h.date::date
from (values
('2018-01-01'), -- 2018
('2018-01-02'),
('2018-01-03'),
('2018-01-04'),
('2018-01-05'),
('2018-01-08'),
('2018-02-23'),
('2018-03-08'),
('2018-03-09'),
('2018-05-01'),
('2018-05-02'),
('2018-05-09'),
('2018-06-11'),
('2018-06-12'),
('2018-11-05'),
('2018-12-31'),
('2019-01-01'), -- 2019
('2019-01-02'),
('2019-01-03'),
('2019-01-04'),
('2019-01-07'),
('2019-01-08'),
('2019-03-08'),
('2019-05-01'),
('2019-05-02'),
('2019-05-03'),
('2019-05-09'),
('2019-05-10'),
('2019-06-12'),
('2019-11-04')
) as h(date)
), cte_working_weekend as (
select
h.date::date
from (values
('2018-04-28'),
('2018-06-09'),
('2018-12-29')
) as h(date)
), cte_holidays_improved as (
select h.date
from cte_holidays as h
where
extract(isodow from h.date) not in (6,7)
)
select
p.id,
p.start_time,
p.stop_time,
case when a1.days > 0 then a1.days else 0 end * interval '9 hour' + coalesce(a2.hours, interval '0 hour')
from cte_periods as p
cross join lateral (select p.start_time::date, p.stop_time::date) as d(start_date, stop_date)
cross join lateral (select d.stop_date - d.start_date) as k(date_diff)
left join lateral (
select
k.date_diff - 2 -
((d.stop_date - '0001-01-07'::date) / 7 - (d.start_date - '0001-01-05'::date) / 7) -
((d.stop_date - '0001-01-08'::date) / 7 - (d.start_date - '0001-01-06'::date) / 7) -
(select count(*) from cte_holidays_improved as tt where tt.date > d.start_date and tt.date < d.stop_date) +
(select count(*) from cte_working_weekend as tt where tt.date > d.start_date and tt.date < d.stop_date)
) as a1(days) on true
cross join lateral (
select sum((upper(b.hours) - lower(b.hours)))
from (
select d.start_date, p.start_time, d.start_date + '19:00'::time where k.date_diff >= 1 union all
select d.stop_date, d.stop_date + '10:00'::time, p.stop_time where k.date_diff >= 1 union all
select d.start_date, p.start_time, p.stop_time where k.date_diff = 0
) as a(date, start_time, stop_time)
cross join lateral (select
tsrange(a.start_time, a.stop_time, '[]') *
tsrange(a.date + '10:00'::time, a.date + '19:00'::time, '[]')
) as b(hours)
where
a.start_time <= a.stop_time and
not exists (select * from cte_holidays_improved as tt where tt.date = a.date) and
(extract(isodow from a.date) not in (6,7) or exists (select * from cte_working_weekend as tt where tt.date = a.date))
) as a2(hours)
Про тестирование. Я никаких тестовых данных не давал, это ж задачка на «размять мозги». Я продемонстрировал только как выглядят входные данные, и как должен выглядеть результат. Программист сам должен знать, на каких данных следует проверить заложенные в решение допущения.
Программист сам должен знать, на каких данных следует проверить заложенные в решение допущения— это бесспорно, к сожалению, не готов тратить достаточное количество времени на создание хорошей тестовой выборки (и продумывать возможныe edge case). Взялся писать, потому что люблю Postgres и никто не предложил решения с подобной логикой, было интересно, насколько компактным можно сделать решение с данным подходом. Пока получается длиннее чем хотелось бы.
with cte_periods(id, start_time, stop_time) as (
values
(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp),
(5, '2019-01-01 21:00:00'::timestamp, '2019-01-01 21:00:00'::timestamp),
(6, '2018-06-09 16:51:40'::timestamp, '2018-06-10 10:55:50'::timestamp),
(7, '2019-05-23 16:51:40'::timestamp, '2019-05-24 02:55:50'::timestamp),
(8, '2018-06-10 18:46:10'::timestamp, '2018-06-13 10:18:18'::timestamp),
(9, '2018-05-24 17:31:12'::timestamp, '2018-05-26 17:59:39'::timestamp),
(10, '2018-05-23 17:31:12'::timestamp, '2018-05-26 17:59:39'::timestamp),
(11, '2019-03-30 07:00:00'::timestamp, '2019-04-07 14:00:00'::timestamp)
), cte_holidays as (
select
h.date::date
from (values
('2018-01-01'), -- 2018
('2018-01-02'),
('2018-01-03'),
('2018-01-04'),
('2018-01-05'),
('2018-01-08'),
('2018-02-23'),
('2018-03-08'),
('2018-03-09'),
('2018-05-01'),
('2018-05-02'),
('2018-05-09'),
('2018-06-11'),
('2018-06-12'),
('2018-11-05'),
('2018-12-31'),
('2019-01-01'), -- 2019
('2019-01-02'),
('2019-01-03'),
('2019-01-04'),
('2019-01-07'),
('2019-01-08'),
('2019-03-08'),
('2019-05-01'),
('2019-05-02'),
('2019-05-03'),
('2019-05-09'),
('2019-05-10'),
('2019-06-12'),
('2019-11-04')
) as h(date)
), cte_working_weekend as (
select
h.date::date
from (values
('2018-04-28'),
('2018-06-09'),
('2018-12-29')
) as h(date)
), cte_holidays_improved as (
select h.date
from cte_holidays as h
where
extract(isodow from h.date) not in (6,7)
)
select
p.id,
p.start_time,
p.stop_time,
(select count(*) from cte_holidays_improved as tt where tt.date > d.start_date and tt.date < d.stop_date),
(select count(*) from cte_working_weekend as tt where tt.date > d.start_date and tt.date < d.stop_date),
case when a1.days > 0 then a1.days else 0 end * interval '9 hour' + coalesce(a2.hours, interval '0 hour')
from cte_periods as p
cross join lateral (select p.start_time::date, p.stop_time::date) as d(start_date, stop_date)
cross join lateral (select d.stop_date - d.start_date) as k(date_diff)
left join lateral (
select
k.date_diff - 1 -
-- amount of Saturdays between 2 days:
-- we know that '0001-01-06' is a Saturday, so number of Saturdays between 2 days will be
-- number of Saturdays between stop_date and '0001-01-06' - number of Saturdays between (start_date - 1) and '0001-01-06'
-- we calculate here amount of Saturdays / Sundays between start_date + 1 and stop_date - 1
((d.stop_date - '0001-01-07'::date) / 7 - (d.start_date - '0001-01-06'::date) / 7) -
((d.stop_date - '0001-01-08'::date) / 7 - (d.start_date - '0001-01-07'::date) / 7) -
(select count(*) from cte_holidays_improved as tt where tt.date > d.start_date and tt.date < d.stop_date) +
(select count(*) from cte_working_weekend as tt where tt.date > d.start_date and tt.date < d.stop_date)
) as a1(days) on true
cross join lateral (
select sum((upper(b.hours) - lower(b.hours)))
from (
select d.start_date, p.start_time, d.start_date + '19:00'::time where k.date_diff >= 1 union all
select d.stop_date, d.stop_date + '10:00'::time, p.stop_time where k.date_diff >= 1 union all
select d.start_date, p.start_time, p.stop_time where k.date_diff = 0
) as a(date, start_time, stop_time)
cross join lateral (select
tsrange(a.start_time, a.stop_time, '[]') *
tsrange(a.date + '10:00'::time, a.date + '19:00'::time, '[]')
) as b(hours)
where
a.start_time <= a.stop_time and
not exists (select * from cte_holidays_improved as tt where tt.date = a.date) and
(extract(isodow from a.date) not in (6,7) or exists (select * from cte_working_weekend as tt where tt.date = a.date))
) as a2(hours);
Взялся писать, потому что люблю Postgres и никто не предложил решения с подобной логикой, было интересно, насколько компактным можно сделать решение с данным подходом.
… И это замечательно. Полностью Вас поддерживаю, мне тоже было интересно, что получится из этого подхода.
«Программист сам должен ...» — это был ни разу не наезд, это как раз была ирония. А то ведь следующим шагом понадобится оформить ТЗ…
Надо мне, наверное, больше использовать смаликов, а то заминусуют. (:
Заявку заводит человек внешний к тому кто работает в рамках рабочих часов SLA.
Клиент тех поддержки написал что у него ничего не работает, а потом понял что у него локальная проблема и закрыл её.
А сам пример, с моей точки зрения, намекает что нужно для таких интервалов строку в результате всё же показывать.
А вот, например, изначально вообще не выводил такой интервал в результате.
Во-вторых, у нас может быть гибкий график. Или в рабочее время человек убежал по своим делам, а вечером задержался (или утром пораньше начал), чтобы успеть доделать всё вовремя.
В-третьих, у нас могут быть офисы в разных часовых поясах. Или в разных городах. В городах поменьше любят график работы с 09 до 18, а в Москве-Питере с 10 до 19.
В-четвёртых, ночной дежурный скучал, от нечего делать подчистил всякие недозакрытые задачки. А может и какую-то свою работу доделал и закрыл.
В-пятых, клиент обратился в нерабочее время, а потом понял, что ложная тревога, или сам справился и закрыл.
В-шестых, бывают запланированные работы в нерабочее время. Каждый dba знает, что кое-что надо делать в отсутствие пользователей.
В-седьмых, иногда в IT бывают авралы, когда просто нужно сделать. Лучше, когда это редко (а ещё лучше если никогда), но иногда бывает.
В-восьмых, пришёл робот и автоматически поменял статусы, просроченное по таймаутам закрыл.
Ну короче не только по пьяни в нерабочее время что-то происходит на работе. Надеюсь, достаточно привёл жизненных примеров. (:
-- https://habr.com/ru/company/postgrespro/blog/448368/
with periods(id, start_time, stop_time) as (
values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
-- http://data.gov.ru/opendata/resource/8ba5011a-233e-4e01-a1d2-ff5598d0f34f#2/0.0/0.0
-- All this block can be persisted in real table
holidays_raw(year, jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec) as (
values(2018,'1,2,3,4,5,6,7,8,13,14,20,21,27,28','3,4,10,11,17,18,22*,23,24,25','3,4,7*,8,9,10,11,17,18,24,25,31','1,7,8,14,15,21,22,28*,29,30','1,2,5,6,8*,9,12,13,19,20,26,27','2,3,9*,10,11,12,16,17,23,24,30','1,7,8,14,15,21,22,28,29','4,5,11,12,18,19,25,26','1,2,8,9,15,16,22,23,29,30','6,7,13,14,20,21,27,28','3,4,5,10,11,17,18,24,25','1,2,8,9,15,16,22,23,29*,30,31'),
(2019,'1,2,3,4,5,6,7,8,12,13,19,20,26,27','2,3,9,10,16,17,22*,23,24','2,3,7*,8,9,10,16,17,23,24,30,31','6,7,13,14,20,21,27,28,30*','1,2,3,4,5,8*,9,10,11,12,18,19,25,26','1,2,8,9,11*,12,15,16,22,23,29,30','6,7,13,14,20,21,27,28','3,4,10,11,17,18,24,25,31','1,7,8,14,15,21,22,28,29','5,6,12,13,19,20,26,27','2,3,4,9,10,16,17,23,24,30','1,7,8,14,15,21,22,28,29,31*')
),
holidays_by_month(year, month, day) as (
select year, 1, unnest(string_to_array(jan, ',')) from holidays_raw
union all
select year, 2, unnest(string_to_array(feb, ',')) from holidays_raw
union all
select year, 3, unnest(string_to_array(mar, ',')) from holidays_raw
union all
select year, 4, unnest(string_to_array(apr, ',')) from holidays_raw
union all
select year, 5, unnest(string_to_array(may, ',')) from holidays_raw
union all
select year, 6, unnest(string_to_array(jun, ',')) from holidays_raw
union all
select year, 7, unnest(string_to_array(jul, ',')) from holidays_raw
union all
select year, 8, unnest(string_to_array(aug, ',')) from holidays_raw
union all
select year, 9, unnest(string_to_array(sep, ',')) from holidays_raw
union all
select year, 10, unnest(string_to_array(oct, ',')) from holidays_raw
union all
select year, 11, unnest(string_to_array(nov, ',')) from holidays_raw
union all
select year, 12, unnest(string_to_array(dec, ',')) from holidays_raw
),
holidays(day) as (
select concat(year, '-', month, '-', day)::date
from holidays_by_month
-- Звёздочкой помечены сокращённые дни, но по условию задачи они считаются рабочими
where day NOT like '%*'
),
-- Календарь всех дней на нужный период
calendar(day) as (
select generate_series(min(start_time), max(stop_time), '1 day')::date from periods
),
-- Только рабочие дни в рамках нашего календаря
workdays(day, begin, until) as (
select C.day, C.day + '10 hours'::interval, C.day + '19 hours'::interval
from calendar AS C
-- todo Тут явно можно сделать оптимальнее
where (C.day NOT IN (select day from holidays AS H where H.day = C.day))
and (
(EXTRACT(DOW FROM C.day) between 1 and 5)
or
-- Сокращённый день считается полностью рабочим даже если это суббота
exists (
select 1
from holidays_by_month AS HH
where HH.year = EXTRACT(YEAR FROM C.day)
and HH.month = EXTRACT(MONTH FROM C.day)
and HH.day = concat(EXTRACT(DAY FROM C.day), '*')
)
)
)
select P.id
-- , P.start_time, P.stop_time
-- , W.*
-- , LEAST(P.stop_time, W.until) - GREATEST(W.begin, P.start_time)
, to_char(max(P.start_time), 'YYYY-MM-DD HH24:MI:SS') as start_time
, to_char(max(P.stop_time), 'YYYY-MM-DD HH24:MI:SS') as stop_time
, to_char(sum(LEAST(P.stop_time, W.until) - GREATEST(W.begin, P.start_time)), 'HH24:MI:SS') as work_hrs
from periods AS P
left outer join workdays AS W ON (W.day between P.start_time::date AND P.stop_time::date)
group by P.id
order by 1, 4
;
Уверен что есть куда оптимизировать :)
Ну и календарь выходных, по хорошему, лучше персистить в БД чем рассчитывать при каждом запросе.
id | start_time | stop_time | work_hrs
----+---------------------+---------------------+----------
1 | 2019-03-29 07:00:00 | 2019-04-08 14:00:00 | 58:00:00
2 | 2019-04-10 07:00:00 | 2019-04-10 20:00:00 | 09:00:00
3 | 2019-04-11 12:00:00 | 2019-04-12 16:07:12 | 13:07:12
4 | 2018-12-28 12:00:00 | 2019-01-16 16:00:00 | 67:00:00
P.S.
Мой вариант работает на PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
.
('2018-08-20 17:55:57', '2018-08-21 02:45:09')
даёт неожиданный результат:
2018-08-20 17:55:57 | 2018-08-21 02:45:09 | -06:-10:-48
Да, такой вариант не был учтён.
https://rextester.com/QKMKGO22854
-- https://habr.com/ru/company/postgrespro/blog/448368/
with periods(id, start_time, stop_time) as (
values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp),
(15, '2019-01-01 21:00:00'::timestamp, '2019-01-01 21:00:00'::timestamp),
(16, '2018-08-20 17:55:57'::timestamp, '2018-08-21 02:45:09'::timestamp)
),
-- http://data.gov.ru/opendata/resource/8ba5011a-233e-4e01-a1d2-ff5598d0f34f#2/0.0/0.0
-- All this block can be persisted in real table
holidays_raw(year, jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec) as (
values(2018,'1,2,3,4,5,6,7,8,13,14,20,21,27,28','3,4,10,11,17,18,22*,23,24,25','3,4,7*,8,9,10,11,17,18,24,25,31','1,7,8,14,15,21,22,28*,29,30','1,2,5,6,8*,9,12,13,19,20,26,27','2,3,9*,10,11,12,16,17,23,24,30','1,7,8,14,15,21,22,28,29','4,5,11,12,18,19,25,26','1,2,8,9,15,16,22,23,29,30','6,7,13,14,20,21,27,28','3,4,5,10,11,17,18,24,25','1,2,8,9,15,16,22,23,29*,30,31'),
(2019,'1,2,3,4,5,6,7,8,12,13,19,20,26,27','2,3,9,10,16,17,22*,23,24','2,3,7*,8,9,10,16,17,23,24,30,31','6,7,13,14,20,21,27,28,30*','1,2,3,4,5,8*,9,10,11,12,18,19,25,26','1,2,8,9,11*,12,15,16,22,23,29,30','6,7,13,14,20,21,27,28','3,4,10,11,17,18,24,25,31','1,7,8,14,15,21,22,28,29','5,6,12,13,19,20,26,27','2,3,4,9,10,16,17,23,24,30','1,7,8,14,15,21,22,28,29,31*')
),
holidays_by_month(year, month, day) as (
select year, 1, unnest(string_to_array(jan, ',')) from holidays_raw
union all
select year, 2, unnest(string_to_array(feb, ',')) from holidays_raw
union all
select year, 3, unnest(string_to_array(mar, ',')) from holidays_raw
union all
select year, 4, unnest(string_to_array(apr, ',')) from holidays_raw
union all
select year, 5, unnest(string_to_array(may, ',')) from holidays_raw
union all
select year, 6, unnest(string_to_array(jun, ',')) from holidays_raw
union all
select year, 7, unnest(string_to_array(jul, ',')) from holidays_raw
union all
select year, 8, unnest(string_to_array(aug, ',')) from holidays_raw
union all
select year, 9, unnest(string_to_array(sep, ',')) from holidays_raw
union all
select year, 10, unnest(string_to_array(oct, ',')) from holidays_raw
union all
select year, 11, unnest(string_to_array(nov, ',')) from holidays_raw
union all
select year, 12, unnest(string_to_array(dec, ',')) from holidays_raw
),
holidays(day) as (
select concat(year, '-', month, '-', day)::date
from holidays_by_month
-- Звёздочкой помечены сокращённые дни, но по условию задачи они считаются рабочими
where day NOT like '%*'
),
-- Календарь всех дней на нужный период
calendar(day) as (
select generate_series(min(start_time), max(stop_time), '1 day')::date from periods
),
-- Только рабочие дни в рамках нашего календаря
workdays(day, begin, until) as (
select C.day, C.day + '10 hours'::interval, C.day + '19 hours'::interval
from calendar AS C
-- todo Тут явно можно сделать оптимальнее
where (C.day NOT IN (select day from holidays AS H where H.day = C.day))
and (
(EXTRACT(DOW FROM C.day) between 1 and 5)
or
-- Сокращённый день считается полностью рабочим даже если это суббота
exists (
select 1
from holidays_by_month AS HH
where HH.year = EXTRACT(YEAR FROM C.day)
and HH.month = EXTRACT(MONTH FROM C.day)
and HH.day = concat(EXTRACT(DAY FROM C.day), '*')
)
)
)
select P.id
-- , P.start_time, P.stop_time
-- , W.*
-- , GREATEST('0'::interval, LEAST(P.stop_time, W.until) - GREATEST(W.begin, P.start_time))
, to_char(max(P.start_time), 'YYYY-MM-DD HH24:MI:SS') as start_time
, to_char(max(P.stop_time), 'YYYY-MM-DD HH24:MI:SS') as stop_time
, to_char(sum(GREATEST('0'::interval, LEAST(P.stop_time, W.until) - GREATEST(W.begin, P.start_time))), 'HH24:MI:SS') as work_hrs
from periods AS P
left outer join workdays AS W ON (W.day between P.start_time::date AND P.stop_time::date)
group by P.id
order by 1, 4
id | start_time | stop_time | work_hrs
----+---------------------+---------------------+----------
1 | 2019-03-29 07:00:00 | 2019-04-08 14:00:00 | 58:00:00
2 | 2019-04-10 07:00:00 | 2019-04-10 20:00:00 | 09:00:00
3 | 2019-04-11 12:00:00 | 2019-04-12 16:07:12 | 07:00:00
4 | 2018-12-28 12:00:00 | 2019-01-16 16:00:00 | 67:00:00
15 | 2019-01-01 21:00:00 | 2019-01-01 21:00:00 | 00:00:00
16 | 2018-08-20 17:55:57 | 2018-08-21 02:45:09 | 01:04:03
На каких данных некорректно работает пока не понял. Чуть позже отвечу, где ошибка.
with periods (id, start_time, stop_time) as (
values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
)
select periods.*, hours from (
select id, sum(upper(hours) - lower(hours)) hours
from (
select working_day::timestamp from periods
join generate_series(start_time::date, stop_time::date, '1 day') working_day on extract(isodow from working_day::timestamp) < 6
except -- holidays
select * from unnest('{2019-01-01,2019-01-02,2019-01-03,2019-01-04,2019-01-07,2019-01-08,2019-03-08,2019-05-01,2019-05-02,2019-05-03,2019-05-09,2019-05-10,2019-06-12,2019-11-04,2018-01-01,2018-01-02,2018-01-03,2018-01-04,2018-01-05,2018-01-08,2018-02-23,2018-03-08,2018-03-09,2018-04-30,2018-05-01,2018-05-02,2018-05-09,2018-06-11,2018-06-12,2018-11-05,2018-12-31}'::date[])
union -- working weekends
select * from unnest('{2018-04-28,2018-06-09,2018-12-29}'::date[])
)_, tsrange(working_day + '10:00', working_day + '19:00') working_time
join (periods cross join tsrange(start_time, stop_time) period_range) on period_range && working_time,
tsrange(period_range * working_time) hours
group by 1
)_ join periods using (id)
with periods (id, start_time, stop_time) as (
values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
)
select *, (
select sum(upper(hours) - lower(hours)) hours
from generate_series(start_time::date, stop_time::date, '1 day')_, "timestamp"(_) period_day,
tsrange(period_day + '10:00', period_day + '19:00') working_time
join tsrange(start_time, stop_time) period_range on period_range && working_time,
tsrange(period_range * working_time) hours
where
extract(isodow from period_day) < 6
and period_day <> all('{2019-01-01,2019-01-02,2019-01-03,2019-01-04,2019-01-07,2019-01-08,2019-03-08,2019-05-01,2019-05-02,2019-05-03,2019-05-09,2019-05-10,2019-06-12,2019-11-04,2018-01-01,2018-01-02,2018-01-03,2018-01-04,2018-01-05,2018-01-08,2018-02-23,2018-03-08,2018-03-09,2018-04-30,2018-05-01,2018-05-02,2018-05-09,2018-06-11,2018-06-12,2018-11-05,2018-12-31}')
or period_day = any('{2018-04-28,2018-06-09,2018-12-29}')
) from periods
from generate_series(start_time::date, stop_time::date, '1 day')_, "timestamp"(_) period_day,
from generate_series(date_trunc('day', start_time), date_trunc('day', stop_time), '1 day') period_day,
from generate_series(start_time::date::timestamp, stop_time::date::timestamp, '1 day') period_day,
(
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
Ещё и формат поехал. Переписал. Но, думаю, это решение довольно популярно
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
Подход может и не оригинален, но всё равно спасибо за участие. Не так много народу вообще показало работающие запросы.
Работу в выходные учёл. Можно ещё и обеды учесть, если надо
with wt_periods(id, start_time, stop_time) as
(
values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp)
, (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp)
, (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp)
, (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
)
,wt_holyday(id, day_off, comment) as
(
values (79, '2018-01-01 00:00:00' :: timestamp, 'НГ')
, (80, '2018-01-02 00:00:00' :: timestamp, 'НГ')
, (81, '2018-01-03 00:00:00' :: timestamp, 'НГ')
, (82, '2018-01-04 00:00:00' :: timestamp, 'НГ')
, (83, '2018-01-05 00:00:00' :: timestamp, 'НГ')
, (84, '2018-01-08 00:00:00' :: timestamp, 'НГ')
, (85, '2018-02-23 00:00:00' :: timestamp, 'ДЗО')
, (86, '2018-03-08 00:00:00' :: timestamp, 'МЖД')
, (87, '2018-03-09 00:00:00' :: timestamp, 'МЖД')
, (88, '2018-04-30 00:00:00' :: timestamp, 'Первомай')
, (89, '2018-05-01 00:00:00' :: timestamp, 'Первомай')
, (90, '2018-05-02 00:00:00' :: timestamp, 'Первомай')
, (91, '2018-05-09 00:00:00' :: timestamp, 'День Победы')
, (92, '2018-06-11 00:00:00' :: timestamp, 'День России')
, (93, '2018-06-12 00:00:00' :: timestamp, 'День России')
, (94, '2018-11-05 00:00:00' :: timestamp, 'День народного единства')
, (95, '2018-12-31 00:00:00' :: timestamp, 'НГ')
, (96, '2019-01-01 00:00:00' :: timestamp, 'НГ')
, (97, '2019-01-02 00:00:00' :: timestamp, 'НГ')
, (98, '2019-01-03 00:00:00' :: timestamp, 'НГ')
, (99, '2019-01-04 00:00:00' :: timestamp, 'НГ')
, (100, '2019-01-07 00:00:00' :: timestamp, 'НГ')
, (101, '2019-01-08 00:00:00' :: timestamp, 'НГ')
, (102, '2019-03-08 00:00:00' :: timestamp, 'МЖД')
, (103, '2019-05-01 00:00:00' :: timestamp, 'Первомай')
, (104, '2019-05-02 00:00:00' :: timestamp, 'Первомай')
, (105, '2019-05-03 00:00:00' :: timestamp, 'Первомай')
, (106, '2019-05-09 00:00:00' :: timestamp, 'День Победы')
, (107, '2019-05-10 00:00:00' :: timestamp, 'День Победы')
, (108, '2019-06-12 00:00:00' :: timestamp, 'День России')
, (109, '2019-11-04 00:00:00' :: timestamp, 'День народного единства')
)
,wt_slave_holyday(id, day_on, comment) as
(
values (201, '2018-04-28 00:00:00' :: timestamp, 'Из-за первомая')
, (202, '2018-06-09 00:00:00' :: timestamp, 'Из-за дня России')
, (203, '2018-12-29 00:00:00' :: timestamp, 'Из-за НГ')
)
,wt_par as
(
select
id
,start_time :: timestamp as i_from_dt
,stop_time :: timestamp as i_to_dt
,10 :: numeric as i_start_slave_time
,19 :: numeric as i_stop_slave_time
from
wt_periods
)
,wt_gen_dt as
(
select
pa1.id
,pa1.i_from_dt
,pa1.i_to_dt
,pa1.i_start_slave_time
,pa1.i_stop_slave_time
,dt
,case
when sh1.day_on is not null
then
1
when sh1.day_on is null
and (
extract(dow from dt) in (6, 0)
or h1.day_off is not null
)
then
0
else
1
end as is_slave_day
from wt_par pa1
cross join generate_series(
date_trunc('day', pa1.i_from_dt)
, date_trunc('day', pa1.i_to_dt)
, '24 hours' :: interval
) as dt
left join wt_holyday h1 --табличка с праздничными днями
on h1.day_off = dt
left join wt_slave_holyday sh1
on sh1.day_on = dt
)
select
id
,i_from_dt
,i_to_dt
,sum( case --дата конца раб дня
when is_slave_day = 0
then
dt + interval '0 hours'
else
greatest(
least(
dt + interval '1 hours' * i_stop_slave_time
, i_to_dt
)
, dt + interval '1 hours' * i_start_slave_time
, i_from_dt
)
end
-
case --дата начала раб дня
when is_slave_day = 0
then
dt + interval '0 hours'
else
greatest(
dt + interval '1 hours' * i_start_slave_time
, i_from_dt
)
end ) as slave_time_mi
from
wt_gen_dt
group by
id
,i_from_dt
,i_to_dt
with periods(id, start_time, stop_time) as (
values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
holidays(period) as (
values (tsrange('2018-01-01 00:00:00', '2018-01-08 23:59:59')),
(tsrange('2018-02-23 00:00:00', '2018-02-23 23:59:59')),
(tsrange('2018-03-08 00:00:00', '2018-03-09 23:59:59')),
(tsrange('2018-04-30 00:00:00', '2018-05-02 23:59:59')),
(tsrange('2018-05-09 00:00:00', '2018-05-09 23:59:59')),
(tsrange('2018-06-11 00:00:00', '2018-06-12 23:59:59')),
(tsrange('2018-11-05 00:00:00', '2018-11-05 23:59:59')),
(tsrange('2018-12-31 00:00:00', '2019-01-08 23:59:59')),
(tsrange('2019-03-08 00:00:00', '2019-03-08 23:59:59')),
(tsrange('2019-05-01 00:00:00', '2019-05-03 23:59:59')),
(tsrange('2019-05-09 00:00:00', '2019-05-10 23:59:59')),
(tsrange('2019-06-12 00:00:00', '2019-06-12 23:59:59')),
(tsrange('2019-11-04 00:00:00', '2019-11-04 23:59:59')),
(tsrange('2019-11-04 00:00:00', '2019-11-04 23:59:59'))
),
transfered_work_days(d) as (
values ('2018-04-28'),
('2018-06-09'),
('2018-12-29')
)
select id, sum(work_time)
from (
select id,
least(stop_time, d::date + '19h'::interval)::timestamp -
greatest(start_time, d::date + '10h'::interval)::timestamp as work_time
from (
select id, generate_series(start_time::date, stop_time::date, '1d'::interval) d, start_time, stop_time from periods
) as days
where (exists(select true from transfered_work_days twd where twd.d::date = days.d::date limit 1) or
extract(isodow from days.d) not in (6, 7))
and not exists(select true from holidays where period @> (days.d::timestamp) limit 1)
) _
group by id
order by id
Спасибо, за участие, но есть недочёты:
1) теряются из вывода периоды, не попадающие на рабочее время, и
2) некорректная работа на периодах типа ('2019-05-23 16:51:40', '2019-05-24 02:55:50').
Поправил по замечаниям
with periods(id, start_time, stop_time) as (
values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp),
(5, '2019-05-23 16:51:40'::timestamp, '2019-05-24 02:55:50'::timestamp),
(6, '2019-05-25 19:51:40'::timestamp, '2019-05-25 23:55:50'::timestamp)
),
holidays(period) as (
values (tsrange('2018-01-01 00:00:00', '2018-01-08 23:59:59')),
(tsrange('2018-02-23 00:00:00', '2018-02-23 23:59:59')),
(tsrange('2018-03-08 00:00:00', '2018-03-09 23:59:59')),
(tsrange('2018-04-30 00:00:00', '2018-05-02 23:59:59')),
(tsrange('2018-05-09 00:00:00', '2018-05-09 23:59:59')),
(tsrange('2018-06-11 00:00:00', '2018-06-12 23:59:59')),
(tsrange('2018-11-05 00:00:00', '2018-11-05 23:59:59')),
(tsrange('2018-12-31 00:00:00', '2019-01-08 23:59:59')),
(tsrange('2019-03-08 00:00:00', '2019-03-08 23:59:59')),
(tsrange('2019-05-01 00:00:00', '2019-05-03 23:59:59')),
(tsrange('2019-05-09 00:00:00', '2019-05-10 23:59:59')),
(tsrange('2019-06-12 00:00:00', '2019-06-12 23:59:59')),
(tsrange('2019-11-04 00:00:00', '2019-11-04 23:59:59')),
(tsrange('2019-11-04 00:00:00', '2019-11-04 23:59:59'))
),
transfered_work_days(d) as (
values ('2018-04-28'),
('2018-06-09'),
('2018-12-29')
)
select p.*, sum(work_time)
from periods p
left join (
select id,
greatest(d::date + '10h'::interval, least(stop_time, d::date + '19h'::interval))::timestamp -
least(d::date + '19h'::interval, greatest(start_time, d::date + '10h'::interval))::timestamp as work_time
from (
select id, generate_series(start_time::date, stop_time::date, '1d'::interval) d, start_time, stop_time from periods
) as days
where (exists(select true from transfered_work_days twd where twd.d::date = days.d::date limit 1) or
extract(isodow from days.d) not in (6, 7))
and not exists(select true from holidays where period @> (days.d::timestamp) limit 1)
) wt on wt.id = p.id
group by p.id, p.start_time, p.stop_time
order by p.id
ХМ) А можно интервал на котором такая проблема есть? а то не хочется решать следствие коалеском)
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
with
periods(id, start_time, stop_time) as ( values
(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
holidays(date_key) as ( values
('2018-01-01'::date), ('2018-01-02'::date), ('2018-01-03'::date), ('2018-01-04'::date), ('2018-01-05'::date), ('2018-01-08'::date), ('2018-02-23'::date), ('2018-03-08'::date), ('2018-03-09'::date), ('2018-04-30'::date), ('2018-05-01'::date), ('2018-05-02'::date), ('2018-05-09'::date), ('2018-06-11'::date), ('2018-06-12'::date), ('2018-11-05'::date), ('2018-12-31'::date),
('2019-01-01'::date), ('2019-01-02'::date), ('2019-01-03'::date), ('2019-01-04'::date), ('2019-01-07'::date), ('2019-01-08'::date), ('2019-03-08'::date), ('2019-05-01'::date), ('2019-05-02'::date), ('2019-05-03'::date), ('2019-05-09'::date), ('2019-05-10'::date), ('2019-06-12'::date), ('2019-11-04'::date)
),
work_holidays(date_key) as ( values
('2018-04-28'::date), ('2018-06-09'::date), ('2018-12-29'::date)
)
select p.id, p.start_time, p.stop_time,
-- hours from start_time
case
when start_time::date = stop_time::date or start_time::date = min(h.time_key) then '00:00:00'::interval
else '19:00:00'::time - greatest(least(start_time::time, '19:00:00'::time), '10:00:00'::time)
end +
-- hours from stop_time
case
when stop_time::date = max(h.time_key) then '00:00:00'::interval
when stop_time::date = start_time::date then greatest(least('19:00:00'::time, stop_time::time) - greatest('10:00:00'::time, start_time::time), '00:00:00'::interval)
else greatest(least(stop_time::time, '19:00:00'::time), '10:00:00'::time) - '10:00:00'::time
end +
--
(
greatest(p.stop_time::date - p.start_time::date - 1, 0)
- sum(case when h.time_key > p.start_time::date and h.time_key < p.stop_time::date then 1 else 0 end)
)*'09:00:00'::interval as answer
from periods p
left outer join
( -- all free days
select time_key::date
from generate_series(
(select min(start_time)::date from periods),
(select max(stop_time)::date from periods),
interval '1 day'
) as time_key
where to_char(time_key, 'D') in ('1', '7')
union
select date_key
from holidays
except
select date_key
from work_holidays
) h
on h.time_key between p.start_time::date and p.stop_time::date
group by p.id, p.start_time, p.stop_time
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
Я этим диалектом sql — не владею, и тем интереснее смотреть варианты. Познавательнее.
Победителей объявят?
Интересно будет глянуть код.
set language russian
;With [days]
as
(
Select Cast('20180101' as date) [day]
Union all
Select DATEADD(day, 1, [days].[day])
From [days]
Where [days].[day] < '20191231'
)
, [periods]
as
(
Select *
from (
VALUES ( 1, Cast('2019-03-29 07:00:00' as datetime2), Cast('2019-04-08 14:00:00'as datetime2) )
, ( 2, '2019-04-10 07:00:00', '2019-04-10 20:00:00' )
, ( 3, '2019-04-11 12:00:00', '2019-04-12 16:07:12' )
, ( 4, '2018-12-28 12:00:00', '2019-01-16 16:00:00' )) t (id, start_time, stop_time)
)
, holidays ( h_date )
as
(
Select *
from (
values ( Cast('2018-01-01' as date) )
, ( '2018-01-02' )
, ( '2018-01-03' )
, ( '2018-01-04' )
, ( '2018-01-05' )
, ( '2018-01-07' )
, ( '2018-01-08' )
, ( '2018-02-23' )
, ( '2018-03-08' )
, ( '2018-05-01' )
, ( '2018-05-09' )
, ( '2018-06-12' )
, ( '2018-11-04' )
, ( '2019-01-01' )
, ( '2019-01-02' )
, ( '2019-01-03' )
, ( '2019-01-04' )
, ( '2019-01-05' )
, ( '2019-01-07' )
, ( '2019-01-08' )
, ( '2019-02-23' )
, ( '2019-03-08' )
, ( '2019-05-01' )
, ( '2019-05-09' )
, ( '2019-06-12' )
, ( '2019-11-04' )
) t(h_date)
)
Select a.*
,t.cnt * 9.0
+ Case when Cast(a.start_time as time) between '10:00:00' and '19:00:00' then Cast(datediff(minute, '19:00:00', Cast(a.start_time as time)) as float) / 60.0
Else 0.0 End
- Case when Cast(a.stop_time as time) between '10:00:00' and '19:00:00' then Cast(datediff(minute, '19:00:00', Cast(a.stop_time as time)) as float) / 60.0
Else 0.0 End
[work_time]
from [periods] a
Outer apply
(Select count(*)
from [days] d
left join holidays h on h.h_date = d.[day]
Where d.[day] between Cast(a.start_time as date) and Cast(a.stop_time as date)
and h.h_date is null
and DATEPART(weekday, d.[day]) not in (6,7)
) t(cnt)
Option (maxrecursion 0)
Скорее всего — не очень верно, т.к. не учтены перерывы на обед (они входят в рабочее время, интересно?)
Написал исходя из того, что рабочий день длится с 10 до 19 — 9 рабочих часов.
Спасибо, что поделились своим вариантом. Действительно интересно сравнивать разные диалекты SQL. Но погонять на предмет ошибок не могу, не на чем.
Победителей объявлю, у меня же приз победителю заявлен.
Получилось postgres-specific, зато компактно. И почти без костылей.
WITH
periods (id, start_time, stop_time) AS (
VALUES
(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
holidays (date) AS (
VALUES
('2018-01-01'::DATE),
('2018-01-02'::DATE),
('2018-01-03'::DATE),
('2018-01-04'::DATE),
('2018-01-05'::DATE),
('2018-01-08'::DATE),
('2018-02-23'::DATE),
('2018-03-08'::DATE),
('2018-03-09'::DATE),
('2018-04-30'::DATE),
('2018-05-01'::DATE),
('2018-05-02'::DATE),
('2018-05-09'::DATE),
('2018-06-11'::DATE),
('2018-06-12'::DATE),
('2018-11-05'::DATE),
('2018-12-31'::DATE),
('2019-01-01'::DATE),
('2019-01-02'::DATE),
('2019-01-03'::DATE),
('2019-01-04'::DATE),
('2019-01-07'::DATE),
('2019-01-08'::DATE),
('2019-03-08'::DATE),
('2019-05-01'::DATE),
('2019-05-02'::DATE),
('2019-05-03'::DATE),
('2019-05-09'::DATE),
('2019-05-10'::DATE),
('2019-06-12'::DATE),
('2019-11-04'::DATE)
),
weekdays (date) AS (
VALUES
('2018-04-28'::DATE),
('2018-06-09'::DATE),
('2018-12-29'::DATE)
),
durations AS (
SELECT
p.*,
tsrange(p.start_time, p.stop_time) * tsrange(g.time + '10 HOURS'::INTERVAL, g.time + '19 HOURS'::INTERVAL) AS range
FROM
periods AS p
LEFT JOIN generate_series(date_trunc('DAY', p.start_time), date_trunc('DAY', p.stop_time), '1 DAY') AS g (time) ON (TRUE)
LEFT JOIN holidays AS h ON (h.date = g.time::DATE)
LEFT JOIN weekdays AS w ON (w.date = g.time::DATE)
WHERE
(extract(DOW FROM g.time)::INT % 6 != 0 OR w.date IS NOT NULL)
AND
h.date IS NULL
)
SELECT
d.id,
d.start_time,
d.stop_time,
coalesce(sum(age(upper(d.range), lower(d.range))), '00:00') AS work_hrs
FROM
durations AS d
GROUP BY
1, 2, 3
ORDER BY
1;
WITH
periods (id, start_time, stop_time) AS (
VALUES
(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
(4, '2018-12-31 12:00:00'::timestamp, '2019-01-01 16:00:00'::timestamp)
),
holidays (date) AS (
VALUES
('2018-01-01'::DATE),
('2018-01-02'::DATE),
('2018-01-03'::DATE),
('2018-01-04'::DATE),
('2018-01-05'::DATE),
('2018-01-08'::DATE),
('2018-02-23'::DATE),
('2018-03-08'::DATE),
('2018-03-09'::DATE),
('2018-04-30'::DATE),
('2018-05-01'::DATE),
('2018-05-02'::DATE),
('2018-05-09'::DATE),
('2018-06-11'::DATE),
('2018-06-12'::DATE),
('2018-11-05'::DATE),
('2018-12-31'::DATE),
('2019-01-01'::DATE),
('2019-01-02'::DATE),
('2019-01-03'::DATE),
('2019-01-04'::DATE),
('2019-01-07'::DATE),
('2019-01-08'::DATE),
('2019-03-08'::DATE),
('2019-05-01'::DATE),
('2019-05-02'::DATE),
('2019-05-03'::DATE),
('2019-05-09'::DATE),
('2019-05-10'::DATE),
('2019-06-12'::DATE),
('2019-11-04'::DATE)
),
weekdays (date) AS (
VALUES
('2018-04-28'::DATE),
('2018-06-09'::DATE),
('2018-12-29'::DATE)
),
durations AS (
SELECT
p.id,
tsrange(p.start_time, p.stop_time) * tsrange(g.time + '10 HOURS'::INTERVAL, g.time + '19 HOURS'::INTERVAL) AS range
FROM
periods AS p
LEFT JOIN generate_series(date_trunc('DAY', p.start_time), date_trunc('DAY', p.stop_time), '1 DAY') AS g (time) ON (TRUE)
LEFT JOIN holidays AS h ON (h.date = g.time::DATE)
LEFT JOIN weekdays AS w ON (w.date = g.time::DATE)
WHERE
(extract(DOW FROM g.time)::INT % 6 != 0 OR w.date IS NOT NULL)
AND
h.date IS NULL
)
SELECT
p.*,
coalesce(sum(age(upper(d.range), lower(d.range))), '00:00') AS work_hrs
FROM
periods AS p
LEFT JOIN durations AS d USING (id)
GROUP BY
1, 2, 3
ORDER BY
1;
with w_periods as
(select 1 as id,
to_date('2019-03-29 07:00:00', 'YYYY-MM-DD HH24:MI:SS') as start_time,
to_date('2019-04-08 14:00:00', 'YYYY-MM-DD HH24:MI:SS') as stop_time
from dual
union all
select 2 as id,
to_date('2019-04-10 07:00:00', 'YYYY-MM-DD HH24:MI:SS') as start_time,
to_date('2019-04-10 20:00:00', 'YYYY-MM-DD HH24:MI:SS') as stop_time
from dual
union all
select 3,
to_date('2019-04-11 12:00:00', 'YYYY-MM-DD HH24:MI:SS') as start_time,
to_date('2019-04-12 16:07:12', 'YYYY-MM-DD HH24:MI:SS') as stop_time
from dual
union all
select 4,
to_date('2018-12-28 12:00:00', 'YYYY-MM-DD HH24:MI:SS') as start_time,
to_date('2019-01-16 16:00:00', 'YYYY-MM-DD HH24:MI:SS') as stop_time
from dual),
w_holidays as
(select to_date('2018-01-01', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-01-02', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-01-03', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-01-04', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-01-05', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-01-07', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-01-08', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-02-23', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-03-08', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-04-30', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-05-01', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-05-02', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-05-09', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-06-12', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-11-04', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2019-01-01', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2019-01-02', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2019-01-03', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2019-01-04', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2019-01-05', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2019-01-07', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2019-01-08', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2019-02-23', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2019-03-08', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2019-05-01', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2019-05-09', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2019-06-12', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2019-11-04', 'YYYY-MM-DD') as dt
from dual),
w_dates1 as
(select v2.id, (count(v2.id) * 9) as work_hrs
from (select v1.*
from (select distinct (trunc(p.start_time) + level) as dt, p.id
from w_periods p
where trunc(p.stop_time - p.start_time) > 1
connect by level < trunc(p.stop_time - p.start_time)) v1
where v1.dt not in (select dt from w_holidays)
and trim(to_char(v1.dt, 'DAY')) not in ('SATURDAY', 'SUNDAY')) v2
group by v2.id),
w_dates2 as
(select v1.id,
(case
when (v1.start_time1 = v1.stop_time1) and (v1.start_time2 = v1.stop_time2) then
(v1.start_time2 - v1.start_time1)
else
(v1.start_time2 - v1.start_time1) + (v1.stop_time2 - v1.stop_time1)
end) * 24 as work_hrs
from (select t.*,
(case
when (t.start_time < trunc_start_time + interval '10' hour) then
trunc_start_time + interval '10' hour
else
t.start_time
end) as start_time1,
(case
when (t.start_time < trunc_start_time + interval '19' hour) then
trunc_start_time + interval '19' hour
else
t.start_time
end) as start_time2,
(case
when (t.stop_time > trunc_stop_time + interval '10' hour) then
trunc_stop_time + interval '10' hour
else
t.stop_time
end) as stop_time1,
(case
when (t.stop_time > trunc_stop_time + interval '19' hour) then
trunc_stop_time + interval '19' hour
else
t.stop_time
end) as stop_time2
from (select x.*, trunc(x.start_time) as trunc_start_time, trunc(x.stop_time) as trunc_stop_time from w_periods x) t) v1)
select p.*, v2.work_hrs
from (select v1.id, sum(work_hrs) as work_hrs from (select * from w_dates1 union all select * from w_dates2) v1 group by v1.id) v2,
w_periods p
where v2.id = p.id
with w_periods(id, start_time, stop_time) as (
values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
w_holidays as
(select to_date('2018-01-01', 'YYYY-MM-DD') as dt
union all
select to_date('2018-01-02', 'YYYY-MM-DD')
union all
select to_date('2018-01-03', 'YYYY-MM-DD')
union all
select to_date('2018-01-04', 'YYYY-MM-DD')
union all
select to_date('2018-01-05', 'YYYY-MM-DD')
union all
select to_date('2018-01-07', 'YYYY-MM-DD')
union all
select to_date('2018-01-08', 'YYYY-MM-DD')
union all
select to_date('2018-02-23', 'YYYY-MM-DD')
union all
select to_date('2018-03-08', 'YYYY-MM-DD')
union all
select to_date('2018-04-30', 'YYYY-MM-DD')
union all
select to_date('2018-05-01', 'YYYY-MM-DD')
union all
select to_date('2018-05-02', 'YYYY-MM-DD')
union all
select to_date('2018-05-09', 'YYYY-MM-DD')
union all
select to_date('2018-06-12', 'YYYY-MM-DD')
union all
select to_date('2018-11-04', 'YYYY-MM-DD')
union all
select to_date('2019-01-01', 'YYYY-MM-DD')
union all
select to_date('2019-01-02', 'YYYY-MM-DD')
union all
select to_date('2019-01-03', 'YYYY-MM-DD')
union all
select to_date('2019-01-04', 'YYYY-MM-DD')
union all
select to_date('2019-01-05', 'YYYY-MM-DD')
union all
select to_date('2019-01-07', 'YYYY-MM-DD')
union all
select to_date('2019-01-08', 'YYYY-MM-DD')
union all
select to_date('2019-02-23', 'YYYY-MM-DD')
union all
select to_date('2019-03-08', 'YYYY-MM-DD')
union all
select to_date('2019-05-01', 'YYYY-MM-DD')
union all
select to_date('2019-05-09', 'YYYY-MM-DD')
union all
select to_date('2019-06-12', 'YYYY-MM-DD')
union all
select to_date('2019-11-04', 'YYYY-MM-DD')),
w_dates1 as
(select v2.id, (count(v2.id) * 9) * '1 hours' ::interval as work_hrs
from (select v1.*
from (select id, dt from w_periods, generate_series(start_time ::date, stop_time ::date, '1 day') as dt) v1
where v1.dt not in (select dt from w_holidays)
and trim(to_char(v1.dt, 'DAY')) not in ('SATURDAY', 'SUNDAY')
and v1.dt not in (select date_trunc('day', start_time) from w_periods)
and v1.dt not in (select date_trunc('day', stop_time) from w_periods)) v2
group by v2.id),
w_dates2 as
(select v1.id,
(case
when (v1.start_time1 = v1.stop_time1) and (v1.start_time2 = v1.stop_time2) then
(v1.start_time2 - v1.start_time1)
else
(v1.start_time2 - v1.start_time1) + (v1.stop_time2 - v1.stop_time1)
end) as work_hrs
from (select t.*,
(case
when (t.start_time < trunc_start_time + interval '10' hour) then
trunc_start_time + interval '10' hour
else
t.start_time
end) as start_time1,
(case
when (t.start_time < trunc_start_time + interval '19' hour) then
trunc_start_time + interval '19' hour
else
t.start_time
end) as start_time2,
(case
when (t.stop_time > trunc_stop_time + interval '10' hour) then
trunc_stop_time + interval '10' hour
else
t.stop_time
end) as stop_time1,
(case
when (t.stop_time > trunc_stop_time + interval '19' hour) then
trunc_stop_time + interval '19' hour
else
t.stop_time
end) as stop_time2
from (select x.*, date_trunc('day', x.start_time) as trunc_start_time, date_trunc('day', x.stop_time) as trunc_stop_time
from w_periods x) t) v1)
select p.*, v2.work_hrs
from (select v1.id, sum(work_hrs) as work_hrs from (select * from w_dates1 union all select * from w_dates2) v1 group by v1.id) v2,
w_periods p
where v2.id = p.id
Оракловый запрос нормально погонять не на чем, а постгресовый на многих периодах сильно занижает. Например, на ('2018-05-24 17:31:12', '2018-06-01 17:59:39') показывает рабочее время всего 09:28:27, а там целая неделя.
А вот на ('2018-06-10 18:46:10', '2018-06-11 10:18:18') наоборот насчитал лишнего.
ЗЫ Даты праздников у Вас неверные. А дополнительные рабочие дни похоже совсем не учитываются, и это тоже даёт неверные результаты.
with w_periods as
(select 1 as id,
to_date('2019-03-29 07:00:00', 'YYYY-MM-DD HH24:MI:SS') as start_time,
to_date('2019-04-08 14:00:00', 'YYYY-MM-DD HH24:MI:SS') as stop_time
from dual
union all
select 2 as id,
to_date('2019-04-10 07:00:00', 'YYYY-MM-DD HH24:MI:SS') as start_time,
to_date('2019-04-10 20:00:00', 'YYYY-MM-DD HH24:MI:SS') as stop_time
from dual
union all
select 3,
to_date('2019-04-11 12:00:00', 'YYYY-MM-DD HH24:MI:SS') as start_time,
to_date('2019-04-12 16:07:12', 'YYYY-MM-DD HH24:MI:SS') as stop_time
from dual
union all
select 4,
to_date('2018-12-28 12:00:00', 'YYYY-MM-DD HH24:MI:SS') as start_time,
to_date('2019-01-16 16:00:00', 'YYYY-MM-DD HH24:MI:SS') as stop_time
from dual),
w_holidays as
(select to_date('2018-01-01', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-01-02', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-01-03', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-01-04', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-01-05', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-01-07', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-01-08', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-02-23', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-03-08', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-03-09', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-04-30', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-05-01', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-05-02', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-05-09', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-06-11', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-06-12', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-11-05', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-12-31', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2019-01-01', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2019-01-02', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2019-01-03', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2019-01-04', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2019-01-07', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2019-01-08', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2019-02-23', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2019-03-08', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2019-05-01', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2019-05-02', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2019-05-03', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2019-05-09', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2019-05-10', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2019-06-12', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2019-11-04', 'YYYY-MM-DD') as dt
from dual),
w_work_days as
(select to_date('2018-04-28', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-06-09', 'YYYY-MM-DD') as dt
from dual
union all
select to_date('2018-12-29', 'YYYY-MM-DD') as dt
from dual),
w_dates1 as
(select v2.id, (count(v2.id) * 9) as work_hrs
from (select v1.*
from (select distinct (trunc(p.start_time) + level) as dt, p.id
from w_periods p
where trunc(p.stop_time - p.start_time) > 1
connect by level < trunc(p.stop_time - p.start_time)) v1
where v1.dt not in (select dt from w_holidays)
and (trim(to_char(v1.dt, 'DAY')) not in ('SATURDAY', 'SUNDAY') or v1.dt in (select dt from w_work_days))) v2
group by v2.id),
w_dates2 as
(select v1.id,
(case
when (v1.start_time1 = v1.stop_time1) and (v1.start_time2 = v1.stop_time2) then
(v1.start_time2 - v1.start_time1)
else
(v1.start_time2 - v1.start_time1) + (v1.stop_time2 - v1.stop_time1)
end) * 24 as work_hrs
from (select t.*,
(case
when (t.start_time < trunc_start_time + interval '10' hour) then
trunc_start_time + interval '10' hour
else
t.start_time
end) as start_time1,
(case
when (t.start_time < trunc_start_time + interval '19' hour) then
trunc_start_time + interval '19' hour
else
t.start_time
end) as start_time2,
(case
when (t.stop_time > trunc_stop_time + interval '10' hour) then
trunc_stop_time + interval '10' hour
else
t.stop_time
end) as stop_time1,
(case
when (t.stop_time > trunc_stop_time + interval '19' hour) then
trunc_stop_time + interval '19' hour
else
t.stop_time
end) as stop_time2
from (select x.*, trunc(x.start_time) as trunc_start_time, trunc(x.stop_time) as trunc_stop_time from w_periods x) t) v1)
select p.*, v2.work_hrs
from (select v1.id, sum(work_hrs) as work_hrs from (select * from w_dates1 union all select * from w_dates2) v1 group by v1.id) v2,
w_periods p
where v2.id = p.id
order by p.id
with w_periods(id, start_time, stop_time) as (
values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
w_holidays as
(select to_date('2018-01-01', 'YYYY-MM-DD') as dt
union all
select to_date('2018-01-02', 'YYYY-MM-DD')
union all
select to_date('2018-01-03', 'YYYY-MM-DD')
union all
select to_date('2018-01-04', 'YYYY-MM-DD')
union all
select to_date('2018-01-05', 'YYYY-MM-DD')
union all
select to_date('2018-01-07', 'YYYY-MM-DD')
union all
select to_date('2018-01-08', 'YYYY-MM-DD')
union all
select to_date('2018-02-23', 'YYYY-MM-DD')
union all
select to_date('2018-03-08', 'YYYY-MM-DD')
union all
select to_date('2018-03-09', 'YYYY-MM-DD')
union all
select to_date('2018-04-30', 'YYYY-MM-DD')
union all
select to_date('2018-05-01', 'YYYY-MM-DD')
union all
select to_date('2018-05-02', 'YYYY-MM-DD')
union all
select to_date('2018-05-09', 'YYYY-MM-DD')
union all
select to_date('2018-06-11', 'YYYY-MM-DD')
union all
select to_date('2018-06-12', 'YYYY-MM-DD')
union all
select to_date('2018-11-05', 'YYYY-MM-DD')
union all
select to_date('2018-12-31', 'YYYY-MM-DD')
union all
select to_date('2019-01-01', 'YYYY-MM-DD')
union all
select to_date('2019-01-02', 'YYYY-MM-DD')
union all
select to_date('2019-01-03', 'YYYY-MM-DD')
union all
select to_date('2019-01-04', 'YYYY-MM-DD')
union all
select to_date('2019-01-07', 'YYYY-MM-DD')
union all
select to_date('2019-01-08', 'YYYY-MM-DD')
union all
select to_date('2019-02-23', 'YYYY-MM-DD')
union all
select to_date('2019-03-08', 'YYYY-MM-DD')
union all
select to_date('2019-05-01', 'YYYY-MM-DD')
union all
select to_date('2019-05-02', 'YYYY-MM-DD')
union all
select to_date('2019-05-03', 'YYYY-MM-DD')
union all
select to_date('2019-05-09', 'YYYY-MM-DD')
union all
select to_date('2019-05-10', 'YYYY-MM-DD')
union all
select to_date('2019-06-12', 'YYYY-MM-DD')
union all
select to_date('2019-11-04', 'YYYY-MM-DD')),
w_work_days as
(select to_date('2018-04-28', 'YYYY-MM-DD') as dt
union all
select to_date('2018-06-09', 'YYYY-MM-DD')
union all
select to_date('2018-12-29', 'YYYY-MM-DD')),
w_dates1 as
(select v2.id, (count(v2.id) * 9) * '1 hours' ::interval as work_hrs
from (select v1.*
from (select id, dt from w_periods, generate_series(start_time ::date, stop_time ::date, '1 day') as dt) v1
where v1.dt not in (select dt from w_holidays)
and (trim(to_char(v1.dt, 'DAY')) not in ('SATURDAY', 'SUNDAY') or v1.dt in (select dt from w_work_days))
and v1.dt not in (select date_trunc('day', start_time) from w_periods)
and v1.dt not in (select date_trunc('day', stop_time) from w_periods)) v2
group by v2.id),
w_dates2 as
(select v1.id,
(case
when (v1.start_time1 = v1.stop_time1) and (v1.start_time2 = v1.stop_time2) then
(v1.start_time2 - v1.start_time1)
else
(v1.start_time2 - v1.start_time1) + (v1.stop_time2 - v1.stop_time1)
end) as work_hrs
from (select t.*,
(case
when (t.start_time < trunc_start_time + interval '10' hour) then
trunc_start_time + interval '10' hour
else
t.start_time
end) as start_time1,
(case
when (t.start_time < trunc_start_time + interval '19' hour) then
trunc_start_time + interval '19' hour
else
t.start_time
end) as start_time2,
(case
when (t.stop_time > trunc_stop_time + interval '10' hour) then
trunc_stop_time + interval '10' hour
else
t.stop_time
end) as stop_time1,
(case
when (t.stop_time > trunc_stop_time + interval '19' hour) then
trunc_stop_time + interval '19' hour
else
t.stop_time
end) as stop_time2
from (select x.*, date_trunc('day', x.start_time) as trunc_start_time, date_trunc('day', x.stop_time) as trunc_stop_time
from w_periods x) t) v1)
select p.*, v2.work_hrs
from (select v1.id, sum(work_hrs) as work_hrs from (select * from w_dates1 union all select * from w_dates2) v1 group by v1.id) v2,
w_periods p
where v2.id = p.id
order by p.id
Под postgresql в Navicat Premium у меня выводит так:
2018-05-24 17:31:12 | 2018-06-01 17:59:39 | 54:28:27
2018-06-10 18:46:10 | 2018-06-11 10:18:18 | 00:32:08
Может под разные клиенты нужно задавать форматирование? В oracle результат в долях (цифры)
SQL: задача о рабочем времени