From: Christine Desmuke <cdesmuke@kshs.org>
To: pgsql-sql@postgresql.org
Subject: How to get list of days between two dates?
Date: Tue, 06 Jun 2006 16:06:47 -0500
Message-ID: <4485EE67.5060005@kshs.org> (raw)
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
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: cdesmuke@kshs.org
Subject: Re: How to get list of days between two dates?
In-Reply-To: <4485EE67.5060005@kshs.org>
* 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