В реальной жизни может быть и так, и эдак. В зависимости от того, разовый ли это запрос или регулярный. Я специально не стал конкретизировать представление календаря, чтобы посмотреть на полёт мысли. Вот, например, Ваше представление мне очень понравилось. Компактнее ни у кого не получилось.
Во-первых, в SLA указывают время, когда предоставляется сервис, а не рабочее время исполнителя. Это может совсем не совпадать, хотя удобнее конечно, чтобы графики рабочего времени если не совпадали, то по крайней мере покрывали время в SLA, а то работать будет некому. Но никто не мешает нам оказывать какие-то услуги, например, только в первой половине дня.
Во-вторых, у нас может быть гибкий график. Или в рабочее время человек убежал по своим делам, а вечером задержался (или утром пораньше начал), чтобы успеть доделать всё вовремя.
В-третьих, у нас могут быть офисы в разных часовых поясах. Или в разных городах. В городах поменьше любят график работы с 09 до 18, а в Москве-Питере с 10 до 19.
В-четвёртых, ночной дежурный скучал, от нечего делать подчистил всякие недозакрытые задачки. А может и какую-то свою работу доделал и закрыл.
В-пятых, клиент обратился в нерабочее время, а потом понял, что ложная тревога, или сам справился и закрыл.
В-шестых, бывают запланированные работы в нерабочее время. Каждый dba знает, что кое-что надо делать в отсутствие пользователей.
В-седьмых, иногда в IT бывают авралы, когда просто нужно сделать. Лучше, когда это редко (а ещё лучше если никогда), но иногда бывает.
В-восьмых, пришёл робот и автоматически поменял статусы, просроченное по таймаутам закрыл.
Ну короче не только по пьяни в нерабочее время что-то происходит на работе. Надеюсь, достаточно привёл жизненных примеров. (:
У Вас 2018-06-10 подхватывается рабочим днём. Вот пример данных, где неверно считается:
('2018-06-09 16:51:40', '2018-06-10 10:55:50')
Предварительно я Ваш запрос скорректировал правильными праздниками и доп. рабочими днями:
Заголовок спойлера
with ...
cte_holidays as (
select h.date::date
from (values('2018-01-01'), -- 2018
('2018-01-02'),
('2018-01-03'),
('2018-01-04'),
('2018-01-05'),
('2018-01-08'),
('2018-02-23'),
('2018-03-08'),
('2018-03-09'),
('2018-05-01'),
('2018-05-02'),
('2018-05-09'),
('2018-06-11'),
('2018-06-12'),
('2018-11-05'),
('2018-12-31'),
('2019-01-01'), -- 2019
('2019-01-02'),
('2019-01-03'),
('2019-01-04'),
('2019-01-07'),
('2019-01-08'),
('2019-03-08'),
('2019-05-01'),
('2019-05-02'),
('2019-05-03'),
('2019-05-09'),
('2019-05-10'),
('2019-06-12'),
('2019-11-04') ) as h(date)
), cte_working_weekend as (
select h.date::date
from (values ('2018-04-28'),
('2018-06-09'),
('2018-12-29') ) as h(date)
), ...
Не, не выходит каменный цветок. На вот таких периодах некорректно считает:
('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 — неправильные данные, я у себя для тестов исправлял.
Бинго! Логика верная, но из чьего-то запроса выше Вы скопировали неправильные данные для праздников и не указали все дополнительные рабочие дни. После того, как я поправил, результаты на моих тестовых выборках стали сходиться с моими результатами.
Что ж не добавили в рабочие дни ещё 2018-06-09 и 2018-12-29? Не так уж это и утомительно.
У меня сошлись результаты запроса на небольшой тестовой выборке, но не сошлись на большой по реальным данным. Сходу ошибку не вижу, посмотрю ещё попозже.
Почему-то ни разу не видел, чтобы в SLA указывали, что предпраздничные рабочие дни на час короче. В SLA обычно пишут «бла-бла-бла за N часов в рабочее время» и рабочее время определено «с/по в рабочие дни кроме выходных и официальных праздников», всё.
Буду рад, если хоть одно из решений позволит учесть укороченные предпраздничные дни. Когда я эту задачу решал в реальной жизни, я на это заложился, но не пригодилось.
Я отчасти с Вами согласен, но тем не менее в условии я просил учесть праздники за два года и мне сперва пришлось «при необходимости решение можно будет легко дополнить», чтобы погонять на тестовых данных. Я бы не стал проявлять излишний формализм, если бы это был единственный нюанс Вашего решения. Но у Вас не учитываются дополнительные рабочие дни, например, 2018-04-28 был рабочим днём. Также потерялись в выводе периоды, которые совсем не попали на рабочее время.
А вот компактность Вашего решения мне очень нравится.
Интересный подход, пока из опубликованных решений никто не пробовал так считать, но есть ошибка в реализации. Запустите на интервале ('2019-01-01 21:00:00', '2019-01-01 21:00:00'), эффект неожиданный.
Про задачку с интервалами сформулируйте более формально, чтобы было понятно какими входными данными оперировать и в какой форме результат получить. С удовольствием порешаю на досуге.
Да, алгоритм-то несложен — посчитать количество рабочих дней, правильно учесть первый и последний дни, всё сложить. Осталось это выразить на SQL.
PS умножить надо на 9
А обедов в SLA не было. (:
Подход может и не оригинален, но всё равно спасибо за участие. Не так много народу вообще показало работающие запросы.
Во-вторых, у нас может быть гибкий график. Или в рабочее время человек убежал по своим делам, а вечером задержался (или утром пораньше начал), чтобы успеть доделать всё вовремя.
В-третьих, у нас могут быть офисы в разных часовых поясах. Или в разных городах. В городах поменьше любят график работы с 09 до 18, а в Москве-Питере с 10 до 19.
В-четвёртых, ночной дежурный скучал, от нечего делать подчистил всякие недозакрытые задачки. А может и какую-то свою работу доделал и закрыл.
В-пятых, клиент обратился в нерабочее время, а потом понял, что ложная тревога, или сам справился и закрыл.
В-шестых, бывают запланированные работы в нерабочее время. Каждый dba знает, что кое-что надо делать в отсутствие пользователей.
В-седьмых, иногда в IT бывают авралы, когда просто нужно сделать. Лучше, когда это редко (а ещё лучше если никогда), но иногда бывает.
В-восьмых, пришёл робот и автоматически поменял статусы, просроченное по таймаутам закрыл.
Ну короче не только по пьяни в нерабочее время что-то происходит на работе. Надеюсь, достаточно привёл жизненных примеров. (:
('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)
), ...
('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 — неправильные данные, я у себя для тестов исправлял.
На каких данных некорректно работает пока не понял. Чуть позже отвечу, где ошибка.
У меня сошлись результаты запроса на небольшой тестовой выборке, но не сошлись на большой по реальным данным. Сходу ошибку не вижу, посмотрю ещё попозже.
Буду рад, если хоть одно из решений позволит учесть укороченные предпраздничные дни. Когда я эту задачу решал в реальной жизни, я на это заложился, но не пригодилось.
А вот компактность Вашего решения мне очень нравится.
Про задачку с интервалами сформулируйте более формально, чтобы было понятно какими входными данными оперировать и в какой форме результат получить. С удовольствием порешаю на досуге.
PS умножить надо на 9