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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
