Календарные функции в MySQL и MariaDB

в 7:58, , рубрики: mariadb, mysql, sequence, календарный план

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

Не знаю, многие ли владеют информацией о наличии в MariaDB(MySQL) встроенного итератора возвращающего значения по заданному диапазону. Если кто не знает, то можно прочитать по ссылке.

Принцип простой. Такой же как у обычного цикла 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

Aborche

Автор: aborche

Источник

* - обязательные к заполнению поля

Главная   |  Архив новостей  |   Android  |   Google  |   Apple  |   Microsoft  |   Информационная безопасность  |   Веб – разработка
Публикации RSS  |  Комментарии RSS
https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js