Friday, April 18, 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(calendar.StartDate) 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 
Add Comment

Name:

Email:

Website:

Message:


Latest Posts

  • 301 Redirect using htaccess file

    • Tue 15-Apr-14
      Further suggestion:
      RewriteRule ^(.*/)?assets/s 2dmain.html\?/( .*/)? $2 [R=301,L]
      Webmaster  
    • Thu 10-Apr-14
      Playing with some RegEx testers
      RewriteRule ^(.*\?/)?(?:$|( .+?)(?:(\.[^.]$ )|$)) $2 [R=301,L] ...
      Webmaster  
    • Wed 09-Apr-14
      I tried to redirect links from my old site to my new site, based on what I read in this thread. The old ...
      pelle
  • JComments 2.3.0 with ReCaptcha in Joomla 2.5.x

    • Fri 28-Mar-14
      You are a rockstar mate! thanks. Followed the steps listed and it worked! If only all tutorials were ...
      Kman
  • K2 Items disappear

    • Thu 03-Apr-14
      The fix works great, but the problem is occurring a couple of times a day. Any idea how to fix ...
      Larry C.