From: Tim Middleton <x@vex.net>
To: pgsql-sql@postgresql.org
Subject: Re: How to get list of days between two dates?
Date: Wed, 7 Jun 2006 01:06:29 -0400
Message-ID: <200606070106.29950.x@vex.net> (raw)
In-Reply-To: <4485EE67.5060005@kshs.org>
References: <4485EE67.5060005@kshs.org>
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)
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: x@vex.net
Subject: Re: How to get list of days between two dates?
In-Reply-To: <200606070106.29950.x@vex.net>
* 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