Re: show all record between two date after group by and aggrigation...

From: Jorge Godoy <jgodoy(at)gmail(dot)com>
To: "Chad Wagner" <chad(dot)wagner(at)gmail(dot)com>
Cc: "deepak pal" <deepak(dot)05pal(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: show all record between two date after group by and aggrigation...
Date: 2007-01-23 11:22:25
Message-ID: 87hcuihtse.fsf@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"Chad Wagner" <chad(dot)wagner(at)gmail(dot)com> writes:

> Most people do this with a "calendar" table, worst case is you could use
> generate_series to do it (but it's ugly, and it may not scale well --
> haven't tested it) and left join it to your data table.
>
> select cal.date, coalesce(foo.x, 0) AS x
> from (select (date_trunc('day', current_timestamp) + (s.s * interval '1
> day'))::date AS date
> from generate_series(1,365) AS s) AS cal
> left join foo ON cal.date = foo.create_date;

Why not, for the date part, the simpler alternative:

SELECT CURRENT_DATE + s.s AS DATE FROM generate_series(1, 365) AS s;

?

Be seeing you,
--
Jorge Godoy <jgodoy(at)gmail(dot)com>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message erick.papa 2007-01-23 13:12:21 Installing PostgreSQL under Cpanel
Previous Message A. Kretschmer 2007-01-23 11:17:27 Re: Postgresql.conf