From: Michael Glaesemann <grzm@seespotcode.net>
To: pgsql-sql@postgresql.org
Subject: Re: How to get list of days between two dates?
Date: Fri, 7 Jul 2006 09:33:35 -0400
Message-ID: <4D58D07F-76F6-4307-8772-FFC1A273D6A0@seespotcode.net> (raw)
In-Reply-To: <200606070106.29950.x@vex.net>
References: <4485EE67.5060005@kshs.org>
<200606070106.29950.x@vex.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: grzm@seespotcode.net
Subject: Re: How to get list of days between two dates?
In-Reply-To: <4D58D07F-76F6-4307-8772-FFC1A273D6A0@seespotcode.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox