Intervals not showing when applying where clause

Question

My table is something like this :

CREATE TABLE issues 
(
    id         INT,
    status     TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

And I get intervals of every day in month and check if there is any issue created on that date. if Issue is created on that day I get count of other wise interval gives 0 count .In short I get 30 intervals , interval of every day with count . My query is like this

select gs.ts, count(r.created_at)
from generate_series(now() - interval '1 month', now() - interval '1 day', interval '1 day') gs(ts) left join
     issues r 
     on r.created_at >= gs.ts and
        r.created_at < gs.ts + interval '1 day'
group by gs.ts
order by gs.ts;

Now there is a issue when I apply a WHERE clause I don't get 30 intervals but just a interval which is of that issue type . I am applying where clause like this

select gs.ts, count(r.created_at)
from generate_series(now() - interval '1 month', now() - interval '1 day', interval '1 day') gs(ts) left join
     issues r 
     on r.created_at >= gs.ts and
        r.created_at < gs.ts + interval '1 day'
WHERE issue = 'NEW'
group by gs.ts
order by gs.ts;

Now will return only interval on which issue type NEW exist. while I want all intervals (30 intervals) with count 0 if there is no issue created with that issue status.

Since your doing left join but your placing the issue in the where clause, so by default it changes your left join to INNER JOIN so only records in your issues table will be returned. You can move the issue = 'NEW' to your join ON clause. — Brad, Apr 05 '21 at 16:49

score 1 · Accepted Answer · answered Apr 05 '21 at 16:49

You need to move the filtering condition to the ON clause because you are using a LEFT JOIN:

select gs.ts, count(r.created_at)
from generate_series(now() - interval '1 month', now() - interval '1 day', interval '1 day') gs(ts) left join
     issues r 
     on r.created_at >= gs.ts and
        r.created_at < gs.ts + interval '1 day' and
        r.issue = 'NEW'
group by gs.ts
order by gs.ts;

Intervals not showing when applying where clause

1 Answers1