Hack 87. Generate a Calendar

You can draw a calendar view using SQL.

Suppose you have a list of important dates that should be displayed in a calendar format. You need to show the month of March in the year 2007, with weekdays from Sunday through Saturday as column headings.

Here is the list of days:

mysql> SELECT * FROM saints;
+------------+--------------+
| d | name |
+------------+--------------+
| 2007-03-08 | St. John |
| 2007-03-09 | St. Francis |
| 2007-03-17 | St. Patrick |
| 2007-03-19 | St. Joseph |
| 2007-03-23 | St. Turibius |
+------------+--------------+

We will make up a VIEW to hold the value of the first day of the month to be displayed:

mysql> CREATE VIEW dayOne AS SELECT DATE '2007-03-01' AS first;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM dayOne;
+------------+
| first |
+------------+
| 2007-03-01 |
+------------+

You also need a table containing a list of dates that includes every day of the month to be displayed. "Generate Sequential or Missing Data" [Hack #82] shows you how to fill a suitable table:

mysql> SELECT * FROM cal WHERE d>= DATE '2007-03-01';
+------------+
| d |
+------------+
| 2007-03-01 |
| 2007-03-02 |
| 2007-03-03 |
| 2007-03-04 |

The weekBeginning view has one row for every week of the month. Each week has an offset relative to the first of the month. You can get that by subtracting the DAYOFWEEK from the DAYOFMONTH:

mysql> CREATE VIEW weekBeginning AS
 -> SELECT DAYOFMONTH(d) - DAYOFWEEK(d) AS wk
 -> FROM cal JOIN dayOne
 -> ON (MONTH(d) = MONTH(first))
 -> GROUP BY wk;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM weekBeginning;
+------+
| wk |
+------+
| -4 |
| 3 |
| 10 |
| 17 |
| 24 |
+------+

The first row has an offset of 4 because the date '2007-03-01' is a Thursday. Thursday has a DAYOFWEEK value of 5. The day of the month is, of course, 1, so 1 5 gives 4.

For each week, you can add the days of the week as columns:

mysql> CREATE VIEW calGrid AS
 -> SELECT wk, first + INTERVAL wk + 0 DAY AS Sun
 -> , first + INTERVAL wk + 1 DAY AS Mon
 -> , first + INTERVAL wk + 2 DAY AS Tue
 -> , first + INTERVAL wk + 3 DAY AS Wed
 -> , first + INTERVAL wk + 4 DAY AS Thu
 -> , first + INTERVAL wk + 5 DAY AS Fri
 -> , first + INTERVAL wk + 6 DAY AS Sat
 -> FROM weekBeginning CROSS JOIN dayOne;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM calGrid;
+------+------------+------------+------------+------------+-----------...
| wk | Sun | Mon | Tue | Wed | Thu 
+------+------------+------------+------------+------------+-----------
| -4 | 2007-02-25 | 2007-02-26 | 2007-02-27 | 2007-02-28 | 2007-03-01
| 3 | 2007-03-04 | 2007-03-05 | 2007-03-06 | 2007-03-07 | 2007-03-08
| 10 | 2007-03-11 | 2007-03-12 | 2007-03-13 | 2007-03-14 | 2007-03-15
| 17 | 2007-03-18 | 2007-03-19 | 2007-03-20 | 2007-03-21 | 2007-03-22
| 24 | 2007-03-25 | 2007-03-26 | 2007-03-27 | 2007-03-28 | 2007-03-29
+------+------------+------------+------------+------------+-----------...

We have truncated the output shownthe columns actually include columns for Fri and Sat.

With the grid established, you can add the data that you want to display. In this example, the day of the month is shown for most days. Where the date has a particular significance the name of the saint is included:

mysql> SELECT
 -> COALESCE((SELECT name FROM saints WHERE d=Sun),DAYOFMONTH(Sun)) 
 -> AS Sun
 -> ,COALESCE((SELECT name FROM saints WHERE d=Mon),DAYOFMONTH(Mon)) 
 -> AS Mon
 -> ,COALESCE((SELECT name FROM saints WHERE d=Tue),DAYOFMONTH(Tue)) 
 -> AS Tue
 -> ,COALESCE((SELECT name FROM saints WHERE d=Wed),DAYOFMONTH(Wed)) 
 -> AS Wed
 -> ,COALESCE((SELECT name FROM saints WHERE d=Thu),DAYOFMONTH(Thu)) 
 -> AS Thu
 -> ,COALESCE((SELECT name FROM saints WHERE d=Fri),DAYOFMONTH(Fri)) 
 -> AS Fri
 -> ,COALESCE((SELECT name FROM saints WHERE d=Sat),DAYOFMONTH(Sat)) 
 -> AS Sat
 -> FROM calGrid
 -> ORDER BY wk;
+------+------------+------+------+----------+--------------+-------------+
| Sun | Mon | Tue | Wed | Thu | Fri | Sat |
+------+------------+------+------+----------+--------------+-------------+
| 25 | 26 | 27 | 28 | 1 | 2 | 3 |
| 4 | 5 | 6 | 7 | St. John | St. Francis | 10 |
| 11 | 12 | 13 | 14 | 15 | 16 | St. Patrick |
| 18 | St. Joseph | 20 | 21 | 22 | St. Turibius | 24 |
| 25 | 26 | 27 | 28 | 29 | 30 | 31 |
+------+------------+------+------+----------+--------------+-------------+

For each day, the saints table is referenced; where there is no matching row a NULL value is returned. The day of the month replaces the NULL.

10.11.1. Variations

The example shown works with MySQL. Unfortunately, there is little agreement among the vendors regarding date functions, and the SQL standard is missing the vital DAYOFWEEK function. However, SQL Server, Oracle, and PostgreSQL will run this hack with a few changes.

10.11.1.1. SQL Server

To add three days to the date wk in SQL Server you can use DateAdd(d, 3, wk). To extract the day of the week of date whn use DatePart(dw, d). Similarly, you can get the day of the month with DatePart(d, whn).

10.11.1.2. Oracle

To add three days to the date wk in Oracle you can use wk + 3. To extract the day of the week of date whn use TO_CHAR(whn, 'd'). Similarly, you can get the day of the month with TO_CHAR(whn, 'dd'). In both cases, the resulting string will implicitly be cast back to an integer as required, but you can use CAST(TO_CHAR(dw, 'd') AS INT) to force the cast.

10.11.1.3. PostgreSQL

To add three days to the date wk in PostgreSQL you can use wk+3. To extract the day of the week of date whn you use EXTRACT(dow FROM whn). Similarly, you can get the day of the month with EXTRACT(DAY FROM whn).