POSTGRES DATA_TRUNC should return 0 for intervals that has no data-CodePudding

I am trying to do a time series-like reporting, for that, I am using the Postgres DATA_TRUNC function, it works fine and I am getting the expected output, but when a specific interval has no record then it is getting skipped to show, but my expected output is to get the interval also with 0 as the count, below is the query that I have right now. What change I should do to get the intervals that have no data? Thanks in advance.

SELECT date_trunc('days', sent_at), count('*')  
FROM (select * from invoice 
WHERE supplier = 'ABC' and sent_at BETWEEN '2021-12-01' AND '2022-07-31') as inv 
GROUP BY date_trunc('days', sent_at)
ORDER BY date_trunc('days', sent_at);

Expected: As you can see below, the current output now shows 02/12 and then 07/12, it has skipped dates in the middle, but for me, it should also show 03/12, 04/12, 05/12 with count as 0

Current output

CodePudding user response：

It doesn't seem like you have those dates in your data, in which case you need to generate them. Also, casting your timestamp to date instead of date_trunc() can get rid of those zeroes.

SELECT dates::date, count(*)  filter (where sent_at is not null)
FROM (
  select * 
  from invoice a 
    right join generate_series( '2021-12-01'::date, 
                                '2021-12-31'::date, 
                                '1 day'::interval ) as b(dates)
      on sent_at::date=b.dates) as inv 
GROUP BY 1
ORDER BY 1;

Here's a working example. Also, please try to improve your question according to @nbk's comment.