How to group by week in postgresql

Question

I've a database table commits with the following columns:

id | author_name | author_email | author_date (timestamp) | total_lines

Sample contents are:

1 | abc | [email protected] | 2013-03-24 15:32:49 | 1234 2 | abc | [email protected] | 2013-03-27 15:32:49 | 534 3 | abc | [email protected] | 2014-05-24 15:32:49 | 2344 4 | abc | [email protected] | 2014-05-28 15:32:49 | 7623 

I want to get a result as follows:

id | name | week | commits 1  | abc  | 1    | 2 2  | abc  | 2    | 0 

I searched online for similar solutions but couldnt get any helpful ones.

I tried this query:

SELECT      date_part('week', author_date::date) AS weekly,         COUNT(author_email)            FROM        commits GROUP BY    weekly ORDER BY weekly 

But its not the right result.

Answer 1

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; 

Answer 2

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