[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    Re: [SQL] How to get list of days between two dates?
From:       Michael Glaesemann <grzm () seespotcode ! net>
Date:       2006-07-07 13:33:35
Message-ID: 4D58D07F-76F6-4307-8772-FFC1A273D6A0 () seespotcode ! net
[Download RAW message or body]


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
[prev in list] [next in list] [prev in thread] [next in thread]