PostgreSQL: COUNT 0 with Generate_Series()

Question

I'm trying to count records for the past 7 days including those without any records or 0. This is my current query.

WITH calendar as (
SELECT  d
FROM generate_series(date_trunc('day',CURRENT_DATE - '7 day'::interval - '7 hour'::interval),date_trunc('day', CURRENT_DATE - INTERVAL '7 hour'), '1 day'::interval) d 
)

SELECT 
        COUNT(mc.id), 
        mc.name AS ord_name, 
        c.d::date AS ord_date
    FROM test_table mc
    LEFT JOIN calendar c
    ON c.d = mc.occured_at::date
    WHERE date_trunc('day', occured_at - interval '7 hour') > 
    (CURRENT_DATE + INTERVAL '7 hour') - INTERVAL '7 days'
    GROUP BY 
        name, 
        c.d
    ORDER BY 
        c.d;

Result of my query with DB Fiddle Link

So I'm using generate_series() to get the dates that I want. I subtract 7 hours because technically a day will start at 7 AM and would end at 6:59 AM of the following day. I use LEFT JOIN to compare the date that I got from the calendar and my table's date.

Sample Data: test_table


| id  |  name  |       occured_at     |
|-----|--------|----------------------|
| 1   |  ord1  |2019-02-23 07:00:00+00|
| 2   |  ord2  |2019-02-23 12:30:00+00|
| 3   |  ord1  |2019-02-24 06:58:00+00|
| 4   |  ord2  |2019-02-25 07:00:00+00|
| 5   |  ord2  |2019-02-25 07:01:00+00|
| 6   |  ord1  |2019-02-26 06:59:00+00|
| 7   |  ord1  |2019-02-26 07:00:00+00|
| 8   |  ord1  |2019-02-26 12:30:00+00|
| 9   |  ord2  |2019-02-27 06:58:00+00|
| 10  |  ord1  |2019-02-28 07:01:00+00|
| 11  |  ord1  |2019-02-28 07:00:00+00|
| 12  |  ord1  |2019-03-01 06:59:00+00|

Expected Result:

|count |ord_name |ord_date  |
|------|---------|----------|
| 1    |  ord1   |2019-02-23|
| 2    |  ord2   |2019-02-23|
| 0    |  ord1   |2019-02-24|
| 0    |  ord2   |2019-02-24|
| 1    |  ord1   |2019-02-25|
| 2    |  ord2   |2019-02-25|
| 2    |  ord1   |2019-02-26|
| 1    |  ord2   |2019-02-26|
| 0    |  ord1   |2019-02-27|
| 0    |  ord2   |2019-02-27|
| 3    |  ord1   |2019-02-28|
| 0    |  ord2   |2019-02-28|
| 0    |  ord1   |2019-03-01|
| 0    |  ord2   |2019-03-01|

Gordon Linoff · Accepted Answer · 2019-03-01 20:26:18Z

1

Use a cross join to generate all the rows and then left join to bring in the rows with matching values:

WITH calendar as (
      SELECT d
      FROM generate_series(date_trunc('day', CURRENT_DATE - '7 day'::interval - '7 hour'::interval),
                           date_trunc('day', CURRENT_DATE - INTERVAL '7 hour'),
                          '1 day'::interval
                          ) d 
     )

SELECT n.name AS ord_name, 
       c.d::date AS ord_date
       COUNT(mc.id), 
FROM (SELECT DISTINCT mc.name test_table mc) n CROSS JOIN
     calendar c LEFT JOIN
     test_table mc
     ON mc.occured_at >= c.d - interval '7 hour' and
        mc.occured_at < c.d + interval '1 day' - interval '7 hour'
GROUP BY n.name, c.d
ORDER BY c.d, n.name;

edited Mar 1, 2019 at 20:26

answered Mar 1, 2019 at 19:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Paolo Over a year ago

Hey Gordon, Thanks for your answer. All of the dates are now showing, my only concern is that the query would count the records in a day 12 AM - 11:59 PM (Same day). Would there be any possiblity to adjust this timeframe to 7 AM - 6:59AM of the next day?

Gordon Linoff Over a year ago

@Paolo . . . You can fix that with date arithmetic on the comparison.

Zaynul Abadin Tuhin · Accepted Answer · 2019-03-01 18:46:18Z

0

use calendar table in left btw order is keyword so it is better to not use any keyword as a table name as you already used so use double quote

WITH calendar as (
SELECT  d
FROM generate_series(date_trunc('day',CURRENT_DATE - '7 day'::interval - '7 hour'::interval),date_trunc('day', CURRENT_DATE - INTERVAL '7 hour'), '1 day'::interval) d 
)

SELECT 
        COUNT(mc.id), 
        mc.name AS ord_name, 
        c.d::date AS ord_date
    FROM 
      calendar c LEFT JOIN 
      "order" mc  
    ON c.d = mc.occured_at::date
    WHERE date_trunc('day', occured_at - interval '7 hour') > 
    (CURRENT_DATE + INTERVAL '7 hour') - INTERVAL '7 days'
    GROUP BY 
        name, 
        c.d
    ORDER BY 
        c.d;

answered Mar 1, 2019 at 18:46

Zaynul Abadin Tuhin

32.1k6 gold badges38 silver badges67 bronze badges

3 Comments

Paolo Over a year ago

Thanks for your answer Zaynul, apparently I've already tried to do that but I'm still missing some dates in between.

Zaynul Abadin Tuhin Over a year ago

@Paolo which date data you are missing

Paolo Over a year ago

I've edited my question and added a DB Fiddle link with the sample data in it.

Collectives™ on Stack Overflow

PostgreSQL: COUNT 0 with Generate_Series()

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related