From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Aaron Bono <postgresql@aranya.com>
Cc: Christine Desmuke <cdesmuke@kshs.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to get list of days between two dates?
Date: Tue, 06 Jun 2006 16:22:22 -0500
Message-ID: <1149628942.25526.232.camel@state.g2switchworks.com> (raw)
In-Reply-To: <bf05e51c0606061417y6504449ctc21725b70d21399a@mail.gmail.com>
References: <4485EE67.5060005@kshs.org>
<bf05e51c0606061417y6504449ctc21725b70d21399a@mail.gmail.com>
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.
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: smarlowe@g2switchworks.com, postgresql@aranya.com, cdesmuke@kshs.org
Subject: Re: How to get list of days between two dates?
In-Reply-To: <1149628942.25526.232.camel@state.g2switchworks.com>
* 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