From: Aaron Bono <postgresql@aranya.com>
To: Christine Desmuke <cdesmuke@kshs.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to get list of days between two dates?
Date: Tue, 6 Jun 2006 16:17:19 -0500
Message-ID: <bf05e51c0606061417y6504449ctc21725b70d21399a@mail.gmail.com> (raw)
In-Reply-To: <4485EE67.5060005@kshs.org>
References: <4485EE67.5060005@kshs.org>
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@kshs.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?
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: postgresql@aranya.com, cdesmuke@kshs.org
Subject: Re: How to get list of days between two dates?
In-Reply-To: <bf05e51c0606061417y6504449ctc21725b70d21399a@mail.gmail.com>
* 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