<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

A function to calculate working days and substracting holidays within a period in MySQL

Published on
14,511 Points
8,511 Views
Last Modified:
Approved
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
http://stackoverflow.com/questions/1828948/mysql-function-to-find-the-number-of-working-days-between-two-dates
And code from
http://stackoverflow.com/users/554795/muhammad-haseeb-khan

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;

Open in new window


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
'2013-01-01'      1
'2013-01-02'      2
'2013-01-03'      3
'2013-01-04'      4

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.

DELIMITER $$

DROP FUNCTION IF EXISTS `YourDataBase`.`WORKDAYS`$$
CREATE FUNCTION  `YourDataBase`.`WORKDAYS`(first_date DATETIME, second_date DATETIME) RETURNS int(11)
    
DETERMINISTIC

BEGIN

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;
ELSE
SET start_date = second_date;
SET end_date = first_date;
END IF;
## 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);
SET NumberOfWeeks=FLOOR(diff/7);
SET RemainingDays=MOD(diff,7);
SET firstDayOfTheWeek=DAYOFWEEK(start_date);
SET lastDayOfTheWeek=DAYOFWEEK(end_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;
   ELSE
       IF( firstDayOfTheWeek=7) THEN SET RemainingDays=RemainingDays-1;
         IF (lastDayOfTheWeek=6) THEN  SET RemainingDays=RemainingDays-1; END IF;
       ELSE SET RemainingDays=RemainingDays-2;
       END IF;
   END IF;
#Here we count the holidays that had occured during the period where are testing
SELECT COUNT(DATE) INTO @DESC
FROM holidays
WHERE FECHA BETWEEN start_date AND end_date;
SET holiday=@DESC;
   SET WorkingDays=NumberOfWeeks*5;
#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;
 END;
 $$
DELIMITER ;

Open in new window


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
WORKDAYS(FirstDate,FinalDate)
Normally I use CURRENT_DATE as the final date, but you can use any date.
0
Comment
Author:xema
1 Comment
LVL 17

Author Comment

by:xema
lherrou;
I didn't intended to cheat buy having two accounts, it was a mistake several years ago an the staf of EE was aware of this.
As for the article Y mut said english is not my primary languaje, Y will do some spell check and post it corrected

Thanks
0

Featured Post

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Join & Write a Comment

In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month