Message-ID: <4485EE67.5060005@kshs.org>
Date: Tue, 06 Jun 2006 16:06:47 -0500
From: Christine Desmuke <cdesmuke@kshs.org>
Organization: Kansas State Historical Society
User-Agent: Thunderbird 1.5.0.4 (Windows/20060516)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: How to get list of days between two dates?
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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?

Thanks
--christine

Christine Desmuke
Database Administrator
Kansas State Historical Society
Topeka, KS
cdesmuke@kshs.org