From: Garrett Murphy <gmurphy@lawlogix.com>
To: pgsql-sql@postgresql.org
Subject: Re: list of all months
Date: Mon, 15 Mar 2010 17:16:31 -0400
Message-ID: <076DC33A3D38CE4BBC64D35DDD9DE70C0A2D9882@mse4be2.mse4.exchange.ms> (raw)
In-Reply-To: <20100308122523.50565.qmail@f5mail-237-209.rediffmail.com>
References: <20100308122523.50565.qmail@f5mail-237-209.rediffmail.com>
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@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of query
Sent: Monday, March 08, 2010 5:25 AM
To: pgsql-sql@postgresql.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-lyEH0fXkaokWf...;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gmurphy@lawlogix.com
Subject: Re: list of all months
In-Reply-To: <076DC33A3D38CE4BBC64D35DDD9DE70C0A2D9882@mse4be2.mse4.exchange.ms>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox