Browse All Articles
> A function to calculate working days and substracting holidays within a period in MySQL
I needed to calculate the working days between the date a request was made and today, searching the web I found this was a common request and found several ideas or code but most of them lack the ability to include the holidays.
As we already have something like this working on PHP I grab the idea of using a table containing the holidays as dates. And the code I found this thread appeal me for its simplicity
And code from
However this FUNCTION didn't consider the holidays and for my specific needs it added one more day as my period of time starts the next day after the request is made, so I modify it to cover my needs.
I would like to note that on my first attempt to run this function, MySQL thrown out an error
"#1436 - Thread stack overrun: 6136 bytes used of a 131072 byte stack, and 128000 bytes needed."
So I modify My.CNF and increased the Thread Stack to 256K
Also this function includes a QUERY so it may not work on all versions of MySQL, it was tested and implemented on version 5.1.54-1 running on an UBUNTU box.
This is the CREATE for the holidays table
DROP TABLE IF EXISTS `YourDataBase`.`holidays`;
CREATE TABLE `YourDataBase`.`holidays` (
`DATE` DATE DEFAULT NOT NULL,
`ID` int(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;
Then you load your holidays; 2013-01-01,2013-MM-DD...
And you end with a table like this, part of the Mexican government holidays for 2013
Then you can make a SELECT COUNT for a period of time and you will get the number of holidays that happened on that period.
Also by using this kind of table it is very easy to keep it updated as in most countries there are few holidays per year
As for getting the working days, Monday through Friday, here is the CREATE for the function I found on another site with minor modifications to subtract the holidays that occurred on the period.
DROP FUNCTION IF EXISTS `YourDataBase`.`WORKDAYS`$$
CREATE FUNCTION `YourDataBase`.`WORKDAYS`(first_date DATETIME, second_date DATETIME) RETURNS int(11)
DECLARE start_date DATE;
DECLARE end_date DATE;
DECLARE diff INT;
DECLARE NumberOfWeeks INT;
DECLARE RemainingDays INT;
DECLARE firstDayOfTheWeek INT;
DECLARE lastDayOfTheWeek INT;
DECLARE WorkingDays INT;
DECLARE holiday INT;
IF (first_date < second_date) THEN
SET start_date = first_date;
SET end_date = second_date;
SET start_date = second_date;
SET end_date = first_date;
## In the original code one was added at the end of the statement to include both days in interval
SET diff = DATEDIFF(end_date, start_date);
IF(firstDayOfTheWeek <= lastDayOfTheWeek) THEN
IF( firstDayOfTheWeek<=6 AND 6 <=lastDayOfTheWeek) THEN SET RemainingDays=RemainingDays-1; END IF;
IF( firstDayOfTheWeek<=7 AND 7 <=lastDayOfTheWeek) THEN SET RemainingDays=RemainingDays-1; END IF;
IF( firstDayOfTheWeek=7) THEN SET RemainingDays=RemainingDays-1;
IF (lastDayOfTheWeek=6) THEN SET RemainingDays=RemainingDays-1; END IF;
ELSE SET RemainingDays=RemainingDays-2;
#Here we count the holidays that had occured during the period where are testing
SELECT COUNT(DATE) INTO @DESC
WHERE FECHA BETWEEN start_date AND end_date;
#Here we substract the number of holidays from the working week
IF(RemainingDays>0) THEN RETURN WorkingDays+RemainingDays-holiday;
ELSE RETURN WorkingDays-holiday; END IF;
By using the same initial and final dates on the calculation of working days and holidays that occurred during that period you obtain the real working days on a period as you substract the holidays that occurred on that period.
You just need to include this statement on your SELECT query
Normally I use CURRENT_DATE as the final date, but you can use any date.