[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: Re: [SQL] How to get list of days between two dates?
From: "Aaron Bono" <postgresql () aranya ! com>
Date: 2006-06-06 21:17:19
Message-ID: bf05e51c0606061417y6504449ctc21725b70d21399a () mail ! gmail ! com
[Download RAW message or body]
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?
[Attachment #3 (text/html)]
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. \
<br><br>I am interested in other approaches though.<br><br>-Aaron<br><br><div><span \
class="gmail_quote">On 6/6/06, <b class="gmail_sendername">Christine Desmuke</b> \
<<a href="mailto:cdesmuke@kshs.org">cdesmuke@kshs.org </a>> \
wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, \
204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">Hello,<br><br>I'm trying to \
write a query and cannot figure out how to do it (or <br>whether it can be done in \
SQL alone). Given a table containing events<br>with their starting and ending days \
(may be single- or multi-day<br>events), I need a list of the events occurring each \
day:<br><br>CREATE TABLE test_events ( <br> event_id \
serial,<br> event_name text,<br> start_time \
date,<br> end_time date,<br> CONSTRAINT event_pkey PRIMARY \
KEY (event_id)<br>);<br><br>INSERT INTO test_events (event_name, start_time, \
end_time) VALUES<br> ('First Event', '05/01/2006', '05/04/2006');<br>INSERT INTO \
test_events (event_name, start_time, end_time) VALUES<br>('Second Event', \
'05/02/2006', '05/02/2006');<br>INSERT INTO test_events (event_name, start_time, \
end_time) VALUES <br>('Third Event', '05/04/2006', '05/05/2006');<br>INSERT INTO \
test_events (event_name, start_time, end_time) VALUES<br>('Fourth Event', \
'05/07/2006', '05/07/2006');<br><br>The query results should look \
like:<br><br>5/1/2006 First Event \
<br>5/2/2006 First \
Event<br>5/2/2006 Second \
Event<br>5/3/2006 First \
Event<br>5/4/2006 First \
Event<br>5/4/2006 Third \
Event<br>5/5/2006 Third \
Event<br>5/7/2006 Fourth Event<br> \
<br>I've been experimenting with set-returning functions, but I haven't<br>stumbled \
on the answer. Suggestions?</blockquote></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic