Thread: How to get list of days between two dates?

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
PRIMARYKEY (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

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 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.

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 Event2006-05-02 | First Event2006-05-02 | Second Event2006-05-03 | First
Event2006-05-04| First Event2006-05-04 | Third Event2006-05-05 | Third Event2006-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)

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