Re: list of all months

On Mon, Mar 8, 2010 at 13:25, query <search2006@rediffmail.com> wrote:
>
> 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
mightnot have any data at all. With normal query, we can display days only if data exists.But I want to display rows
forall days in a month with blank data for non-existing day in database. 
>
> How can this be achieved ?

Say, you have a table like:

CREATE TABLE some_data (  date date NOT NULL,  some_value int
);

Now, You would like to print values
from March 2010, even if there is no
entry for some days in such a table.

We need to have a list of all the days
in March.  We can do it with a query:

SELECT date '2010-03-01' + n AS date FROM generate_series(0, date '2010-04-01' - date'2010-03-01' - 1) AS x(n);

Sweet, we have dates, we just need a LEFT JOIN now:

SELECT date,      coalesce(value, 0) AS value  FROM some_data  RIGHT JOIN (    SELECT date '2010-03-01' + n AS date
FROM generate_series(0, date '2010-04-01' - date'2010-03-01' - 1) AS x(n)    ) AS dates USING (date); 

If you are running fairy recent PostgreSQL
it could be written even nicer:

WITH dates AS ( SELECT date '2010-03-01' + n AS date      FROM generate_series(0, date '2010-04-01' - date'2010-03-01'
-1) AS x(n) 
)
SELECT date,      coalesce(value, 0) AS value     FROM dates    LEFT JOIN some_data USING (date);


Two remarks:- it is fairy easy to create generate_series(date, date) function.  Give it a try - its fun! :)- coalesce
functionwill provide 0 in places where there is no  data row, or value is NULL. 

Best regards,   Dawid