Mysql: как рассчитать рабочее время между двумя временными отметками и пренебречь выходными днями

Ответ 1

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

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

Позвольте создать таблицу с некоторыми примерами данных, которые охватывают различные случаи:

CREATE TABLE T (CreatedDate datetime, UpdatedDate datetime);

INSERT INTO T VALUES
('2012-03-05 09:00:00', '2012-03-05 15:00:00'), -- simple part of the same day
('2012-03-05 10:00:00', '2012-03-06 10:00:00'), -- full day across the midnight
('2012-03-05 11:00:00', '2012-03-06 10:00:00'), -- less than a day across the midnight
('2012-03-05 10:00:00', '2012-03-06 15:00:00'), -- more than a day across the midnight
('2012-03-09 16:00:00', '2012-03-12 10:00:00'), -- over the weekend, less than 7 days
('2012-03-06 16:00:00', '2012-03-15 10:00:00'), -- over the weekend, more than 7 days
('2012-03-09 16:00:00', '2012-03-19 10:00:00'); -- over two weekends

В MS SQL Server я использую следующую формулу:

SELECT
    CreatedDate,
    UpdatedDate,
    DATEDIFF(minute, CreatedDate, UpdatedDate)/60.0 -
    DATEDIFF(day,    CreatedDate, UpdatedDate)*16 -
    DATEDIFF(week,   CreatedDate, UpdatedDate)*16 AS BusinessHours
FROM T

Что дает следующий результат:

+-------------------------+-------------------------+---------------+
|       CreatedDate       |       UpdatedDate       | BusinessHours |
+-------------------------+-------------------------+---------------+
| 2012-03-05 09:00:00     | 2012-03-05 15:00:00     | 6             |
| 2012-03-05 10:00:00     | 2012-03-06 10:00:00     | 8             |
| 2012-03-05 11:00:00     | 2012-03-06 10:00:00     | 7             |
| 2012-03-05 10:00:00     | 2012-03-06 15:00:00     | 13            |
| 2012-03-09 16:00:00     | 2012-03-12 10:00:00     | 2             |
| 2012-03-06 16:00:00     | 2012-03-15 10:00:00     | 50            |
| 2012-03-09 16:00:00     | 2012-03-19 10:00:00     | 42            |
+-------------------------+-------------------------+---------------+

Это работает, потому что в SQL Server DATEDIFF возвращается счет указанных границ даты, пересеченных между указанными начальными и конечными датами.

Каждый день имеет 8 рабочих часов. Я вычисляю общее количество часов между двумя датами, а затем вычитаю количество полуночи, умноженное на 16 нерабочих часов в день, затем вычитаем количество выходных дней, умноженное на 16 (8 + 8 рабочих часов для Sat + Sun).

Он также предполагает, что данные даты и времени начала и окончания указаны в рабочее время.

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

Итак, чтобы получить нужные нам результаты, мы можем вычислить TIMESTAMPDIFF между некоторым якорем datetime и CreatedDate и UpdatedDate, а не напрямую вычислять разницу между CreatedDate и UpdatedDate.

Я выбрал 2000-01-03 00:00:00, который является понедельником. Вы можете выбрать любой другой понедельник (или воскресенье, если ваша неделя начнется в воскресенье) в полночь для даты привязки.

Запрос MySQL становится (см. SQL Fiddle):

SELECT
    CreatedDate,
    UpdatedDate,

    TIMESTAMPDIFF(MINUTE, CreatedDate, UpdatedDate)/60.0 -

    16*(
        TIMESTAMPDIFF(DAY,    '2000-01-03',UpdatedDate)-
        TIMESTAMPDIFF(DAY,    '2000-01-03',CreatedDate)
    ) -

    16*(
        TIMESTAMPDIFF(WEEK,   '2000-01-03',UpdatedDate)-
        TIMESTAMPDIFF(WEEK,   '2000-01-03',CreatedDate)
    ) AS BusinessHours

FROM T

Ответ 2

Создайте таблицу под названием BusinessHours с одним столбцом, называемым часом, содержащим дату. Сделайте Час основным ключом. Напишите процесс, чтобы заполнить его с каждым рабочим часом года (2012-01-02 00:90:00, 2012-01-02 00:10:00 и т.д.). Это не должно быть сложно, поскольку правила просты. Это звучит как много данных, но в великой схеме вещей это не так (есть только 8760 часов в году - даже если ваше время на дату занимает 8 байтов каждый, это колоссальный 60 КБ). Обязательно назначьте задание, чтобы оно не заполнилось.

Тогда:

Select
  y.CreatedDate,
  y.UpdatedDate,
  Count(*) as BusinessHours
From
  YourTable y
    Inner Join
  BusinessHours h
    On h.Hour >= y.CreatedDate And h.Hour < y.UpdatedDate
Group By
  y.CreatedDate,
  y.UpdatedDate

Это также дает вам довольно простой подход, если вы когда-либо рассматриваете праздничные дни - просто выведите строки из таблицы BusinessHours.

Ответ 3

попробовать

SELECT (FLOOR(DATEDIFF (UpdatedDate, CreatedDate) / 7) * 5 + MOD (DATEDIFF (UpdatedDate, CreatedDate), 7)) * 8 +
       TIMEDIFF (TIME (UpdatedDate), TIME(CreatedDate))
FROM YourTable

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

Для справки см. Документация по MySQL.

Ответ 4

Используйте датифик, чтобы рассчитать разницу между начальным и конечным временем, а затем вычесть (date_add с отрицательными часами) нерабочее время: 24 в выходные дни, 16 для рабочих дней ожидают окончания и начальный день, который требует дополнительного расчета, Независимо от того, является ли день выходным днем ​​или нет, его можно указать с помощью метода dayofweek.