PostgreSQL: count all grouped by month including gaps

I have a pretty simple query to do - a table has a date field and I need to select count of all rows, grouped by month including gaps.

I have the following query that works for days, but it does not work for month

SELECT f.date::date as period, count(e.id) as count
FROM generate_series((now() - interval '1 year')::date, now()::date, '1 month'::interval) AS f(date)
LEFT JOIN my_table AS e ON e.created_at::date = f.date::date
GROUP BY f.date
ORDER BY f.date DESC;

For some reason this query returns zero's for all rows.

This query works, but it does not fill in gaps

SELECT date_trunc('month', created_at) AS txn_month, count(*) as count
FROM my_table
GROUP BY txn_month

What is the right way to do so?

Solution

The following query returns the number of rows in my_table for each period, including periods without corresponding data in my_table.

WITH agg_periods AS (
  SELECT s.period_start, s.period_start + p.agg_interval AS period_end
    FROM (SELECT 'P1M'::interval AS agg_interval) p
   CROSS JOIN generate_series(date_trunc('month', now()) - 'P1Y'::interval, now(), p.agg_interval) s(period_start)
)
SELECT f.period_start AS period, count(e.id) AS COUNT
  FROM agg_periods f
  LEFT JOIN my_table e
    ON f.period_start <= e.created_at
       AND e.created_at < f.period_end
 GROUP BY f.period_start
 ORDER BY f.period_start;

The query is structured so that different periods can be accomodated by changing the period generation parameters in the subquery, agg_periods. Because created_at is used without type casts in the comparisons, PostgreSQL isn't prevented from using indexes on that column.