Как показывает практика, множество систем с использованием календарей обычно строится в виде статических таблиц, в которых перечислены даты и их соответствие рабочим, выходным, праздничным дням. Обычно проблемы начинаются когда система работает без вмешательств программистов достаточно долго и заполненный календарь просто кончается. Для очередного проекта я решил немного оптимизировать данную ситуацию и написал календарь, который создаётся или пересчитывается автоматически, например по встроенному таймеру.
Не знаю, многие ли владеют информацией о наличии в MariaDB(MySQL) встроенного итератора возвращающего значения по заданному диапазону. Если кто не знает, то можно прочитать по ссылке https://mariadb.com/kb/en/mariadb/sequence/
Принцип простой. Такой же как у обычного цикла For, которому передаются начало, конец и шаг. Данный итератор имеет фантастическое быстродействие и позволяет производить вычисления последовательностей.
Начнём с простого.
Как все мы знаем, обычный календарь состоит из рабочих и выходных дней, а также из официальных государственных праздничных дней и дней на которые данные праздники будут перенесены, если они выпали на выходные. Перенес праздничных дней обычно происходит по постановлению правительства РФ в середине текущего года.
Создадим 2 таблицы. Периодические ежегодные праздники и перенос выходных дней.
CREATE TABLE `holidays_periodic` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `hdate` DATE NOT NULL COMMENT 'Праздничный или переносимый выходной день', `hdate_remap` DATE NOT NULL COMMENT 'День на который переносится Праздничный или переносимый выходной день', `hcomment` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci', PRIMARY KEY (`id`) ) COMMENT='Ежегодный неизменяемый календарь праздничных дней' COLLATE='utf8_unicode_ci' ENGINE=InnoDB ROW_FORMAT=COMPACT AUTO_INCREMENT=1 ;
CREATE TABLE `holidays` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `hdate` DATE NOT NULL COMMENT 'Праздничный или переносимый выходной день', `hdate_remap` DATE NOT NULL COMMENT 'День на который переносится Праздничный или переносимый выходной день', `hcomment` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci', `hdate_workday` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Переносимый день является рабочим (0/1)', PRIMARY KEY (`id`), INDEX `IDX_hdate` (`hdate`), INDEX `IDX_hdate_remap` (`hdate_remap`) ) COMMENT='Календарь праздничных и переносимых дней' COLLATE='utf8_unicode_ci' ENGINE=InnoDB AUTO_INCREMENT=1 ;
Зальём в таблицы периодические праздники
INSERT INTO `holidays_periodic` (`id`, `hdate`, `hdate_remap`, `hcomment`) VALUES (1, '1970-01-01', '0000-00-00', 'Новый год'); INSERT INTO `holidays_periodic` (`id`, `hdate`, `hdate_remap`, `hcomment`) VALUES (2, '1970-01-07', '0000-00-00', 'Рождество Христово'); INSERT INTO `holidays_periodic` (`id`, `hdate`, `hdate_remap`, `hcomment`) VALUES (3, '1970-02-23', '0000-00-00', 'День защитника отечества'); INSERT INTO `holidays_periodic` (`id`, `hdate`, `hdate_remap`, `hcomment`) VALUES (4, '1970-03-08', '0000-00-00', 'Международный женский день'); INSERT INTO `holidays_periodic` (`id`, `hdate`, `hdate_remap`, `hcomment`) VALUES (5, '1970-05-01', '0000-00-00', 'Праздник весны и труда'); INSERT INTO `holidays_periodic` (`id`, `hdate`, `hdate_remap`, `hcomment`) VALUES (6, '1970-05-09', '0000-00-00', 'День победы'); INSERT INTO `holidays_periodic` (`id`, `hdate`, `hdate_remap`, `hcomment`) VALUES (7, '1970-06-12', '0000-00-00', 'День России'); INSERT INTO `holidays_periodic` (`id`, `hdate`, `hdate_remap`, `hcomment`) VALUES (8, '1970-11-04', '0000-00-00', 'День народного единства'); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (1, '2017-01-01', '2017-02-24', 'Новый год', 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (2, '2017-01-07', '2017-05-08', 'Рождество Христово', 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (3, '2017-02-23', '0000-00-00', 'День защитника отечества', 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (4, '2017-03-08', '0000-00-00', 'Международный женский день', 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (5, '2017-05-01', '0000-00-00', 'Праздник весны и труда', 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (6, '2017-05-09', '0000-00-00', 'День победы', 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (7, '2017-06-12', '0000-00-00', 'День России', 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (8, '2017-11-04', '2017-11-06', 'День народного единства', 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (9, '2016-01-02', '2016-05-03', NULL, 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (10, '2016-01-03', '2016-03-07', NULL, 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (11, '2016-02-20', '2016-02-22', NULL, 1); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (12, '2016-05-01', '2016-05-02', NULL, 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (13, '2016-06-12', '2016-06-13', NULL, 0);
Обратите внимание, что в таблице holidays присутствует поле hdate_workday, используемое если перенос осуществлён с выходного дня, который переназначен рабочим. Например 20 февраля 2016 года приходится на субботу, но согласно положению правительства, данный день переносится на 22 февраля и обозначен как рабочий. Т.е. появляется рабочая суббота, после которой идёт 3 дня отдыха.
Начальные данные у нас есть, теперь магия SEQUENCE.
Создадим таблицу календаря
CREATE TABLE `calendar_byholiday` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `cdate` DATE NULL DEFAULT NULL, `holiday` TINYINT(4) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), INDEX `IDX_cdate` (`cdate`) ) COLLATE='utf8_unicode_ci' ENGINE=InnoDB ROW_FORMAT=COMPACT AUTO_INCREMENT=1 ;
Для заполнения таблицы создадим хранимую процедуру
CREATE DEFINER=`root`@`%` PROCEDURE `rebuild_calendar_byholiday`() LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT 'Обновление календаря с выходными днями' BEGIN DECLARE PastWeek DATE; DECLARE DaysLimit INT DEFAULT 365; DECLARE YearDIFF INT DEFAULT 0; # Начальная дата таблицы календаря минус одна неделя от текущей даты SET PastWeek=DATE_SUB(CURDATE(),INTERVAL 1 WEEK); # Чистим данные и индексы TRUNCATE TABLE calendar_byholiday; # Для заполнения таблицы используем SEQUENCE от 1 до 365 # Таблица пересоздаётся каждый день в 00:00 или по факту изменения таблицы holidays INSERT INTO calendar_byholiday (cdate,holiday) SELECT DATE (PastWeek + INTERVAL s.seq DAY) AS cdate, IF(DAYOFWEEK(DATE (PastWeek + INTERVAL s.seq DAY)) >= 2 AND DAYOFWEEK(DATE (PastWeek + INTERVAL s.seq DAY)) <= 6,0,1) as holiday FROM (SELECT seq FROM seq_0_to_365) s LIMIT DaysLimit ; # вычисляем разницу лет для периодических дат SET YearDIFF = YEAR(CURDATE()) - 1970; # отмечаем праздничные дни для периодических дат UPDATE calendar_byholiday c, holidays_periodic hp SET c.holiday = 1 WHERE c.cdate=DATE_ADD(hp.hdate,INTERVAL YearDIFF YEAR); # отмечаем перенесенные праздничные данные на другие дни UPDATE calendar_byholiday c, holidays h SET c.holiday = 1 WHERE (c.cdate=h.hdate OR c.cdate=h.hdate_remap) AND h.hdate_workday=0; # отмечаем перенесенные даты отмеченные как рабочие дни. UPDATE calendar_byholiday c, holidays h SET c.holiday = 0 WHERE c.cdate=h.hdate AND h.hdate_workday=1; END
Далее достаточно сделать простой запрос
call rebuild_calendar_byholiday(); select * from calendar_byholiday;
Многие наверное скажут "ну и что? где тут магия ?"
А магия в том, что глубину календаря можно регулировать изменением всего одного параметра DaysLimit, а также максимальным значением в цикле
FROM (SELECT seq FROM seq_0_to_365) s LIMIT DaysLimit;
Создание календаря по времени занимает просто копейки.
call rebuild_calendar_byholiday(); /* Затронуто строк: 0 Найденные строки: 0 Предупреждения: 0 Длительность 1 запрос: 0,032 sec. */
Но какой же календарь без функций приведения дат.
Обычные операции в календаре подразумевают определение дат следующих за искомой через определенный интервал времени. Например предыдущий рабочий день, первый день квартала, последний рабочий день месяца и т.д.
Ниже я собрал в единое целое свои наработки и Best Practices из сети по обработке дат
CREATE DEFINER=`root`@`%` FUNCTION `date_transform`( `InDate` TIMESTAMP, `Method` INT, `Units` VARCHAR(10), `Duration` INT SIGNED ) RETURNS datetime LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT 'Трансформация даты' BEGIN DECLARE ReturnDate DATETIME; DECLARE LastQuarterDate DATE; DECLARE LastYearDate DATE; SET ReturnDate=InDate; CASE WHEN Method = 2 THEN # Предыдущий рабочий день SELECT CASE WHEN WEEKDAY(InDate - INTERVAL 1 DAY) = 5 THEN (InDate - INTERVAL 2 DAY) WHEN WEEKDAY(InDate - INTERVAL 1 DAY) = 6 THEN (InDate - INTERVAL 3 DAY) ELSE (InDate - INTERVAL 1 DAY) END INTO ReturnDate; WHEN Method = 3 THEN # Первое число месяца SELECT (InDate - INTERVAL DAYOFMONTH(InDate)-1 DAY) INTO ReturnDate; WHEN Method = 4 THEN # Первое число квартала SELECT STR_TO_DATE(CONCAT(EXTRACT(YEAR FROM InDate),'-',(QUARTER(InDate)-1)*3+1,'-01'),'%Y-%m-%d') INTO ReturnDate; WHEN Method = 5 THEN # Первое число года SELECT STR_TO_DATE(CONCAT(EXTRACT(YEAR FROM InDate),'-01-01'),'%Y-%m-%d') INTO ReturnDate; WHEN Method = 6 THEN # Последнее число месяца SELECT LAST_DAY(InDate) INTO ReturnDate; WHEN Method = 7 THEN # Последнее число квартала SELECT CASE WHEN MOD(MONTH(InDate),3) != 0 THEN LAST_DAY(DATE_ADD(InDate,INTERVAL (3-MOD(MONTH(InDate),3)) MONTH)) ELSE LAST_DAY(InDate) END INTO ReturnDate; WHEN Method = 8 THEN # Последнее число года SELECT STR_TO_DATE(CONCAT(EXTRACT(YEAR FROM InDate),'-12-31'),'%Y-%m-%d') INTO ReturnDate; WHEN Method = 9 THEN # Последний рабочий день месяца SELECT CASE WHEN WEEKDAY(LAST_DAY(InDate)) = 5 THEN DATE_SUB(LAST_DAY(InDate),INTERVAL 1 DAY) WHEN WEEKDAY(LAST_DAY(InDate)) = 6 THEN DATE_SUB(LAST_DAY(InDate),INTERVAL 2 DAY) ELSE LAST_DAY(InDate) END INTO ReturnDate; WHEN Method = 10 THEN # Последний рабочий день квартала SELECT CASE WHEN MOD(MONTH(InDate),3) != 0 THEN LAST_DAY(DATE_ADD(InDate,INTERVAL (3-MOD(MONTH(InDate),3)) MONTH)) ELSE LAST_DAY(InDate) END INTO LastQuarterDate; SELECT CASE WHEN WEEKDAY(LAST_DAY(LastQuarterDate)) = 5 THEN DATE_SUB(LAST_DAY(LastQuarterDate),INTERVAL 1 DAY) WHEN WEEKDAY(LAST_DAY(LastQuarterDate)) = 6 THEN DATE_SUB(LAST_DAY(LastQuarterDate),INTERVAL 2 DAY) ELSE LAST_DAY(LastQuarterDate) END INTO ReturnDate; WHEN Method = 11 THEN # Последний рабочий день года SELECT STR_TO_DATE(CONCAT(EXTRACT(YEAR FROM InDate),'-12-31'),'%Y-%m-%d') INTO LastYearDate; SELECT CASE WHEN WEEKDAY(LAST_DAY(LastYearDate)) = 5 THEN DATE_SUB(LAST_DAY(LastYearDate),INTERVAL 1 DAY) WHEN WEEKDAY(LAST_DAY(LastYearDate)) = 6 THEN DATE_SUB(LAST_DAY(LastYearDate),INTERVAL 2 DAY) ELSE LAST_DAY(LastYearDate) END INTO ReturnDate; WHEN Method = 12 THEN # Понедельник SELECT DATE_SUB(InDate,INTERVAL WEEKDAY(InDate) DAY) INTO ReturnDate; WHEN Method = 13 THEN # Вторник SELECT DATE_SUB(InDate,INTERVAL WEEKDAY(InDate)-1 DAY) INTO ReturnDate; WHEN Method = 14 THEN # Среда SELECT DATE_SUB(InDate,INTERVAL WEEKDAY(InDate)-2 DAY) INTO ReturnDate; WHEN Method = 15 THEN # Четверг SELECT DATE_SUB(InDate,INTERVAL WEEKDAY(InDate)-3 DAY) INTO ReturnDate; WHEN Method = 16 THEN # Пятница SELECT DATE_SUB(InDate,INTERVAL WEEKDAY(InDate)-4 DAY) INTO ReturnDate; WHEN Method = 17 THEN # Суббота SELECT DATE_SUB(InDate,INTERVAL WEEKDAY(InDate)-5 DAY) INTO ReturnDate; WHEN Method = 18 THEN # Воскресенье SELECT DATE_SUB(InDate,INTERVAL WEEKDAY(InDate)-6 DAY) INTO ReturnDate; ELSE # Без модификации BEGIN END; END CASE; SELECT CASE WHEN Units='minute' THEN ReturnDate + INTERVAL Duration MINUTE WHEN Units='hour' THEN ReturnDate + INTERVAL Duration HOUR WHEN Units='day' THEN ReturnDate + INTERVAL Duration DAY WHEN Units='week' THEN ReturnDate + INTERVAL Duration WEEK WHEN Units='month' THEN ReturnDate + INTERVAL Duration MONTH WHEN Units='year' THEN ReturnDate + INTERVAL Duration YEAR WHEN Units='workday' THEN get_workday_offset(ReturnDate,Duration) ELSE ReturnDate END INTO ReturnDate; RETURN ReturnDate; END
CREATE DEFINER=`root`@`%` FUNCTION `get_workday_offset`( `InDate` DATETIME, `Offset` INT ) RETURNS DATETIME LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN DECLARE ReturnDate DATETIME DEFAULT NULL; SELECT cdate INTO ReturnDate FROM calendar_byholiday WHERE cdate >= InDate AND holiday=0 LIMIT Offset,1; RETURN ReturnDate; END
Собственно для чего это нужно?
Это нужно для вычисления различной периодики в событиях.
Например нужно понять на какое число приходится 6й рабочий день от начала месяца в январе 2017 года.
MariaDB [db]> select date_transform('2017-01-26',3,'workday',6); +--------------------------------------------+ | date_transform('2017-01-26',3,'workday',6) | +--------------------------------------------+ | 2017-01-10 00:00:00 | +--------------------------------------------+ 1 row in set (0.00 sec)
Или каким числом был понедельник для даты 15 декабря 2016 года.
MariaDB [db]> select date_transform('2016-12-15',12,0,0); +-------------------------------------+ | date_transform('2016-12-15',12,0,0) | +-------------------------------------+ | 2016-12-12 00:00:00 | +-------------------------------------+ 1 row in set (0.00 sec)
И т.д.
Соответственно функция легко вызывается из обычных запросов и является довольно гибкой.
Формат запроса следующий
date_transform(DATETIME,ModifyID,Units,Offset)
где
DATETIME — дата или дата со временем
ModifyID:
id;name 1;Без модификации 2;Предыдущий рабочий день 3;Первое число месяца 4;Первое число квартала 5;Первое число года 6;Последнее число месяца 7;Последнее число квартала 8;Последнее число года 9;Последний рабочий день месяца 10;Последний рабочий день квартала 11;Последний рабочий день года 12;Понедельник 13;Вторник 14;Среда 15;Четверг 16;Пятница 17;Суббота 18;Воскресенье
Units:
minute hour day week month year workday
Offset: SIGNED INT
Если Offset указать отрицательным числом, то вычисление будет производиться назад.
В функции приведения дат, вычисление последних рабочих дней недели, месяца, года сделано независимо от календаря, т.е. для глобального календаря. Если нужна привязка к локальному календарю с праздничными днями, то лучше использовать ‘workday’ offset с отрицательным значением.
Надеюсь кому-то данный материал поможет в разработке.
© Aborche 2016
ссылка на оригинал статьи https://habrahabr.ru/post/318022/
Добавить комментарий