# 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;

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;

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 |
+---------------+--------+------------+

```