Re: list of all months

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

From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of query
Sent: Monday, March 08, 2010 5:25 AM
To: pgsql-sql(at)postgresql(dot)org
Subject: [SQL] list of all months

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 ?

<http://portal.mxlogic.com/redir/?atT74QSkQkjqtSkXI6zB5xAS03F8zG5ezW4DnunM5_FUTW4JPkapo-lyEH0fXkaokWfEitt4-nOAycdCT7PVg_w20ErAwwvV8TvAnXLcIELTKrKrm8Q5zOZ1oYLkN3Uw1lpp76zBc5blFcz7W2N_00jr5NPVJ5dNVNVNAsUrjodCBIo0saCBQQg7OFcQgltd46DDCy1mI9AW6V-7PM76Qjq9JeXb3bUVcQsTdxGcl-Vx8li0S>