pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to get list of days between two dates?
5+ messages / 5 participants
[nested] [flat]
* How to get list of days between two dates?
@ 2006-06-06 21:06 Christine Desmuke <cdesmuke@kshs.org>
2006-06-06 21:17 ` Re: How to get list of days between two dates? Aaron Bono <postgresql@aranya.com>
2006-06-07 05:06 ` Re: How to get list of days between two dates? Tim Middleton <x@vex.net>
0 siblings, 2 replies; 5+ messages in thread
From: Christine Desmuke @ 2006-06-06 21:06 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to get list of days between two dates?
2006-06-06 21:06 How to get list of days between two dates? Christine Desmuke <cdesmuke@kshs.org>
@ 2006-06-06 21:17 ` Aaron Bono <postgresql@aranya.com>
2006-06-06 21:22 ` Re: How to get list of days between two dates? Scott Marlowe <smarlowe@g2switchworks.com>
1 sibling, 1 reply; 5+ messages in thread
From: Aaron Bono @ 2006-06-06 21:17 UTC (permalink / raw)
To: Christine Desmuke <cdesmuke@kshs.org>; +Cc: pgsql-sql
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?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to get list of days between two dates?
2006-06-06 21:06 How to get list of days between two dates? Christine Desmuke <cdesmuke@kshs.org>
2006-06-06 21:17 ` Re: How to get list of days between two dates? Aaron Bono <postgresql@aranya.com>
@ 2006-06-06 21:22 ` Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 0 replies; 5+ messages in thread
From: Scott Marlowe @ 2006-06-06 21:22 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; +Cc: Christine Desmuke <cdesmuke@kshs.org>; pgsql-sql
On Tue, 2006-06-06 at 16:17, Aaron Bono wrote:
> 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.
If you're on a version of pgsql with generate_series, you can use that
to easily create a pivoting data set on the fly to do this.
If you're on an older version, it's easy enough to make a generic pivot
table and keep it around to do these things. Since we're still on 7.4
where I work, we use the pre-populated pivot table. It's primitive, but
it works.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to get list of days between two dates?
2006-06-06 21:06 How to get list of days between two dates? Christine Desmuke <cdesmuke@kshs.org>
@ 2006-06-07 05:06 ` Tim Middleton <x@vex.net>
2006-07-07 13:33 ` Re: How to get list of days between two dates? Michael Glaesemann <grzm@seespotcode.net>
1 sibling, 1 reply; 5+ messages in thread
From: Tim Middleton @ 2006-06-07 05:06 UTC (permalink / raw)
To: pgsql-sql
This is going to be ugly, and I can't even say for sure it's right (and if by
chance it is right, I imagine it still might be more efficient broken up in a
function), but intrigued by learning about generate_series() from Scott
Marlows response I fiddled until I got the results specified like this...
SELECT dt, event_name
FROM (
SELECT (mn.d + s.d) AS dt
FROM (
SELECT min(start_time) FROM test_events) AS mn(d),
generate_series(0, (
SELECT (extract('epoch' from age(max(end_time),
min(start_time)))/86400)::integer
FROM test_events))
AS s(d))
AS x
JOIN test_events AS y ON (dt BETWEEN start_time AND end_time)
ORDER BY dt, event_name;
dt | event_name
------------+--------------
2006-05-01 | First Event
2006-05-02 | First Event
2006-05-02 | Second Event
2006-05-03 | First Event
2006-05-04 | First Event
2006-05-04 | Third Event
2006-05-05 | Third Event
2006-05-07 | Fourth Event
(8 rows)
--
Tim Middleton | Vex.Net | "Who is Ungit?" said he, still holding
x@veX.net | VexTech.ca | my hands. --C.S.Lewis (TWHF)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to get list of days between two dates?
2006-06-06 21:06 How to get list of days between two dates? Christine Desmuke <cdesmuke@kshs.org>
2006-06-07 05:06 ` Re: How to get list of days between two dates? Tim Middleton <x@vex.net>
@ 2006-07-07 13:33 ` Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 0 replies; 5+ messages in thread
From: Michael Glaesemann @ 2006-07-07 13:33 UTC (permalink / raw)
To: pgsql-sql
On Jun 7, 2006, at 1:06 , Tim Middleton wrote:
> I fiddled until I got the results specified like this...
I think this alternative may work as well. I refactored a bit of it
out into a view.
CREATE VIEW test_event_dates AS
SELECT min(start_time) as min_time, max(end_time) as max_time
FROM test_events;
SELECT event_date, event_name
FROM (
SELECT min_time + day_increment as event_date
FROM test_event_dates
CROSS JOIN generate_series(0, (
SELECT max_time - min_time
FROM test_event_dates
)
) as dates(day_increment)
) date_range
JOIN test_events ON (event_date BETWEEN start_time AND end_time)
ORDER BY event_date, start_time, event_name;
event_date | event_name
------------+--------------
2006-05-01 | First Event
2006-05-02 | First Event
2006-05-02 | Second Event
2006-05-03 | First Event
2006-05-04 | First Event
2006-05-04 | Third Event
2006-05-05 | Third Event
2006-05-07 | Fourth Event
(8 rows)
Michael Glaesemann
grzm seespotcode net
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-07-07 13:33 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-06 21:06 How to get list of days between two dates? Christine Desmuke <cdesmuke@kshs.org>
2006-06-06 21:17 ` Aaron Bono <postgresql@aranya.com>
2006-06-06 21:22 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-06-07 05:06 ` Tim Middleton <x@vex.net>
2006-07-07 13:33 ` Michael Glaesemann <grzm@seespotcode.net>
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