More term dates

Here are two functions/procedures to use together with the provisional term dates functions I posted in:

http://www.cs.ox.ac.uk/blogs/edward-crichton/2012/05/30/full-term-dates/

for converting to and from Oxford term dates.


# get the date for: a term and a week and a day of week, with 1..7 being sun-sat. (2..6 = mon-fri)
# e.g. you can get Thursday of week 0 Michaelmas term with: SELECT getDateForTermWeekDay(2012,'MT',0,5);

DROP FUNCTION IF EXISTS getDateForTermWeekDay;

delimiter //

create function getDateForTermWeekDay(year int(5),term char(2),weekInTerm int(2),dayOfWeek int(1)) RETURNS date
main:BEGIN
	declare termStart date;
	
	IF term NOT IN ('MT','HT','TT')
	THEN
		RETURN NULL;
	END IF;
	
	IF term='MT'
	THEN
		SET termStart=michaelmas(year);
	END IF;
	
	IF term='HT'
	THEN
		SET termStart=hilary(year);
	END IF;
	
	IF term='TT'
	THEN
		SET termStart=trinity(year);
	END IF;
	
	RETURN DATE_ADD(DATE_ADD(termStart, INTERVAL (weekInTerm-1) WEEK), INTERVAL (dayOfWeek-1) DAY);

END//

delimiter ;

DROP PROCEDURE IF EXISTS getTermWeekDayForDate;

delimiter //

# returns year, term, week and day, with week starting from week '-1' onwards.
# e.g. call getTermWeekDayForDate('2012-10-04');

create procedure getTermWeekDayForDate(IN date_in date)
main:BEGIN
	
	DECLARE Y int(5);
	DECLARE MT date;
	DECLARE HT date;
	DECLARE TT date;
	DECLARE term char(2);
	DECLARE relative date;
	DECLARE WEEK_DEC decimal(5,3);
	DECLARE WEEK int(2);
		
	SET Y=EXTRACT(YEAR FROM date_in);
	
	SET MT=michaelmas(Y);
	SET HT=hilary(Y);
	SET TT=trinity(Y);
	
	IF date_in >=DATE_SUB(MT,INTERVAL 2 WEEK)
	THEN
		SET term='MT';
		SET relative=MT;
	ELSEIF date_in >=DATE_SUB(TT,INTERVAL 2 WEEK)
	THEN
		SET term='TT';
		SET relative=TT;
	
	
	ELSEIF date_in >=DATE_SUB(HT,INTERVAL 2 WEEK)
	THEN
		SET term='HT';
		SET relative=HT;
	
	END IF;
	
	SET WEEK_DEC=FLOOR((((DATEDIFF(date_in,DATE_SUB(relative,INTERVAL 2 WEEK)))-14)/7))+1;
	SET WEEK=WEEK_DEC;
	
	SELECT Y AS Year,term AS Term,WEEK AS Week, DAYOFWEEK(date_in) AS Day;
END//

delimiter ;
mysql> SELECT getDateForTermWeekDay(2012,'MT',0,5);
+--------------------------------------+
| getDateForTermWeekDay(2012,'MT',0,5) |
+--------------------------------------+
| 2012-10-04                           | 
+--------------------------------------+

mysql> call getTermWeekDayForDate('2012-10-04');
+------+------+------+------+
| Year | Term | Week | Day  |
+------+------+------+------+
| 2012 | MT   |    0 |    5 | 
+------+------+------+------+

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