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.

How is it incorrect? What is the problem?

Gordon Linoff
– Gordon Linoff

2016-03-16 00:25:17 +00:00
Commented Mar 16, 2016 at 0:25 — Gordon Linoff
– Gordon Linoff, Commented Mar 16, 2016 at 0:25

Gordon Linoff · Accepted Answer · 2016-03-16 00:26:07Z

144

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;

answered Mar 16, 2016 at 0:26

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Jilson Thomas Over a year ago

@GordonLinoff : How can we include zero commit weeks also in this query? I tested this one and it gives all rows with at least one commit.

Gordon Linoff Over a year ago

@JilsonThomas . . . If you have a question, it is better to ask as a question rather than in a comment.

Henry Heikkinen Over a year ago

@GordonLinoff Grouping by date_part('year', x), date_part('week', x) is most likely not what the user wants and should be considered incorrect. For example the first day of 2017 is still part of week 52 that started in 2016 and therefore the (2017, 52) pair is probably not at all what you want as it sorts to the end of 2017. Using ´date_trunc('week', x)` on the other hand works as one would expect.

Chopra Over a year ago

@GordonLinoff I am getting this error - django.db.utils.ProgrammingError: function date_trunc(unknown, unknown) is not unique and HINT: Could not choose a best candidate function. You might need to add explicit type casts. I am using Django and PostgreSQL.

Tom Söderlund Over a year ago

Note: date_part formats the week as week number (probably more desirable), whereas date_trunc gives a date.

|

saintlyzero · Accepted Answer · 2022-02-08 01:01:40Z

26

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

edited Feb 8, 2022 at 1:01

answered Apr 13, 2020 at 11:15

saintlyzero

1,8502 gold badges21 silver badges28 bronze badges

1 Comment

StonedTensor Over a year ago

Worth noting you can COALESCE(commit_count,0), which usually presents the analysis more cleanly and makes numeric calculations easier

Collectives™ on Stack Overflow

How to group by week in postgresql

2 Answers 2

7 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related