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

Full term dates

I’ve just fallen off my chair. I’ve managed to calculate the dates of full term programmatically. No more keeping tables of dates, or at least no more entering them.

I should share this so, here are the stored functions for mysql:

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

# term dates
# "Michaelmas, Term shall begin on and include 1 October and end on and include 17 December."
# Full term actually appears to be the first Sunday after the first Monday
# find the first Monday in October and add 6 days.


DROP FUNCTION IF EXISTS michaelmas;

delimiter //

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


	IF (2-DAYOFWEEK(CONCAT(Y,'-10-01')))>=0
	THEN
		RETURN DATE_ADD(DATE_ADD( CONCAT(Y,'-10-01'), INTERVAL ( 2- DAYOFWEEK(  CONCAT(Y,'-10-01') ) ) DAY ), INTERVAL 6 DAY);
	ELSE
		RETURN DATE_ADD(DATE_ADD(CONCAT(Y,'-10-01'), INTERVAL ( 9 - DAYOFWEEK(CONCAT(Y,'-10-01') ) ) DAY), INTERVAL 6 DAY);
	END IF;
	
END//

delimiter ;

# "Hilary, Term shall begin on and include 7 January and end on and include 25 March or the Saturday before Palm Sunday, whichever is the earlier."
# Full term actually appears to be the first Sunday after the first Monday after 7th January

DROP FUNCTION IF EXISTS hilary;

delimiter //

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


	IF (2-DAYOFWEEK(CONCAT(Y,'-01-07')))>=0
	THEN
		RETURN DATE_ADD(DATE_ADD( CONCAT(Y,'-01-07'), INTERVAL ( 2- DAYOFWEEK(  CONCAT(Y,'-01-07') ) ) DAY ), INTERVAL 6 DAY);
	ELSE
		RETURN DATE_ADD(DATE_ADD(CONCAT(Y,'-01-07'), INTERVAL ( 9 - DAYOFWEEK(CONCAT(Y,'-01-07') ) ) DAY), INTERVAL 6 DAY);
	END IF;
	
END//

delimiter ;

# "Trinity, Term shall begin on and include 20 April or the Wednesday after Easter, whichever is the later, and end on and include 6 July."
# Full term actually appears to be the first Sunday after the latest of (20th April or Wednesday after Easter)

DROP FUNCTION IF EXISTS trinity;

delimiter //

create function trinity(Y int(5))
	RETURNS date
	
main:BEGIN
	DECLARE latest date;
	DECLARE twentyTh date;
	
	SET twentyTh=CONCAT(Y,'-04-20');
	
	# Wednesday after Easter
	SET latest=DATE_ADD(easter(Y), INTERVAL 3 DAY);
	
	IF latest < twentyTh
	THEN
	
		SET latest=twentyTh;
	
	END IF;

	IF (1-DAYOFWEEK(latest))>=0
	THEN
		RETURN DATE_ADD( latest, INTERVAL ( 1- DAYOFWEEK(  latest ) ) DAY );
	ELSE
		RETURN DATE_ADD(latest, INTERVAL ( 8 - DAYOFWEEK(latest ) ) DAY);
	END IF;
	
END//

delimiter ;

So if I want the term dates for the academic year starting MT2012 I could query:

SELECT michaelmas(2012),hilary(2013),trinity(2013);
+------------------+--------------+---------------+
| michaelmas(2012) | hilary(2013) | trinity(2013) |
+------------------+--------------+---------------+
| 2012-10-07       | 2013-01-13   | 2013-04-21    | 
+------------------+--------------+---------------+

Officially (which this is not), the dates for full term are prescribed by Council and then published by the Registrar to the University Gazette, and they may be different from the provisional dates. But it seems to be infrequent that the provisional dates are changed. Which, at least, makes this useful for entering dates in advance.