Расчет рабочих дней между датами в MySQL: функция

Быстрый ответ

Если вам необходимо определить, как вычислить рабочие дни между датами в MySQL, решение близко. Используйте рекурсивные общие табличные выражения (CTE) для создания диапазона дат, отсечьте выходные и подсчитайте оставшиеся дни.

Вот пример кода:

WITH RECURSIVE DateSeq AS (
  SELECT '2023-01-01' as date
  UNION ALL
  SELECT DATE_ADD(date, INTERVAL 1 DAY)
  FROM DateSeq
  WHERE date < '2023-01-10'
)
SELECT COUNT(*) as working_days
FROM DateSeq
WHERE DAYOFWEEK(date) BETWEEN 2 AND 6;

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

Создание функции MySQL

Хотите спрячься от простоты? Отложите простые методы и создайте свою функцию. Реализуйте BusinessDaysBetweenDates, аналогичную функции NETWORKDAYS() в Excel, которая будет учитывать выходные и праздники.

Улучшение вашей функции

Доверьтесь возможностям MySQL и создайте функцию BusinessDaysBetweenDates:

DELIMITER //
CREATE FUNCTION BusinessDaysBetweenDates(start_date DATE, end_date DATE)
RETURNS INT
BEGIN
  DECLARE total_days INT DEFAULT 0;
  DECLARE iter_date DATE;

  IF(start_date > end_date) THEN
    RETURN total_days;
  END IF;

  SET iter_date = start_date;
  WHILE iter_date <= end_date DO
    IF DAYOFWEEK(iter_date) BETWEEN 2 AND 6 THEN
      SET total_days = total_days + 1;
    END IF;
    SET iter_date = iter_date + INTERVAL 1 DAY;
  END WHILE;

  RETURN total_days;
END //
DELIMITER ;

Вызовите функцию следующим образом:

SELECT BusinessDaysBetweenDates('2023-01-01', '2023-01-10') as working_days;

Подробнее об этом расскажет наш спикер на видео

Создание календарной таблицы

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

CREATE TABLE Calendar (
  date DATE PRIMARY KEY,
  is_weekend BIT,
  is_holiday BIT
);

-- Затем заполните таблицу данными

Дополните функцию проверкой наличия даты в таблице Calendar.

Учет праздников

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

WHILE iter_date <= end_date DO
  IF (SELECT is_weekend FROM Calendar WHERE date = iter_date) = 0 
    AND (SELECT is_holiday FROM Calendar WHERE date = iter_date) = 0 THEN
      SET total_days = total_days + 1;
  END IF;
  SET iter_date = iter_date + INTERVAL 1 DAY;
END WHILE;

Оптимизация надежности и точности

Желаете стать MVP в мире SQL-скриптов? Убедитесь, что ваша функция корректно обрабатывает крайние случаи, когда начальная или конечная дата выпадает на выходной или праздник.

Соответствие границам

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

IF DAYOFWEEK(start_date) == 7 THEN 
  SET start_date = start_date + INTERVAL 2 DAY;
ELSEIF DAYOFWEEK(start_date) == 1 THEN 
  SET start_date = start_date + INTERVAL 1 DAY;
END IF;

Действуйте таким образом, как если бы последний день недели был всегда пятницей.

Повышение производительности

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

Тестирование скрипта

Подтвердите свой профессионализм, тщательно протестируйте свои функции. Хорошей практикой будет проведение тестирования после работы.

SELECT BusinessDaysBetweenDates('2023-01-01', '2023-01-10') as working_days;

Убедитесь, что функция работает правильно на реальных временных интервалах. В конце концов, вы создаете не простые функции, а настоящих героев!

Визуализация

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

     [Начальная Дата] 📅
         |
      (🗓️ Подсчет)
         |
     🚧 (Выходные)
         |
     ⛔️ (Праздники)
         |
     [Конечная Дата] 📅

Думайте о функции SQL как о вашем проводнике, который уверенно обходит стены выходных и преодолевает преграды праздников.

Полезные материалы

1. Руководство по MySQL 8.0: функции даты и времени — благодатный источник информации о функциях даты и времени в MySQL.
2. Недавние обсуждения на тему 'workday-api mysql' на Stack Overflow — обмен опытом и решениями по подсчету рабочих дней в MySQL.
3. Советы по оптимизации производительности SQL: производные таблицы против временных таблиц — разбираемся в тонкостях и выбираем наиболее эффективный способ оптимизации для работы с датами.