CODEWITHSUNDEEP
postgresql
Rabin
Rabin

Reputation: 1583

Date range in PostgreSQL

When I apply a date range to my query, is there anyway to display the dates used in the date range even if there is no data at those dates?

Suppose I use, 

... where date between '1/12/2010' and '31/12/2010' order by date

What I want in my result is to show sum of all amount column until 1/12/2010 on that day even if there is no data for that date and also same for 31/12/2010.

Upvotes: 15

Views: 8617

Answers (1)

Frank Heikens
Frank Heikens

Reputation: 126991

Join with generate_series() to fill in the gaps.

Example:

CREATE TEMP TABLE foo AS SELECT CURRENT_DATE AS today;
SELECT
    COUNT(foo.*),
    generate_series::date
FROM
    foo
        RIGHT JOIN generate_series('2010-12-18', '2010-12-25', interval '1 day') ON generate_series = today
GROUP BY
    generate_series;

Result:

0,'2010-12-18'
0,'2010-12-19'
1,'2010-12-20'
0,'2010-12-21'
0,'2010-12-22'
0,'2010-12-23'
0,'2010-12-24'
0,'2010-12-25'

Upvotes: 27

Related Questions