pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedlist of all months
6+ messages / 6 participants
[nested] [flat]
* list of all months
@ 2010-03-08 12:25 query <search2006@rediffmail.com>
0 siblings, 5 replies; 6+ messages in thread
From: query @ 2010-03-08 12:25 UTC (permalink / raw)
To: pgsql-sql
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 ?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: list of all months
@ 2010-03-15 21:16 Garrett Murphy <gmurphy@lawlogix.com>
parent: query <search2006@rediffmail.com>
4 siblings, 0 replies; 6+ messages in thread
From: Garrett Murphy @ 2010-03-15 21:16 UTC (permalink / raw)
To: pgsql-sql
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...;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: list of all months
@ 2010-03-15 21:41 Petru Ghita <petrutz@venaver.info>
parent: query <search2006@rediffmail.com>
4 siblings, 0 replies; 6+ messages in thread
From: Petru Ghita @ 2010-03-15 21:41 UTC (permalink / raw)
To: query <search2006@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-----
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: list of all months
@ 2010-03-15 22:25 Dawid Kuroczko <qnex42@gmail.com>
parent: query <search2006@rediffmail.com>
4 siblings, 0 replies; 6+ messages in thread
From: Dawid Kuroczko @ 2010-03-15 22:25 UTC (permalink / raw)
To: query <search2006@rediffmail.com>; +Cc: pgsql-sql
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 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: list of all months
@ 2010-03-16 06:12 silly sad <sad@bankir.ru>
parent: query <search2006@rediffmail.com>
4 siblings, 0 replies; 6+ messages in thread
From: silly sad @ 2010-03-16 06:12 UTC (permalink / raw)
To: pgsql-sql
It looks like a procedural problem.
I would solve it in plpgsql.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: list of all months
@ 2010-03-16 09:02 Jasen Betts <jasen@xnet.co.nz>
parent: query <search2006@rediffmail.com>
4 siblings, 0 replies; 6+ messages in thread
From: Jasen Betts @ 2010-03-16 09:02 UTC (permalink / raw)
To: pgsql-sql
On 2010-03-08, query <search2006@rediffmail.com> 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-03-16 09:02 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-03-08 12:25 list of all months query <search2006@rediffmail.com>
2010-03-15 21:16 ` Garrett Murphy <gmurphy@lawlogix.com>
2010-03-15 21:41 ` Petru Ghita <petrutz@venaver.info>
2010-03-15 22:25 ` Dawid Kuroczko <qnex42@gmail.com>
2010-03-16 06:12 ` silly sad <sad@bankir.ru>
2010-03-16 09:02 ` Jasen Betts <jasen@xnet.co.nz>
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