Mime-Version: 1.0 (Apple Message framework v752.2)
In-Reply-To: <200606070106.29950.x@vex.net>
References: <4485EE67.5060005@kshs.org> <200606070106.29950.x@vex.net>
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <4D58D07F-76F6-4307-8772-FFC1A273D6A0@seespotcode.net>
Content-Transfer-Encoding: 7bit
From: Michael Glaesemann <grzm@seespotcode.net>
Subject: Re: How to get list of days between two dates?
Date: Fri, 7 Jul 2006 09:33:35 -0400
To: pgsql-sql@postgresql.org


On Jun 7, 2006, at 1:06 , Tim Middleton wrote:

> I fiddled until I got the results specified like this...

I think this alternative may work as well. I refactored a bit of it  
out into a view.

CREATE VIEW test_event_dates AS
SELECT min(start_time) as min_time, max(end_time) as max_time
FROM test_events;

SELECT event_date, event_name
FROM (
     SELECT min_time + day_increment as event_date
     FROM test_event_dates
     CROSS JOIN generate_series(0, (
         SELECT max_time - min_time
         FROM test_event_dates
             )
         ) as dates(day_increment)
     ) date_range
JOIN test_events ON (event_date BETWEEN start_time AND end_time)
ORDER BY event_date, start_time, event_name;

event_date |  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)

Michael Glaesemann
grzm seespotcode net