Saturday, May 18, 2013
   
Text Size
Login

Generate Academic Calendar using MySQL

Print

Last Updated on Wednesday, 09 January 2013

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:



Human Check:

Security code
Refresh

Please type what you see in the image above:

Main Menu

Latest Posts

  • Joes Revolver Map (JRM)

    • Fri 17-May-13
      Hmmm... Sounds like a problem with the identifier. Was it working before and has there been a change ...
      Joel Lipman  
    • Fri 17-May-13
      Hello Joel: Yes, I do have it published on all pages of the site. I just went back to Revolver maps to ...
      Bill Duncan  
    • Fri 17-May-13
      Hi Bill, From the developers of RevolverMaps, "the module would need to be published on every page ...
      Joel Lipman  
    • Fri 17-May-13
      Hi Bill, I'll investigate further as you're not the first to say this happens. In the meantime, simply ...
      Joel Lipman  
    • Fri 17-May-13
      Its a great extension. But when I set it up I only show my presence on the 3D map and no other visitors ...
      Bill Duncan

Joes Word Cloud

Copyright © 1999-2013 Joel Lipman .Com

Restore Default Settings