From: Petru Ghita <petrutz@venaver.info>
To: query <search2006@rediffmail.com>
Subject: Re: list of all months
Date: Mon, 15 Mar 2010 22:41:47 +0100
Message-ID: <4B9EA99B.50301@venaver.info> (raw)
In-Reply-To: <20100308122523.50565.qmail@f5mail-237-209.rediffmail.com>
References: <20100308122523.50565.qmail@f5mail-237-209.rediffmail.com>
-----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 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: petrutz@venaver.info, search2006@rediffmail.com
Subject: Re: list of all months
In-Reply-To: <4B9EA99B.50301@venaver.info>
* 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