Calendar functions in MySQL and MariaDB

As practice shows, many systems using calendars are usually built in the form of static tables that list the dates and their correspondence to work, weekends, holidays. Usually, problems start when the system works without programmer intervention for a long time and the filled calendar simply ends. For the next project, I decided to slightly optimize this situation and wrote a calendar that is created or recalculated automatically, for example, by the built-in timer.

I don’t know how many people have information about the presence in MariaDB (MySQL) of a built-in iterator that returns values ​​over a given range. If anyone knows, you can read the link .

The principle is simple. It is the same as the usual For loop, to which the beginning, end and step are transmitted. This iterator has a fantastic speed and allows for the computation of sequences.

Let's start with the simple.

As we all know, a regular calendar consists of working days and weekends, as well as official public holidays and days for which these holidays will be rescheduled if they fell on a weekend. The transfer of holidays usually occurs by order of the government of the Russian Federation in the middle of the current year.

Create 2 tables. Periodic annual holidays and weekend transfers.

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 ; 

Fill the table with periodic holidays

 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); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (16, '2017-01-02', '0000-00-00', '', 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (17, '2017-01-03', '0000-00-00', '', 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (18, '2017-01-04', '0000-00-00', '', 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (19, '2017-01-05', '0000-00-00', '', 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (20, '2017-01-06', '0000-00-00', '', 0); 

Note that in the holidays table there is a hdate_workday field, used if the transfer is made from a weekend that is reassigned by the worker. For example, February 20, 2016 falls on Saturday, but according to the position of the government, this day is postponed to February 22 and is designated as a working day. Those. There is a working Saturday, after which there is 3 days of rest.

We have the initial data, now the magic is SEQUENCE. Create a calendar table

 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 ; 

To fill the table, create a stored procedure.

 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 

Next, just make a simple request.

 call rebuild_calendar_byholiday(); select * from calendar_byholiday; 

Many will probably say "so what? Where is the magic?" And the magic is that the depth of the calendar can be adjusted by changing only one parameter DaysLimit, as well as the maximum value in the cycle.

 FROM (SELECT seq FROM seq_0_to_365) s LIMIT DaysLimit; 

Creating a calendar by time takes just a penny.

 call rebuild_calendar_byholiday(); /*  : 0  : 0 : 0  1 : 0,032 sec. */ 

But what a calendar without date-casting functions. The usual operations in the calendar imply the determination of the dates following the search after a certain time interval. For example, the previous working day, the first day of the quarter, the last working day of the month, etc. Below, I have put together my own practices and Best Practices from the date processing network.

 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; SET Offset=Offset-1; CASE WHEN Offset < 0 THEN SELECT InDate INTO ReturnDate; ELSE BEGIN SELECT cdate INTO ReturnDate FROM calendar_byholiday WHERE cdate >= InDate AND holiday=0 LIMIT Offset,1; END; END CASE; RETURN ReturnDate; END 

Actually what is it for? It is necessary to calculate various periodicals in the events. For example, you need to understand what day is the 6th working day from the beginning of the month in January 2017.

 MariaDB [db]> select date_transform('2017-01-26',3,'workday',6); +--------------------------------------------+ | date_transform('2017-01-26',3,'workday',6) | +--------------------------------------------+ | 2017-01-16 00:00:00 | +--------------------------------------------+ 1 row in set (0.01 sec) 

Or what date was Monday for December 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) 

Etc. Accordingly, the function is easily called from normal requests and is quite flexible. The format of the request is as follows:

date_transform (DATETIME, ModifyID, Units, Offset)

Where
DATETIME - date or date with time

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

If Offset is specified as a negative number, the calculation will be performed backwards.

In the function of bringing dates, the calculation of the last working days of the week, month, year is done independently of the calendar, i.e. for the global calendar. If you need a binding to a local calendar with holidays, it is better to use a 'workday' offset with a negative value.

I hope someone this material will help in the development.

© Aborche 2016