How to group by week in postgresql
If you have multiple years, you should take the year into account as well. One way is:
SELECT date_part('year', author_date::date) as year,
date_part('week', author_date::date) AS weekly,
COUNT(author_email)
FROM commits
GROUP BY year, weekly
ORDER BY year, weekly;
A more natural way to write this uses date_trunc()
:
SELECT date_trunc('week', author_date::date) AS weekly,
COUNT(author_email)
FROM commits
GROUP BY weekly
ORDER BY weekly;
If you want the count of all the intermediate weeks as well where there are no commits/records, you can get it by providing a start_date
and end_date
to generate_series()
function
SELECT t1.year_week week,
t2.commit_count
FROM (SELECT week,
To_char(week, 'IYYY-IW') year_week
FROM generate_series('2020-02-01 06:06:51.25+00'::DATE,
'2020-04-05 12:12:33.25+00'::
DATE, '1 week'::interval) AS week) t1
LEFT OUTER JOIN (SELECT To_char(author_date, 'IYYY-IW') year_week,
COUNT(author_email) commit_count
FROM commits
GROUP BY year_week) t2
ON t1.year_week = t2.year_week;
The output will be:
week | commit_count
----------+-------------
2020-05 | 2
2020-06 | NULL
2020-07 | 1