When are the holidays?

When a system sends out emails requesting that some action is to be taken, or deadlines are being set automatically, it is important that these do not fall on dates where there are no administrators at work to deal with any consequences.
You need to know when the holidays are so that if a date is calculated that lands on a holiday, it can be moved forwards or backwards in time.

Here is a procedure that calculates the calculable public bank holidays and office closures for Oxford for a given year:


DROP PROCEDURE IF EXISTS listHolidays;

delimiter //

create procedure listHolidays(
		IN currentYear int(5)
		)
main:BEGIN		
	declare date_in date;
	declare yearEnd_par date;
	declare newYear_par date;
	declare christmasClose_par date;
	
	SET date_in=CONCAT(currentYear,'-01-01');
	SET yearEnd_par=CONCAT(currentYear+1,'-01-01');
	
	# When is new year day?
	IF DAYOFWEEK(yearEnd_par)=1
	THEN
		SET newYear_par=CONCAT(currentYear+1,'-01-02');
	END IF;

	IF DAYOFWEEK(yearEnd_par)=7
	THEN
		SET newYear_par=CONCAT(currentYear+1,'-01-03');

	END IF;

	IF DAYOFWEEK(yearEnd_par)!=7 AND DAYOFWEEK(yearEnd_par)!=1
	THEN
		SET newYear_par=yearEnd_par;
	END IF;
	
	# extend year end
	IF yearEnd_par < newYear_par
	THEN
		SET yearEnd_par=newYear_par;
	END IF;
	
	# calc office closure for christmas
	SET christmasClose_par=DATE_SUB(newYear_par,INTERVAL 9 DAY);
	IF DAYOFWEEK(christmasClose_par)=1 OR DAYOFWEEK(christmasClose_par)=7
	THEN
		SET christmasClose_par=DATE_SUB(christmasClose_par,INTERVAL 2 DAY);
	END IF;
	
	CREATE TEMPORARY TABLE holidays (title varchar(255) not null, type varchar(255), date date not null);
	
	WHILE date_in <= yearEnd_par
	DO
	
		# new year
		IF EXTRACT(MONTH FROM date_in)=1
		THEN

			IF DAYOFWEEK(CONCAT(currentYear,'-01-01'))=1 AND date_in=CONCAT(currentYear,'-01-02')
			THEN
				INSERT INTO holidays values('New Year','bank',date_in);
				
			END IF;

			IF DAYOFWEEK(CONCAT(currentYear,'-01-01'))=7 AND date_in=CONCAT(currentYear,'-01-03')
			THEN
				INSERT INTO holidays values('New Year','bank',date_in);
				
			END IF;

			IF date_in=CONCAT(currentYear,'-01-01') AND DAYOFWEEK(CONCAT(currentYear,'-01-01'))!=7 AND DAYOFWEEK(CONCAT(currentYear,'-01-01'))!=1
			THEN
				INSERT INTO holidays values('New Year','bank',date_in);
				
			END IF;
		END IF;
		
		
		# Good friday, Easter monday
	
		IF EXTRACT(MONTH FROM date_in)=3 OR EXTRACT(MONTH FROM date_in)=4
		THEN
			IF date_in=DATE_SUB(easter(currentYear),INTERVAL 3 day)
			THEN
				INSERT INTO holidays values('Easter','office',date_in);
			END IF;
			
			IF date_in=DATE_SUB(easter(currentYear),INTERVAL 2 day)
			THEN
				INSERT INTO holidays values('Good Friday','bank',date_in);
			END IF;
			
			IF date_in=DATE_ADD(easter(currentYear),INTERVAL 1 day)
			THEN
				INSERT INTO holidays values('Easter Monday','bank',date_in);
			END IF;
		END IF;
		
		# may day, spring bank. 1st and last monday in may.
		IF EXTRACT(MONTH FROM date_in)=5
		THEN

			IF (2-DAYOFWEEK(CONCAT(currentYear,'-05-01')))>=0 AND date_in=DATE_ADD( CONCAT(currentYear,'-05-01'), INTERVAL ( 2- DAYOFWEEK(  CONCAT(currentYear,'-05-01') ) ) DAY )
			THEN
				INSERT INTO holidays values('May Day','bank',date_in);
			END IF;


			IF (2-DAYOFWEEK(CONCAT(currentYear,'-05-01')))<0 AND date_in=DATE_ADD(CONCAT(currentYear,'-05-01'), INTERVAL ( 9 - DAYOFWEEK(CONCAT(currentYear,'-05-01') ) ) DAY)
			THEN
				INSERT INTO holidays values('May Day','bank',date_in);
			END IF;

			IF DAYOFWEEK(CONCAT(currentYear,'-05-31'))>=2 AND date_in=DATE_ADD( CONCAT(currentYear,'-05-31'), INTERVAL ( 2 - DAYOFWEEK(  CONCAT(currentYear,'-05-31')  ) ) DAY )
			THEN
				INSERT INTO holidays values('Spring Bank','bank',date_in);
			END IF;

			IF DAYOFWEEK(CONCAT(currentYear,'-05-31'))=1 AND date_in=DATE_ADD(CONCAT(currentYear,'-05-24'), INTERVAL (2-DAYOFWEEK(CONCAT(currentYear,'-05-31'))) DAY)
			THEN
				INSERT INTO holidays values('Spring Bank','bank',date_in);
			END IF;

		END IF;
		
		IF EXTRACT(MONTH FROM date_in)=8
		THEN

			IF DAYOFWEEK(CONCAT(currentYear,'-08-31'))>=2 AND date_in=DATE_ADD( CONCAT(currentYear,'-08-31'), INTERVAL ( 2 - DAYOFWEEK(  CONCAT(currentYear,'-08-31')  ) ) DAY )
			THEN
				INSERT INTO holidays values('Summer Bank','bank',date_in);
			END IF;

			IF DAYOFWEEK(CONCAT(currentYear,'-08-31'))=1 AND date_in=DATE_ADD(CONCAT(currentYear,'-08-24'), INTERVAL (2-DAYOFWEEK(CONCAT(currentYear,'-08-31'))) DAY)
			THEN
				INSERT INTO holidays values('Summer Bank','bank',date_in);
			END IF;


		END IF;
		
		# christmas day in lieu of weekend
		IF EXTRACT(MONTH FROM date_in)=12
		THEN

			IF DAYOFWEEK(CONCAT(currentYear,'-12-25'))=1 AND date_in=CONCAT(currentYear,'-12-26')
			THEN
				INSERT INTO holidays values('Christmas','bank',date_in);
			END IF;

			IF DAYOFWEEK(CONCAT(currentYear,'-12-25'))=7 AND date_in=CONCAT(currentYear,'-12-27')
			THEN
				INSERT INTO holidays values('Christmas','bank',date_in);
			END IF;

			IF date_in=CONCAT(currentYear,'-12-25') AND DAYOFWEEK(CONCAT(currentYear,'-12-25'))!=7 AND DAYOFWEEK(CONCAT(currentYear,'-12-25'))!=1
			THEN
				INSERT INTO holidays values('Christmas','bank',date_in);
			END IF;
		END IF;
	
		# boxing day or monday or tuesday in lieu
		# university office closures
		IF EXTRACT(MONTH FROM date_in)=12 OR EXTRACT(MONTH FROM date_in)=1
		THEN
			
		
			IF (DAYOFWEEK(CONCAT(currentYear,'-12-25'))=1 OR (DAYOFWEEK(CONCAT(currentYear,'-12-25'))!=7 AND DAYOFWEEK(CONCAT(currentYear,'-12-25'))!=1 AND DAYOFWEEK(CONCAT(currentYear,'-12-26'))=1) ) AND date_in=CONCAT(currentYear,'-12-27')
			THEN
				INSERT INTO holidays values('Boxing Day','bank',date_in);
			END IF;

			IF (DAYOFWEEK(CONCAT(currentYear,'-12-25'))=7 OR (DAYOFWEEK(CONCAT(currentYear,'-12-25'))!=7 AND DAYOFWEEK(CONCAT(currentYear,'-12-25'))!=1 AND DAYOFWEEK(CONCAT(currentYear,'-12-26'))=7) ) AND date_in=CONCAT(currentYear,'-12-28')
			THEN
				INSERT INTO holidays values('Boxing Day','bank',date_in);
			END IF;
			
			IF date_in=CONCAT(currentYear,'-12-26') AND DAYOFWEEK(CONCAT(currentYear,'-12-25'))!=7 AND DAYOFWEEK(CONCAT(currentYear,'-12-25'))!=1 AND DAYOFWEEK(CONCAT(currentYear,'-12-26'))!=7 AND DAYOFWEEK(CONCAT(currentYear,'-12-26'))!=1
			THEN
				INSERT INTO holidays values('Boxing Day','bank',date_in);
			END IF;
			
			
			IF date_in>=christmasClose_par AND date_in <=newYear_par
			THEN
			
				INSERT INTO holidays values('Christmas','office',date_in);
			END IF;
			
			
		END IF;
	
		SET date_in=DATE_ADD(date_in, INTERVAL 1 DAY);
		
	END WHILE;
	
	# output results
	SELECT * FROM holidays;
	
	DROP TEMPORARY TABLE holidays;
END//

delimiter ;

# returns where easter is according to the Carter algorithm
DROP FUNCTION IF EXISTS easter;

delimiter //

create function easter(Y int(5))
	RETURNS date
	
main:BEGIN

	DECLARE D int(4);
	DECLARE E int(4);
	DECLARE Q int(4);
	
	SET D=225-(11 * (Y MOD 19));
	
	WHILE D > 50 DO
	
		SET D=D-30;
	
	END WHILE;

	IF D > 48
	THEN
		SET D=D-1;
	END IF;
	
	
	SET E = (Y + FLOOR(Y/4) + D + 1) MOD 7;
	
	SET Q=D + 7 - E;
	
	IF Q<32
	THEN
	
		return CONCAT(Y,'-','03','-',Q);
	
	ELSE
	
		return CONCAT(Y,'-','04','-',(Q-31));
	
	END IF;

END//

delimiter ;

Obviously, if there are royal jubilees or weddings these will have to be dealt with separately.

For 2012 and 2013 the results are:

mysql> call listHolidays(2012);
+---------------+--------+------------+
| title         | type   | date       |
+---------------+--------+------------+
| New Year      | bank   | 2012-01-02 | 
| Easter        | office | 2012-04-05 | 
| Good Friday   | bank   | 2012-04-06 | 
| Easter Monday | bank   | 2012-04-09 | 
| May Day       | bank   | 2012-05-07 | 
| Spring Bank   | bank   | 2012-05-28 | 
| Summer Bank   | bank   | 2012-08-27 | 
| Christmas     | office | 2012-12-21 | 
| Christmas     | office | 2012-12-22 | 
| Christmas     | office | 2012-12-23 | 
| Christmas     | office | 2012-12-24 | 
| Christmas     | bank   | 2012-12-25 | 
| Christmas     | office | 2012-12-25 | 
| Boxing Day    | bank   | 2012-12-26 | 
| Christmas     | office | 2012-12-26 | 
| Christmas     | office | 2012-12-27 | 
| Christmas     | office | 2012-12-28 | 
| Christmas     | office | 2012-12-29 | 
| Christmas     | office | 2012-12-30 | 
| Christmas     | office | 2012-12-31 | 
| Christmas     | office | 2013-01-01 | 
+---------------+--------+------------+

mysql> call listHolidays(2013);
+---------------+--------+------------+
| title         | type   | date       |
+---------------+--------+------------+
| New Year      | bank   | 2013-01-01 | 
| Easter        | office | 2013-03-28 | 
| Good Friday   | bank   | 2013-03-29 | 
| Easter Monday | bank   | 2013-04-01 | 
| May Day       | bank   | 2013-05-06 | 
| Spring Bank   | bank   | 2013-05-27 | 
| Summer Bank   | bank   | 2013-08-26 | 
| Christmas     | office | 2013-12-23 | 
| Christmas     | office | 2013-12-24 | 
| Christmas     | bank   | 2013-12-25 | 
| Christmas     | office | 2013-12-25 | 
| Boxing Day    | bank   | 2013-12-26 | 
| Christmas     | office | 2013-12-26 | 
| Christmas     | office | 2013-12-27 | 
| Christmas     | office | 2013-12-28 | 
| Christmas     | office | 2013-12-29 | 
| Christmas     | office | 2013-12-30 | 
| Christmas     | office | 2013-12-31 | 
| Christmas     | office | 2014-01-01 | 
+---------------+--------+------------+