PostgreSQL aggregation with missing dates

Question

I'm trying this query

SELECT date_trunc('day', commit_at) AS day, count(*)
  FROM commits
  GROUP BY date_trunc('day', commit_at)
  ORDER BY date_trunc('day', commit_at) ASC;

and it return

      day         | count 
---------------------+-------
2015-05-18 00:00:00 |     5
2015-05-19 00:00:00 |     2
2015-05-21 00:00:00 |     2
(3 lignes)

The question is: How I can force empty days to be in the results ?

      day         | count 
---------------------+-------
2015-05-18 00:00:00 |     5
2015-05-19 00:00:00 |     2
2015-05-20 00:00:00 |     0
2015-05-21 00:00:00 |     2
(3 lignes)

SQL can't create data from nothing. You either have to have a system with dates, or use a method to create these dates to join or union in. for example a recursive CTE that returns all dates between a date range. Join back to this set so you get all dates regardless. Here's one way: stackoverflow.com/questions/13100445/… take a look at how @a_horse_with_no_name provided an answer or erwin's... — xQbert
– xQbert, Commented May 21, 2015 at 16:24
ok so the idea is to generate_series between first and last date then query theses dates ? I tried that but I can't have something working, if someone have an example — m4tm4t
– m4tm4t, Commented May 21, 2015 at 16:26
See link in first comment, examples are provided and yes, Erwin uses generate_series — xQbert
– xQbert, Commented May 21, 2015 at 16:27

Mureinik · Accepted Answer · 2018-02-26 12:36:46Z

5

One way of doing this is using nu generate_series to generate all the days in between the minimal and maximal date, and then join it to the aggregate query:

SELECT    DATE_TRUNC ('day', 
                      GENERATE_SERIES (MIN(commit_at), MAX(commit_at), '1 day') 
             AS day, 
          COALESCE (cnt, 0)
FROM      commits
LEFT JOIN (SELECT   DATE_TRUNC('day', commit_at) AS cday
           FROM     commits
           GROUP BY DATE_TRUNC('day', commit_at)) agg ON day = cday
ORDER BY  1 ASC

edited Feb 26, 2018 at 12:36

answered May 21, 2015 at 16:29

Mureinik

316k54 gold badges400 silver badges405 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL aggregation with missing dates

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related