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

Though there may be a more eligant way to do it, when we did things like
this in the past we created a function (or stored procedure) that got the
min and max dates and then created a result set that iterated through the
dates to create a virtual table of days. Then you can inner join that list
of days with your physical table.

I am interested in other approaches though.

-Aaron

On 6/6/06, Christine Desmuke <cdesmuke(at)kshs(dot)org> wrote:
>
> Hello,
>
> I'm trying to write a query and cannot figure out how to do it (or
> whether it can be done in SQL alone). Given a table containing events
> with their starting and ending days (may be single- or multi-day
> events), I need a list of the events occurring each day:
>
> CREATE TABLE test_events (
> event_id serial,
> event_name text,
> start_time date,
> end_time date,
> CONSTRAINT event_pkey PRIMARY KEY (event_id)
> );
>
> INSERT INTO test_events (event_name, start_time, end_time) VALUES
> ('First Event', '05/01/2006', '05/04/2006');
> INSERT INTO test_events (event_name, start_time, end_time) VALUES
> ('Second Event', '05/02/2006', '05/02/2006');
> INSERT INTO test_events (event_name, start_time, end_time) VALUES
> ('Third Event', '05/04/2006', '05/05/2006');
> INSERT INTO test_events (event_name, start_time, end_time) VALUES
> ('Fourth Event', '05/07/2006', '05/07/2006');
>
> The query results should look like:
>
> 5/1/2006 First Event
> 5/2/2006 First Event
> 5/2/2006 Second Event
> 5/3/2006 First Event
> 5/4/2006 First Event
> 5/4/2006 Third Event
> 5/5/2006 Third Event
> 5/7/2006 Fourth Event
>
> I've been experimenting with set-returning functions, but I haven't
> stumbled on the answer. Suggestions?