Creating a calendar like table using crosstab in postgres

Question

I'm going through the Seven Databases in Seven Weeks book, and am sort of stuck on an extra work question.

The question is:

Build a pivot table that displays every day in a single month, where each week of the month is a row and each day name forms a column across the top (seven days, starting with Sunday and ending with Saturday) like a standard month calendar. Each day should contain a count of the number of events for that date or should remain blank if no event occurs.

The provided data was the following:

From that I created the following query for my first version:

SELECT * FROM crosstab(
    'SELECT extract(month from starts) as month
    ,extract(WEEK from starts) as week 
    ,extract(DOW from starts) as day
    ,count(*) FROM events GROUP BY month, week, day'
    ,'SELECT * FROM generate_series(0, 6)') 
AS 
    (month int
     ,week int
     ,Sunday int, Monday int, Tuesday int, Wednesday int
     ,Thursday int, Friday int, Saturday int) 
ORDER BY week

Which then created the following table:

There are 2 problems here, but I only want to focus on one.

I'm not sure why that the February 15th event shows up in week 6, when it should show up in week 7. So the proper version would have a 1 in the Tuesday (Valentine's day) and 1 in the Wednesday (for the Larp club event).

I wanted to try something out, and added another row for March 1st:

INSERT INTO events (title, starts, ends) VALUES
('March 1st', '2012-03-01 00:00:00', '2012-03-01 23:59:59');

Then the query returned the following table:

This is a little closer to correct, as it now puts the Wednesday the 15th after Tuesday the 14th, but of course there's now 2 rows, when they are in the same week.

So the problems that exist are:

1. Fix the floating Wednesday the 15th problem.
2. Get days in the same week to show up in the same row.

Answer 1

can you try change location week to be on top month Like:

SELECT * FROM crosstab(
    'SELECT 
    extract(WEEK from starts) as week 
    ,extract(month from starts) as month
    ,extract(DOW from starts) as day
    ,count(*) FROM events GROUP BY month, week, day'
    ,'SELECT * FROM generate_series(0, 6)') 
AS 
    (
     week int,month int
     ,Sunday int, Monday int, Tuesday int, Wednesday int
     ,Thursday int, Friday int, Saturday int) 
ORDER BY week