How to group by week in postgresql
SqlPostgresqlSql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
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;
Solution 2 - Sql
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