This article describes how to store recurring events in a MySQL database. This might be useful if you're building a calendar or some sort of job scheduler.
The table for storing recurring events is inspired by cron http://www.manpagez.com/man/5/crontab/. You will notice obvious similarities between the table structure and the structure of a crontab file. The table structure also uses the SET datatype in MySQL. You should be familiar with this type before you continue. Documentation can be found here.
Just as an example, I'll create a table that can be used to schedule the automatic updating of files. Here is the SQL statement you might use to create this table:
CREATE TABLE `File Update Scheduler` (
`Filename` varchar(255) NOT NULL default '',
`LastRefreshed` timestamp NULL default NULL COMMENT 'Timestamp indicating when the file was last refreshed',
`ScheduledMinutes` set('0','15','30','45') NOT NULL COMMENT 'The minutes at which this file is scheduled to be updated',
`ScheduledHours` set('0','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23') NOT NULL COMMENT 'The hours at which this file is scheduled to be updated',
`ScheduledDaysOfMonth` set('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35') NOT NULL COMMENT 'The days of the month on which this file is scheduled to be updated',
`ScheduledMonthType` enum('Calendar Month','Fiscal Month 5-4-4') NOT NULL COMMENT 'Defines how the starting date for each month is calculated, either by regular calendar dates, or by fiscal dates',
`ScheduledDaysOfWeek` set('Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday') NOT NULL COMMENT 'The days of the week on which this file is scheduled to be updated',
`ScheduledMonths` set('1','2','3','4','5','6','7','8','9','10','11','12') NOT NULL COMMENT 'The months of the year on which this file is scheduled to be updated',
PRIMARY KEY (`Filename`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Specifies a list of Excel documents that should be updated.'
Now all you need is a function to calculate the next date that this event is scheduled to occur. The following is such a function. Please read the comments for the function, and feel free to play around with it.
CREATE FUNCTION NextScheduledTimestamp(
LastOccurance TIMESTAMP,
ScheduledMinutes SET('0','15','30','45'),
ScheduledHours SET('0','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23'),
ScheduledDaysOfMonth SET('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35'),
ScheduledMonthType ENUM('Calendar Month','Fiscal Month 5-4-4'),
ScheduledDaysOfWeek SET('Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday'),
ScheduledMonths SET('1','2','3','4','5','6','7','8','9','10','11','12')
) RETURNS TIMESTAMP
DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
COMMENT 'Returns the next timestamp at which the specified event is scheduled to occur; Returns NULL if the event will not occur again or if the schedule is invalid. ScheduledDaysOfWeek is ignored when ScheduledMonthType is "Fiscal Month 5-4-4" since ScheduledDaysOfMonth correspond to days of the week. When ScheduledMonthType is "Calendar Month" and ScheduledDaysOfMonth has any day between 31-35 set, the event will be scheduled on the last day of the next scheduled month. If day 30 is set, the event will be scheduled on the 30th of each month, except if the next scheduled month is February, in which case, it will be scheduled on the last day of February. Similarly, when ScheduledMonthType is "Fiscal Month 5-4-4", if ScheduledDaysOfMonth has any day between 29-35 set and the next scheduled month has only 28 days, the event will occur on the last day of the month.'
BEGIN
DECLARE nextDate TIMESTAMP DEFAULT LastOccurance;
DECLARE nextDateWeek TINYINT UNSIGNED;
DECLARE fiscalQuarter TINYINT UNSIGNED;
DECLARE fiscalMonth TINYINT UNSIGNED;
DECLARE remainderWeeks TINYINT UNSIGNED;
DECLARE weekInMonth TINYINT UNSIGNED;
DECLARE done BOOLEAN;
/* Check for invalid schedule */
IF ScheduledMinutes = 0 OR ScheduledHours = 0 OR ScheduledDaysOfMonth = 0 OR ScheduledDaysOfWeek = 0 OR ScheduledMonths = 0 THEN
RETURN NULL;
END IF;
/* Round nextDate to the following minute */
SET nextDate = DATE_SUB(nextDate, INTERVAL SECOND(nextDate) SECOND);
SET nextDate = DATE_ADD(nextDate, INTERVAL 1 MINUTE);
SET done = FALSE;
IF ScheduledMonthType = 'Calendar Month' THEN
/* Check for more invalid schedules - make sure that a future date actually exists. */
/*IF ScheduledDaysOfMonth & ((1 << 30)-1) = 0 AND ScheduledMonths & 2773 = 0 THEN --2773 = 0b101011010101 = '1,3,5,7,8,10,12'
--Schedule only runs on days 31-35. No month with 31 days is selected.
RETURN NULL;
ELSEIF ScheduledDaysOfMonth & ((1 << 29)-1) = 0 AND ScheduledMonths = 2 THEN --2 = 0b000000000010 = '2'
--Schedule only runs on days 30-35. No month with 30 days is selected.
RETURN NULL;
END IF;*/
WHILE NOT done DO
/* 1 << x is the same as POW(2, x) */
IF 1 << (MONTH(nextDate)-1) & ScheduledMonths = 0 THEN
/* Go to midnight of the first day of the next month */
SET nextDate = DATE(nextDate);
SET nextDate = DATE_ADD(nextDate, INTERVAL 1-DAYOFMONTH(nextDate) DAY);
SET nextDate = DATE_ADD(nextDate, INTERVAL 1 MONTH);
ELSEIF (1 << (DAY(nextDate)-1) & ScheduledDaysOfMonth = 0 AND /* If this is the last day of the month, and days 30 or 31 are scheduled to run, don't increment the day */
/* 536870912 = 0b100000000000000000000000000000 = '30'*/
NOT (DAYOFMONTH(DATE_ADD(nextDate, INTERVAL 1 DAY)) = 1 AND ScheduledDaysOfMonth >= 536870912) ) OR 1 << (DAYOFWEEK(nextDate)-1) & ScheduledDaysOfWeek = 0 THEN
/* Go to midnight of the next day */
SET nextDate = DATE(nextDate);
SET nextDate = DATE_ADD(nextDate, INTERVAL 1 DAY);
ELSEIF 1 << HOUR(nextDate) & ScheduledHours = 0 THEN
/* Go to minute 0 of next hour */
SET nextDate = DATE_SUB(nextDate, INTERVAL MINUTE(nextDate) MINUTE);
SET nextDate = DATE_ADD(nextDate, INTERVAL 1 HOUR);
ELSEIF 1 << (MINUTE(nextDate) DIV 15) & ScheduledMinutes = 0 OR MINUTE(nextDate) % 15 <> 0 THEN
/* Go to next quarter hour */
SET nextDate = DATE_ADD(nextDate, INTERVAL 15 - (MINUTE(nextDate) % 15) MINUTE);
ELSEIF nextDate < LastOccurance THEN
RETURN NULL;
ELSE
SET done = TRUE;
END IF;
END WHILE;
ELSE
/* Check for more invalid schedules - make sure that a future date actually exists. */
/*IF ScheduledDaysOfMonth & ((1 << 28)-1) = 0 AND ScheduledMonths & 2633 = 0 THEN --2633 = 0b101001001001 = '1,4,7,10,12'
--Schedule only runs on days 29-35. No month with 5 weeks is selected.
RETURN NULL;
END IF;*/
WHILE NOT done DO
/* Calculate week number of nextDate, based on Excel's methodology (week should be between 1-53) */
SET nextDateWeek = WEEK(nextDate, 0);
/* If Jan 1st week number is 0, incremement week number */
IF WEEK(DATE_ADD(DATE(nextDate), INTERVAL 1-DAYOFYEAR(nextDate) DAY), 0) = 0 THEN
SET nextDateWeek = nextDateWeek +1;
END IF;
/* Calculate fiscal month */
SET fiscalQuarter = (nextDateWeek-1) DIV (5+4+4);
SET remainderWeeks = (nextDateWeek-1) % (5+4+4);
IF nextDateWeek > 52 THEN
SET fiscalMonth = 12;
SET fiscalQuarter = 3;
SET weekInMonth = 5;
ELSEIF remainderWeeks <= 4 THEN
SET fiscalMonth = fiscalQuarter * 3 + 1;
SET weekInMonth = remainderWeeks+1;
ELSEIF remainderWeeks <= 4+4 THEN
SET fiscalMonth = fiscalQuarter * 3 + 2;
SET weekInMonth = remainderWeeks-5+1;
ELSE
SET fiscalMonth = fiscalQuarter * 3 + 3;
SET weekInMonth = remainderWeeks-5-4+1;
END IF;
IF 1 << (fiscalMonth-1) & ScheduledMonths = 0 THEN
/* Go to midnight of the first day of the next fiscal month */
SET nextDate = DATE(nextDate);
SET nextDate = DATE_ADD(nextDate, INTERVAL 1-DAYOFWEEK(nextDate) DAY);
/* I'm now at the beginning of the week. Put me at the beginning of the next fiscal month. */
IF fiscalMonth = 12 THEN
SET nextDate = DATE_ADD(nextDate, INTERVAL 1-DAYOFYEAR(nextDate) DAY);
SET nextDate = DATE_ADD(nextDate, INTERVAL 1 YEAR);
ELSE
CASE fiscalMonth % 3
WHEN 1 THEN
/* I'm in a 5-week month */
SET nextDate = DATE_ADD(nextDate, INTERVAL 5 - weekInMonth + 1 WEEK);
ELSE
/* I'm in a 4-week month */
SET nextDate = DATE_ADD(nextDate, INTERVAL 4 - weekInMonth + 1 WEEK);
END CASE;
END IF;
ELSEIF 1 << ( (weekInMonth-1)*7+DAYOFWEEK(nextDate)-1) & ScheduledDaysOfMonth = 0 AND
/* If this is the last day of the month, and days 30 or 31 are scheduled to run, don't increment the day */
/* 268435456 = 0b10000000000000000000000000000 = '29' */
NOT (DAYOFWEEK(nextDate)=7 AND (CASE fiscalMonth % 3 WHEN 1 THEN 5 ELSE 4 END)=weekInMonth AND ScheduledDaysOfMonth >= 268435456) THEN
/* Go to midnight of the next day */
SET nextDate = DATE(nextDate);
SET nextDate = DATE_ADD(nextDate, INTERVAL 1 DAY);
ELSEIF 1 << HOUR(nextDate) & ScheduledHours = 0 THEN
/* Go to minute 0 of next hour */
SET nextDate = DATE_SUB(nextDate, INTERVAL MINUTE(nextDate) MINUTE);
SET nextDate = DATE_ADD(nextDate, INTERVAL 1 HOUR);
ELSEIF 1 << (MINUTE(nextDate) DIV 15) & ScheduledMinutes = 0 OR MINUTE(nextDate) % 15 <> 0 THEN
/* Go to next quarter hour */
SET nextDate = DATE_ADD(nextDate, INTERVAL 15 - (MINUTE(nextDate) % 15) MINUTE);
ELSE
SET done = TRUE;
END IF;
END WHILE;
END IF;
RETURN nextDate;
END//
The table for storing recurring events is inspired by cron http://www.manpagez.com/man/5/crontab/. You will notice obvious similarities between the table structure and the structure of a crontab file. The table structure also uses the SET datatype in MySQL. You should be familiar with this type before you continue. Documentation can be found here.
Just as an example, I'll create a table that can be used to schedule the automatic updating of files. Here is the SQL statement you might use to create this table:
CREATE TABLE `File Update Scheduler` (
`Filename` varchar(255) NOT NULL default '',
`LastRefreshed` timestamp NULL default NULL COMMENT 'Timestamp indicating when the file was last refreshed',
`ScheduledMinutes` set('0','15','30','45') NOT NULL COMMENT 'The minutes at which this file is scheduled to be updated',
`ScheduledHours` set('0','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23') NOT NULL COMMENT 'The hours at which this file is scheduled to be updated',
`ScheduledDaysOfMonth` set('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35') NOT NULL COMMENT 'The days of the month on which this file is scheduled to be updated',
`ScheduledMonthType` enum('Calendar Month','Fiscal Month 5-4-4') NOT NULL COMMENT 'Defines how the starting date for each month is calculated, either by regular calendar dates, or by fiscal dates',
`ScheduledDaysOfWeek` set('Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday') NOT NULL COMMENT 'The days of the week on which this file is scheduled to be updated',
`ScheduledMonths` set('1','2','3','4','5','6','7','8','9','10','11','12') NOT NULL COMMENT 'The months of the year on which this file is scheduled to be updated',
PRIMARY KEY (`Filename`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Specifies a list of Excel documents that should be updated.'
Now all you need is a function to calculate the next date that this event is scheduled to occur. The following is such a function. Please read the comments for the function, and feel free to play around with it.
CREATE FUNCTION NextScheduledTimestamp(
LastOccurance TIMESTAMP,
ScheduledMinutes SET('0','15','30','45'),
ScheduledHours SET('0','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23'),
ScheduledDaysOfMonth SET('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35'),
ScheduledMonthType ENUM('Calendar Month','Fiscal Month 5-4-4'),
ScheduledDaysOfWeek SET('Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday'),
ScheduledMonths SET('1','2','3','4','5','6','7','8','9','10','11','12')
) RETURNS TIMESTAMP
DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
COMMENT 'Returns the next timestamp at which the specified event is scheduled to occur; Returns NULL if the event will not occur again or if the schedule is invalid. ScheduledDaysOfWeek is ignored when ScheduledMonthType is "Fiscal Month 5-4-4" since ScheduledDaysOfMonth correspond to days of the week. When ScheduledMonthType is "Calendar Month" and ScheduledDaysOfMonth has any day between 31-35 set, the event will be scheduled on the last day of the next scheduled month. If day 30 is set, the event will be scheduled on the 30th of each month, except if the next scheduled month is February, in which case, it will be scheduled on the last day of February. Similarly, when ScheduledMonthType is "Fiscal Month 5-4-4", if ScheduledDaysOfMonth has any day between 29-35 set and the next scheduled month has only 28 days, the event will occur on the last day of the month.'
BEGIN
DECLARE nextDate TIMESTAMP DEFAULT LastOccurance;
DECLARE nextDateWeek TINYINT UNSIGNED;
DECLARE fiscalQuarter TINYINT UNSIGNED;
DECLARE fiscalMonth TINYINT UNSIGNED;
DECLARE remainderWeeks TINYINT UNSIGNED;
DECLARE weekInMonth TINYINT UNSIGNED;
DECLARE done BOOLEAN;
/* Check for invalid schedule */
IF ScheduledMinutes = 0 OR ScheduledHours = 0 OR ScheduledDaysOfMonth = 0 OR ScheduledDaysOfWeek = 0 OR ScheduledMonths = 0 THEN
RETURN NULL;
END IF;
/* Round nextDate to the following minute */
SET nextDate = DATE_SUB(nextDate, INTERVAL SECOND(nextDate) SECOND);
SET nextDate = DATE_ADD(nextDate, INTERVAL 1 MINUTE);
SET done = FALSE;
IF ScheduledMonthType = 'Calendar Month' THEN
/* Check for more invalid schedules - make sure that a future date actually exists. */
/*IF ScheduledDaysOfMonth & ((1 << 30)-1) = 0 AND ScheduledMonths & 2773 = 0 THEN --2773 = 0b101011010101 = '1,3,5,7,8,10,12'
--Schedule only runs on days 31-35. No month with 31 days is selected.
RETURN NULL;
ELSEIF ScheduledDaysOfMonth & ((1 << 29)-1) = 0 AND ScheduledMonths = 2 THEN --2 = 0b000000000010 = '2'
--Schedule only runs on days 30-35. No month with 30 days is selected.
RETURN NULL;
END IF;*/
WHILE NOT done DO
/* 1 << x is the same as POW(2, x) */
IF 1 << (MONTH(nextDate)-1) & ScheduledMonths = 0 THEN
/* Go to midnight of the first day of the next month */
SET nextDate = DATE(nextDate);
SET nextDate = DATE_ADD(nextDate, INTERVAL 1-DAYOFMONTH(nextDate) DAY);
SET nextDate = DATE_ADD(nextDate, INTERVAL 1 MONTH);
ELSEIF (1 << (DAY(nextDate)-1) & ScheduledDaysOfMonth = 0 AND /* If this is the last day of the month, and days 30 or 31 are scheduled to run, don't increment the day */
/* 536870912 = 0b100000000000000000000000000000 = '30'*/
NOT (DAYOFMONTH(DATE_ADD(nextDate, INTERVAL 1 DAY)) = 1 AND ScheduledDaysOfMonth >= 536870912) ) OR 1 << (DAYOFWEEK(nextDate)-1) & ScheduledDaysOfWeek = 0 THEN
/* Go to midnight of the next day */
SET nextDate = DATE(nextDate);
SET nextDate = DATE_ADD(nextDate, INTERVAL 1 DAY);
ELSEIF 1 << HOUR(nextDate) & ScheduledHours = 0 THEN
/* Go to minute 0 of next hour */
SET nextDate = DATE_SUB(nextDate, INTERVAL MINUTE(nextDate) MINUTE);
SET nextDate = DATE_ADD(nextDate, INTERVAL 1 HOUR);
ELSEIF 1 << (MINUTE(nextDate) DIV 15) & ScheduledMinutes = 0 OR MINUTE(nextDate) % 15 <> 0 THEN
/* Go to next quarter hour */
SET nextDate = DATE_ADD(nextDate, INTERVAL 15 - (MINUTE(nextDate) % 15) MINUTE);
ELSEIF nextDate < LastOccurance THEN
RETURN NULL;
ELSE
SET done = TRUE;
END IF;
END WHILE;
ELSE
/* Check for more invalid schedules - make sure that a future date actually exists. */
/*IF ScheduledDaysOfMonth & ((1 << 28)-1) = 0 AND ScheduledMonths & 2633 = 0 THEN --2633 = 0b101001001001 = '1,4,7,10,12'
--Schedule only runs on days 29-35. No month with 5 weeks is selected.
RETURN NULL;
END IF;*/
WHILE NOT done DO
/* Calculate week number of nextDate, based on Excel's methodology (week should be between 1-53) */
SET nextDateWeek = WEEK(nextDate, 0);
/* If Jan 1st week number is 0, incremement week number */
IF WEEK(DATE_ADD(DATE(nextDate), INTERVAL 1-DAYOFYEAR(nextDate) DAY), 0) = 0 THEN
SET nextDateWeek = nextDateWeek +1;
END IF;
/* Calculate fiscal month */
SET fiscalQuarter = (nextDateWeek-1) DIV (5+4+4);
SET remainderWeeks = (nextDateWeek-1) % (5+4+4);
IF nextDateWeek > 52 THEN
SET fiscalMonth = 12;
SET fiscalQuarter = 3;
SET weekInMonth = 5;
ELSEIF remainderWeeks <= 4 THEN
SET fiscalMonth = fiscalQuarter * 3 + 1;
SET weekInMonth = remainderWeeks+1;
ELSEIF remainderWeeks <= 4+4 THEN
SET fiscalMonth = fiscalQuarter * 3 + 2;
SET weekInMonth = remainderWeeks-5+1;
ELSE
SET fiscalMonth = fiscalQuarter * 3 + 3;
SET weekInMonth = remainderWeeks-5-4+1;
END IF;
IF 1 << (fiscalMonth-1) & ScheduledMonths = 0 THEN
/* Go to midnight of the first day of the next fiscal month */
SET nextDate = DATE(nextDate);
SET nextDate = DATE_ADD(nextDate, INTERVAL 1-DAYOFWEEK(nextDate) DAY);
/* I'm now at the beginning of the week. Put me at the beginning of the next fiscal month. */
IF fiscalMonth = 12 THEN
SET nextDate = DATE_ADD(nextDate, INTERVAL 1-DAYOFYEAR(nextDate) DAY);
SET nextDate = DATE_ADD(nextDate, INTERVAL 1 YEAR);
ELSE
CASE fiscalMonth % 3
WHEN 1 THEN
/* I'm in a 5-week month */
SET nextDate = DATE_ADD(nextDate, INTERVAL 5 - weekInMonth + 1 WEEK);
ELSE
/* I'm in a 4-week month */
SET nextDate = DATE_ADD(nextDate, INTERVAL 4 - weekInMonth + 1 WEEK);
END CASE;
END IF;
ELSEIF 1 << ( (weekInMonth-1)*7+DAYOFWEEK(nextDate)-1) & ScheduledDaysOfMonth = 0 AND
/* If this is the last day of the month, and days 30 or 31 are scheduled to run, don't increment the day */
/* 268435456 = 0b10000000000000000000000000000 = '29' */
NOT (DAYOFWEEK(nextDate)=7 AND (CASE fiscalMonth % 3 WHEN 1 THEN 5 ELSE 4 END)=weekInMonth AND ScheduledDaysOfMonth >= 268435456) THEN
/* Go to midnight of the next day */
SET nextDate = DATE(nextDate);
SET nextDate = DATE_ADD(nextDate, INTERVAL 1 DAY);
ELSEIF 1 << HOUR(nextDate) & ScheduledHours = 0 THEN
/* Go to minute 0 of next hour */
SET nextDate = DATE_SUB(nextDate, INTERVAL MINUTE(nextDate) MINUTE);
SET nextDate = DATE_ADD(nextDate, INTERVAL 1 HOUR);
ELSEIF 1 << (MINUTE(nextDate) DIV 15) & ScheduledMinutes = 0 OR MINUTE(nextDate) % 15 <> 0 THEN
/* Go to next quarter hour */
SET nextDate = DATE_ADD(nextDate, INTERVAL 15 - (MINUTE(nextDate) % 15) MINUTE);
ELSE
SET done = TRUE;
END IF;
END WHILE;
END IF;
RETURN nextDate;
END//
Comments