From: Tim Middleton <x@vex.net>
Reply-To: pgsql-sql@postgresql.org
Organization: xxvii.net
To: pgsql-sql@postgresql.org
Subject: Re: How to get list of days between two dates?
Date: Wed, 7 Jun 2006 01:06:29 -0400
User-Agent: KMail/1.9.1
References: <4485EE67.5060005@kshs.org>
In-Reply-To: <4485EE67.5060005@kshs.org>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200606070106.29950.x@vex.net>

This is going to be ugly, and I can't even say for sure it's right (and if by 
chance it is right, I imagine it still might be more efficient broken up in a 
function), but intrigued by learning about generate_series() from Scott 
Marlows response I fiddled until I got the results specified like this...

SELECT dt, event_name 
FROM (
    SELECT (mn.d + s.d) AS dt 
    FROM (
        SELECT min(start_time) FROM test_events) AS mn(d),
                generate_series(0, (
                        SELECT (extract('epoch' from age(max(end_time),
                                min(start_time)))/86400)::integer 
                        FROM test_events)) 
                        AS s(d)) 
    AS x 
JOIN test_events AS y ON (dt BETWEEN start_time AND end_time) 
ORDER BY dt, event_name;

     dt     |  event_name
------------+--------------
 2006-05-01 | First Event
 2006-05-02 | First Event
 2006-05-02 | Second Event
 2006-05-03 | First Event
 2006-05-04 | First Event
 2006-05-04 | Third Event
 2006-05-05 | Third Event
 2006-05-07 | Fourth Event
(8 rows)

-- 
Tim Middleton | Vex.Net    | "Who is Ungit?" said he, still holding
x@veX.net     | VexTech.ca | my hands. --C.S.Lewis (TWHF)