X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id EEC119FA13E for ; Tue, 6 Jun 2006 18:17:39 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 57935-07 for ; Tue, 6 Jun 2006 18:17:24 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.170]) by postgresql.org (Postfix) with ESMTP id 4EFAA9FB1B9 for ; Tue, 6 Jun 2006 18:17:24 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id a2so35420ugf for ; Tue, 06 Jun 2006 14:17:19 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=Bgbntt0+f4TWtXEC/rhj5kwL2YAhBUUGjagFFaKaYJknjN9X5Ozoxkc7O/z0V5pzOd9TyxrEfoiTyhvtIaBt5w0vZQtmhKVuS4C6Xt67yWWA3jOq+045nUNtalA1AYDCuL2kALOXYuAKjozpu7Gp9TgAjZoNKae1Cx8rmHCB9JE= Received: by 10.67.100.17 with SMTP id c17mr31986ugm; Tue, 06 Jun 2006 14:17:19 -0700 (PDT) Received: by 10.67.20.12 with HTTP; Tue, 6 Jun 2006 14:17:19 -0700 (PDT) Message-ID: Date: Tue, 6 Jun 2006 16:17:19 -0500 From: "Aaron Bono" To: "Christine Desmuke" Subject: Re: How to get list of days between two dates? Cc: pgsql-sql@postgresql.org In-Reply-To: <4485EE67.5060005@kshs.org> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_11829_24219227.1149628639251" References: <4485EE67.5060005@kshs.org> X-Google-Sender-Auth: 5de9cdd68db26b89 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/74 X-Sequence-Number: 25310 ------=_Part_11829_24219227.1149628639251 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 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? ------=_Part_11829_24219227.1149628639251 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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?
------=_Part_11829_24219227.1149628639251--