From: david@fetter.org
To: pgsql-sql@postgresql.org
Subject: Re: generating a sequence table against which to do a LEFT OUTER JOIN
Date: Tue, 11 Oct 2005 13:35:21 -0500
Message-ID: <tPKdnUvyffZ0mNHeRVn-oQ@speakeasy.net> (raw)
References: <1128722342.139760.195020@g47g2000cwa.googlegroups.com>
Andrew Hammond <andrew.george.hammond@gmail.com> wrote:
> So I need an end result that has entries for all days, even when
> there's nothing happening on those days, generate from a timestamped
> event table. I've already got the interesting entries. Now I need to
> fill the holes.
>
> To do this, I'm thinking a LEFT OUTER JOIN against a date sequence
> table. So, how do I generate a table with every day from A to B?
>
> Or am I going about this the wrong way?
What you have is fine, but you're doing extra work. There's this neat
function in PostgreSQL 8.0 or better (you can write one for earlier
versions) called generate_series().
> SELECT pop_days('2005-01-01'::date, '2005-02-01'::date); -- barfs.
SELECT
'2005-01-01'::date + s.i * '1 day'::interval AS "Date",
t.your_date_col
FROM
generate_series(0,'2005-02-01'::date - '2005-01-01'::date - 1) AS s(i);
LEFT JOIN
your_table t
ON
('2005-01-01'::date + s.i = t.your_date_col);
You can also use generate_series() with a correllated subquery so as
not to have to hard-code dates.
HTH :)
Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
When a man tells you that he got rich through hard work, ask him:
'Whose?'
Don Marquis, quoted in Edward Anthony, O Rare Don Marquis
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: david@fetter.org
Subject: Re: generating a sequence table against which to do a LEFT OUTER JOIN
In-Reply-To: <tPKdnUvyffZ0mNHeRVn-oQ@speakeasy.net>
* 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