list of all months

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

list of all months

query
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 ?

Reply | Threaded
Open this post in threaded view
|

Re: list of all months

Garrett Murphy

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: [hidden email] [mailto:[hidden email]] On Behalf Of query
Sent: Monday, March 08, 2010 5:25 AM
To: [hidden email]
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 ?

Reply | Threaded
Open this post in threaded view
|

Re: list of all months

Petru Ghita
In reply to this post by query
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
 
One approach could be:

You build a table with month information over which you are willing to
show data from another table.

Then you just cross join your data table and the data in your month table.

Here is some code I use for generating the table that holds all the
month I care about.


DROP TABLE  IF EXISTS tmp_lookup_months;
CREATE TABLE tmp_lookup_months
    (month_year_str varchar(7) NOT NULL,
     first_day_of_month DATE NOT NULL,
     month INTEGER NOT NULL,
     year INTEGER NOT NULL,
     PRIMARY KEY (first_day_of_month, year),
     
     CONSTRAINT valid_date
        CHECK (
           (EXTRACT (YEAR FROM first_day_of_month)::integer = year) AND
           (EXTRACT (MONTH FROM first_day_of_month)::integer = month) AND
           (EXTRACT (MONTH FROM first_day_of_month) > 0) AND
           (EXTRACT (MONTH FROM first_day_of_month) < 13) AND          
           (EXTRACT (DAY FROM first_day_of_month) = 01) AND
           (month_year_str) like (CASE WHEN month <= 9
            then
              cast (year::text ||'-0'|| month::text  as char(7))   
      
            else
             cast (year::text||'-'||  month::text  as char(7))      
           end)
           )       
    );


INSERT INTO  tmp_lookup_months
    select month_year_str, first_day_of_month, month, year from   
(
    select month, year,
           CASE WHEN month <= 9
            then
              cast (year::text ||'-0'|| month::text  as char(7))   
      
            else
              cast (year::text||'-'||  month::text  as char(7))    
      
           end as month_year_str,          
           cast (year::text||'-'||month||'-1' as date) as
first_day_of_month           
    from
    generate_series(1990, 2090) as year cross join
    generate_series(1,12) as month
    order by year, month
) as t1;


What is nice about this approach is that you can easily change the
granularity of the time over which you are willing to show the info so
you can create a second table with a trimester list for example.


Then say you have your data in a table called mydata.


select    

    ...
from mydata

CROSS JOIN tmp_lookup_months as ym where(
    ym.year >= $1 and  ym.year <= $2
   and my_intersection_function(start_date, end_date, ym.month, ym.year)>0
)
order by ...

So I'm assuming here that in the mydata table you have at least 4 columns:
id, start_date, end_date,  some_data.
some_data field probably only makes sense over the start_date to
end_date interval.
So in your select query you'll most likely need an aggregate function.


If you could provided a more complete description of what you are
trying to achive I might be able to further help.

Petru Ghita

On 08/03/2010 13:25, query 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 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://sigads.rediff.com/RealMedia/ads/click_nx.ads/www.rediffmail.com/signatureline.htm@Middle?>

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
 
iEYEARECAAYFAkueqZsACgkQt6IL6XzynQT+rgCguhFx6qzH3sgiti3O5zaqVQYS
ra4Anjz1C8hS5YC6jRVD9coV6j1AxpPv
=OoAd
-----END PGP SIGNATURE-----

Reply | Threaded
Open this post in threaded view
|

Re: list of all months

Dawid Kuroczko
In reply to this post by query
On Mon, Mar 8, 2010 at 13:25, query <[hidden email]> 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 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 ?

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 function will provide 0 in places where there is no
   data row, or value is NULL.

Best regards,
    Dawid

--
Sent via pgsql-sql mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply | Threaded
Open this post in threaded view
|

Re: list of all months

silly sad
In reply to this post by query
It looks like a procedural problem.
I would solve it in plpgsql.

--
Sent via pgsql-sql mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply | Threaded
Open this post in threaded view
|

Re: list of all months

Jasen Betts-5
In reply to this post by query
On 2010-03-08, query <[hidden email]> wrote:

> --=_484d28810a276e7b5e461f0328ee205f
> Content-Transfer-Encoding: 7bit
> Content-Type: text/plain; charset="UTF-8"
>
> 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 ?
>  

an outer join to (select FIRST_DAY_OF_MONTH + generate_series(1,DAYS_IN_MONTH) -1 )
where the date columns match

FIRST_DAY_OF_MONTH and DAYS_IN_MONTH are to be replaced (by you) with the
apropriate expressions by some means, possibly date arithmetic.




--
Sent via pgsql-sql mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql