Saturday, October 25, 2014
   
Text Size
Login

Generate Academic Calendar using MySQL

What?
We have a specific timetabling system for academic institutions and all our staff/students follow academic week numbers as opposed to calendar week numbers.

Why?
The aim of this article is to quickly generate a calendar for a full academic year for referencing by staff/students.

How?
Let us assume that we have a system holding a calendar in the following table (called "joes_weekstructure"):

  1.  ID        AcademicSet       WeekNumber      StartDate  
  2.  --------- ----------------- --------------- ------------------ 
  3.  417       2012/2013         1               2012-07-16 
  4.  418       2012/2013         2               2012-07-23 
  5.  419       2012/2013         3               2012-07-30 
  6.  ... 
  7.  467       2012/2013         51              2013-07-01 
  8.  468       2012/2013         52              2013-07-08 

I'll use this table to create the calendar.

Query #1: Works from command-line but may generate "Empty Query 1065"

  1.  SELECT 
  2.       calendar.WeekNumber AS AcademicWeek, 
  3.       DATE_FORMAT(calendar.StartDate, '%e-%b-%y'AS Mon, 
  4.       DATE_FORMAT(DATE_ADD(calendar.StartDate, INTERVAL 1 DAY), '%e-%b-%y'AS Tue, 
  5.       DATE_FORMAT(DATE_ADD(calendar.StartDate, INTERVAL 2 DAY), '%e-%b-%y'AS Wed, 
  6.       DATE_FORMAT(DATE_ADD(calendar.StartDate, INTERVAL 3 DAY), '%e-%b-%y'AS Thu, 
  7.       DATE_FORMAT(DATE_ADD(calendar.StartDate, INTERVAL 4 DAY), '%e-%b-%y'AS Fri, 
  8.       WEEKOFYEAR(calendar.StartDate) AS CalendarWeek 
  9.  FROM 
  10.       joes_weekstructure calendar 
  11.  WHERE 
  12.       calendar.SetID=( 
  13.            SELECT acyear.SetID  
  14.            FROM joes_weekstructure acyear  
  15.            WHERE NOW() >= acyear.StartDate  
  16.            ORDER BY acyear.StartDate DESC  
  17.            LIMIT 0,1 
  18.       ) 
  19.  ORDER BY 
  20.       calendar.weekNumber 

Same Query but without using % (avoids MySQL Error 1065: Works within PHP scripts)

  1.  SELECT  
  2.        calendar.WeekNumber AS AcademicWeek,  
  3.        CONCAT(  
  4.             DAYOFMONTH(calendar.StartDate),   
  5.             '-',   
  6.             SUBSTR(MONTHNAME(calendar.StartDate), 1, 3),   
  7.             '-',   
  8.             YEAR(calendar.StartDate)  
  9.        ) AS Mon,  
  10.        CONCAT(  
  11.             DAYOFMONTH(DATE_ADD(calendar.StartDate, INTERVAL 1 DAY)),   
  12.             '-',   
  13.             SUBSTR(MONTHNAME(DATE_ADD(calendar.StartDate, INTERVAL 1 DAY)), 1, 3),   
  14.             '-',   
  15.             YEAR(DATE_ADD(calendar.StartDate, INTERVAL 1 DAY))  
  16.        ) AS Tue,  
  17.        CONCAT(  
  18.             DAYOFMONTH(DATE_ADD(calendar.StartDate, INTERVAL 2 DAY)),   
  19.             '-',   
  20.             SUBSTR(MONTHNAME(DATE_ADD(calendar.StartDate, INTERVAL 2 DAY)), 1, 3),   
  21.             '-',   
  22.             YEAR(DATE_ADD(calendar.StartDate, INTERVAL 2 DAY))  
  23.        ) AS Wed,  
  24.        CONCAT(  
  25.             DAYOFMONTH(DATE_ADD(calendar.StartDate, INTERVAL 3 DAY)),   
  26.             '-',   
  27.             SUBSTR(MONTHNAME(DATE_ADD(calendar.StartDate, INTERVAL 3 DAY)), 1, 3),   
  28.             '-',   
  29.             YEAR(DATE_ADD(calendar.StartDate, INTERVAL 3 DAY))  
  30.        ) AS Thu,  
  31.        CONCAT(  
  32.             DAYOFMONTH(DATE_ADD(calendar.StartDate, INTERVAL 4 DAY)),   
  33.             '-',   
  34.             SUBSTR(MONTHNAME(DATE_ADD(calendar.StartDate, INTERVAL 4 DAY)), 1, 3),   
  35.             '-',   
  36.             YEAR(DATE_ADD(calendar.StartDate, INTERVAL 4 DAY))  
  37.        ) AS Fri,  
  38.        WEEKOFYEAR(DATE_ADD(calendar.StartDate, INTERVAL 4 DAY)) AS CalendarWeek  
  39.   FROM  
  40.        joes_weekstructure calendar  
  41.   WHERE  
  42.        calendar.SetID=(  
  43.             SELECT acyear.SetID   
  44.             FROM joes_weekstructure acyear   
  45.             WHERE NOW() >= acyear.StartDate   
  46.             ORDER BY acyear.StartDate DESC   
  47.             LIMIT 0,1  
  48.        )  
  49.   ORDER BY  
  50.        calendar.weekNumber 
For those of you who are familiar with this, I am simply using an alternative to the DATE_FORMAT() function in mySQL.

Should yield:

  1.  AcademicWeek   Mon         Tue         Wed         Thu         Fri         CalendarWeek 
  2.  -------------- ----------- ----------- ----------- ----------- ----------- --------------- 
  3.  1              16-Jul-2012 17-Jul-2012 18-Jul-2012 19-Jul-2012 20-Jul-2012 29 
  4.  2              23-Jul-2012 24-Jul-2012 25-Jul-2012 26-Jul-2012 27-Jul-2012 30 
  5.  3              30-Jul-2012 31-Jul-2012 1-Aug-2012  2-Aug-2012  3-Aug-2012  31 
  6.  ... 
  7.  51             1-Jul-2013  2-Jul-2013  3-Jul-2013  4-Jul-2013  5-Jul-2015  27 
  8.  52            8-Jul-2013  9-Jul-2013  10-Jul-2013 11-Jul-2013 12-Jul-2015 28 


This query in T-SQL (SQL Server 2008 R2) for CMIS Facility

  1.  SELECT                
  2.       calendar.WeekNumber AS AcademicWeek,           
  3.       CAST(DATEPART(dd, calendar.StartDate) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, calendar.StartDate), 1, 3) + '-' + CAST(DATEPART(yyyy, calendar.StartDate) AS VARCHAR(4)) AS Monday,           
  4.       CAST(DATEPART(dd, DATEADD(d, 1, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 1, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 1, calendar.StartDate)) AS VARCHAR(4)) AS Tuesday,           
  5.       CAST(DATEPART(dd, DATEADD(d, 2, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 2, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 2, calendar.StartDate)) AS VARCHAR(4)) AS Wednesday,           
  6.       CAST(DATEPART(dd, DATEADD(d, 3, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 3, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 3, calendar.StartDate)) AS VARCHAR(4)) AS Thursday,           
  7.       CAST(DATEPART(dd, DATEADD(d, 4, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 4, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 4, calendar.StartDate)) AS VARCHAR(4)) AS Friday,           
  8.       CAST(DATEPART(dd, DATEADD(d, 5, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 5, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 5, calendar.StartDate)) AS VARCHAR(4)) AS Saturday,           
  9.       CAST(DATEPART(dd, DATEADD(d, 6, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 6, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 6, calendar.StartDate)) AS VARCHAR(4)) AS Sunday,           
  10.       DATEPART(wk, CAST(DATEPART(dd, DATEADD(d, 6, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 6, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 6, calendar.StartDate)) AS VARCHAR(4))) AS CalendarWeek           
  11.  FROM                
  12.       cmisuser.WEEKSTRUCTURE calendar      
  13.  /* specific to our calendars       
  14.  WHERE                
  15.       calendar.SetID=(           
  16.            SELECT     TOP 1 acyear.SetID  
  17.            FROM     cmisuser.WEEKSTRUCTURE acyear  
  18.            WHERE     acyear.StartDate >= GETDATE() 
  19.            ORDER BY acyear.StartDate DESC)      
  20.  */ 
  21.  ORDER BY                
  22.       calendar.weekNumber 
Add Comment

Name:

Email:

Website:

Message:


Latest Posts