Re: list of all months - Mailing list pgsql-sql

I recently ran into the same issue and I resolved it by generating a table of nothing but months for the last 5 years:

select  TO_CHAR((current_date - interval '1 month' * a),'YYYY-MM') AS mmyyyy FROM generate_series(1,60,1) AS s(a)

"2010-02"

"2010-01"

"2009-12"

"2009-11"

"2009-10"

…

Then I did a join on this generated series:

SELECT months.mmyyyy

                ,COUNT(foo_key)

from      (

                select TO_CHAR((current_date - interval '1 month' * a),'YYYY-MM') as mmyyyy

                from generate_series(1,60,1) AS s(a)

                ) months

                LEFT OUTER JOIN foo

                                ON months.mmyyyy=to_char(foo_date_created,'YYYY-MM')

GROUP BY months.mmyyyy

I’m sure you can adapt this to your needs.

Garrett Murphy

Hi,

I want to display data for all days in a month even if no data exists for that month. Some of the days in a month might not have any data at all. With normal query, we can display days only if data exists.But I want to display rows for all days in a month with blank data for non-existing day in database.

How can this be achieved ?