Printing Calendar -> SQL Solution.

Do you got any alternative Solution ?

Rahul Beniwal
4 min readJul 15, 2024

Welcome everyone! Today, in this blog, I am going to explore how we can print a calendar using SQL. Printing a calendar is a common exercise while learning any programming language.

Step 1 -> Generate All the Days in the Current Month

We can use the generate_series function to generate all the days in the current month. It works similarly to a for loop

select x.id from pg_catalog.generate_series(0, 31) x(id);
0
1
2
3
4
5
6
7
8
---
31

Remember, generate_series($1, $2) generates a series from $1 to $2.

Step 2 -> Generate Necessary Details Using generate_series

First, let’s see how we can extract values from the current date

select
current_date as cur_date,
to_char(current_date, 'mm') as month_number,
to_char(current_date, 'month') as month_name,
to_char(current_date, 'iw') as iso_week,
to_char(current_date, 'dd') as dm,
to_char(current_date, 'd') as week_date;

Explanation of format patterns:

  • iw: ISO week number. ISO weeks start on Monday and end on Sunday. The first week of the year contains the first Thursday.
  • dm: Day of the month as a number.
  • d: Day of the week as a number (1-7), where 1 corresponds to Sunday, 2 to Monday, and so on.
  cur_date  | month_number | month_name | iso_week | dm | week_date 
------------+--------------+------------+----------+----+-----------
2024-07-15 | 07 | july | 29 | 15 | 2

Let’s generate this information for all the dates in the current month

select 
cast(date_trunc('month', current_date) as date) + x.id,
to_char(cast(date_trunc('month', current_date) as date) + x.id, 'iw') as wk,
to_char(cast(date_trunc('month', current_date) as date) + x.id, 'dd') as dm,
cast(to_char(cast(date_trunc('month', current_date) as date) + x.id, 'd') as integer) as dw,
to_char(cast(date_trunc('month', current_date) as date) + x.id, 'mm') as cur_month
from generate_series(0, 31) x(id);
  ?column?  | wk | dm | dw | cur_month 
------------+----+----+----+-----------
2024-07-01 | 27 | 01 | 2 | 07
2024-07-02 | 27 | 02 | 3 | 07
2024-07-03 | 27 | 03 | 4 | 07
...
2024-07-31 | 31 | 31 | 4 | 07
2024-08-01 | 31 | 01 | 5 | 08

We need to remove the unwanted dates beyond the current month.

Final Step -> Group by Week

The calendar format shows that days should be grouped by week. Let’s achieve this incrementally

select 
x.wk
from (select
cast(date_trunc('month', current_date) as date) + x.id,
to_char(cast(date_trunc('month', current_date) as date) + x.id, 'iw') as wk,
to_char(cast(date_trunc('month', current_date) as date) + x.id, 'dd') as dm,
cast(to_char(cast(date_trunc('month', current_date) as date) + x.id, 'd') as integer) as dw,
to_char(cast(date_trunc('month', current_date) as date) + x.id, 'mm') as cur_month
from generate_series(0, 31) x(id)
) X
where to_char(current_date, 'mm')= cur_month
group by wk;
 wk 
----
27
28
29
30
31

But it does not look like calendar huh. To make it a calendar

select 
max(CASE dw WHEN 2 THEN dm END) AS Mo,
max(CASE dw WHEN 3 THEN dm END) AS Tu,
max(CASE dw WHEN 4 THEN dm END) AS Wed,
max(CASE dw WHEN 5 THEN dm END) AS Thu,
max(CASE dw WHEN 6 THEN dm END) AS Fri,
max(CASE dw WHEN 7 THEN dm END) AS Sat,
max(CASE dw WHEN 1 THEN dm END) AS Sun
from (select
cast(date_trunc('month', current_date) as date) + x.id,
to_char(cast(date_trunc('month', current_date) as date) + x.id, 'iw') as wk,
to_char(cast(date_trunc('month', current_date) as date) + x.id, 'dd') as dm,
cast(to_char(cast(date_trunc('month', current_date) as date) + x.id, 'd') as integer) as dw,
to_char(cast(date_trunc('month', current_date) as date) + x.id, 'mm') as cur_month
from generate_series(0, 31) x(id)
) X
where to_char(current_date, 'mm')= cur_month
group by wk
order by wk;
 mo | tu | wed | thu | fri | sat | sun 
----+----+-----+-----+-----+-----+-----
01 | 02 | 03 | 04 | 05 | 06 | 07
08 | 09 | 10 | 11 | 12 | 13 | 14
15 | 16 | 17 | 18 | 19 | 20 | 21
22 | 23 | 24 | 25 | 26 | 27 | 28
29 | 30 | 31 | | | |
  • select dm is invalid because we are doing group by so aggregate function must be there.
  • max(case dw when 2 then dm end) as Mo set label for weekdays. max is required to make this expression valid. It is like max([number]) = number

Conclusion

By grouping and ordering our data correctly, we can format the output to look like a traditional calendar. This approach demonstrates SQL’s capability to handle complex data transformations and presentations, making it a valuable tool for a wide range of data processing tasks.

So, next time you need to generate a calendar or similar output using SQL, remember that with a few creative queries, it’s entirely possible. Happy querying!

--

--

Rahul Beniwal
Rahul Beniwal

Written by Rahul Beniwal

I can help you master Python | Backend | System Design | Django | Rust | Computer Science | Databases | Making complex concepts clear and accessible with code

No responses yet