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 

Tags:

Sql

Postgresql