From: david@fetter.org (David Fetter)
Subject: Re: generating a sequence table against which to do a LEFT OUTER JOIN
References: <1128722342.139760.195020@g47g2000cwa.googlegroups.com>
User-Agent: tin/1.7.8-20050321 ("Hellisay") (UNIX) (Linux/2.6.13-1.1526_FC4
	(i686))
Message-ID: <tPKdnUvyffZ0mNHeRVn-oQ@speakeasy.net>
Date: Tue, 11 Oct 2005 13:35:21 -0500
Lines: 41
To: pgsql-sql@postgresql.org

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