Re: How to get list of days between two dates?

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