From bryce1@obviously.com Thu Jun  21 05:35:41 2007
Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 54CCF9FB4F8
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 21 Jun 2007 02:35:41 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 64622-09 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 21 Jun 2007 02:35:38 -0300 (ADT)
X-Greylist: delayed 00:31:32.33347 by SQLgrey-1.7.5
Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159])
	by postgresql.org (Postfix) with ESMTP id C681D9FBCC6
	for <pgsql-sql@postgresql.org>; Thu, 21 Jun 2007 02:35:37 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3
Received: from outR.internet-mail-service.net (outR.internet-mail-service.net
	[216.240.47.241])
	by svr4.postgresql.org (Postfix) with ESMTP id C2B4E5AF039
	for <pgsql-sql@postgresql.org>; Thu, 21 Jun 2007 05:04:04 +0000 (GMT)
Received: from mx0.idiom.com (HELO idiom.com) (216.240.32.160)
	by out.internet-mail-service.net (qpsmtpd/0.32) with ESMTP;
	Wed, 20 Jun 2007 22:03:57 -0700
Received: from [192.168.1.109] (adsl-71-135-174-255.dsl.pltn13.pacbell.net
	[71.135.174.255]) by idiom.com (Postfix) with ESMTP id 00291125B4F
	for <pgsql-sql@postgresql.org>; Wed, 20 Jun 2007 22:03:56 -0700 (PDT)
Message-ID: <467A06BC.6090900@obviously.com>
Date: Wed, 20 Jun 2007 22:03:56 -0700
From: Bryce Nesbitt <bryce1@obviously.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.8.1.4) Gecko/20061107 SUSE/1.1.2-1.1 SeaMonkey/1.1.2
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Technique for turning time ranges into a graph
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200706/151
X-Sequence-Number: 28641

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
</head>
<body bgcolor="#ffffff" text="#000000">
I have a bunch of data which is expressed in terms of start and stop
dates, e.g.:<br>
<br>
<table border="1" cellpadding="2" cellspacing="2" height="112"
 width="460">
  <tbody>
    <tr>
      <td valign="top"><b>Member<br>
      </b></td>
      <td valign="top"><b>Start<br>
      </b></td>
      <td valign="top"><b>Stop<br>
      </b></td>
    </tr>
    <tr>
      <td valign="top">Fred<br>
      </td>
      <td valign="top">2007-01-01<br>
      </td>
      <td valign="top">2007-05-01<br>
      </td>
    </tr>
    <tr>
      <td valign="top">Joe<br>
      </td>
      <td valign="top">2005-05-04<br>
      </td>
      <td valign="top">2007-04-01<br>
      </td>
    </tr>
    <tr>
      <td valign="top">Freddie<br>
      </td>
      <td valign="top">2002-02-01<br>
      </td>
      <td valign="top">2006-04-01<br>
      </td>
    </tr>
    <tr>
      <td valign="top">...<br>
      </td>
      <td valign="top">...<br>
      </td>
      <td valign="top">...<br>
      </td>
    </tr>
  </tbody>
</table>
<br>
And what I want is a graph over time showing the number of members on
each day.<br>
<br>
Thus the input is rows with time ranges, and the output is a scalar for
each time bucket.  The time bucket might be months, days, hours, or
quarter hours. Such a data series could then be loaded into a
spreadsheet or otherwise graphed.<br>
<br>
I've got a perl script that can do this.  But is there a good and fast
way to do this in the database?  If I had views with the scalar data,
then I could do some more interesting joining like comparing member
growth to advertising dollars spent, or calculating the ratios between
members and customer service calls.<br>
<br>
It seems like a lot of data is inherently stored as time ranges.  How
would you query and manipulate such data?<br>
<pre class="moz-signature" cols="100">-- 
----
Visit <a href="http://www.obviously.com/">http://www.obviously.com/</a>
</pre>
</body>
</html>



From andreas.kretschmer@schollglas.com Thu Jun  21 05:58:06 2007
Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id C5AEA9FB56C
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 21 Jun 2007 02:58:06 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
	with ESMTP id 70548-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 21 Jun 2007 02:58:03 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from moutng.kundenserver.de (moutng.kundenserver.de
	[212.227.126.186])
	by postgresql.org (Postfix) with ESMTP id 07A8D9FB4C5
	for <pgsql-sql@postgresql.org>; Thu, 21 Jun 2007 02:58:02 -0300 (ADT)
Received: from [194.25.154.138] (helo=webserv.wug.de)
	by mrelayeu.kundenserver.de (node=mrelayeu5) with ESMTP (Nemesis),
	id 0ML25U-1I1Fg92nu2-0006NX; Thu, 21 Jun 2007 07:58:02 +0200
Received: from kretschmer by webserv.wug.de with local (Exim 3.36 #1)
	id 1I1Fg8-0000mw-00
	for pgsql-sql@postgresql.org; Thu, 21 Jun 2007 07:58:00 +0200
Date: Thu, 21 Jun 2007 07:58:00 +0200
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: Re: Technique for turning time ranges into a graph
Message-ID: <20070621055800.GA1405@a-kretschmer.de>
References: <467A06BC.6090900@obviously.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <467A06BC.6090900@obviously.com>
X-OS: Debian/GNU Linux - weil ich es mir Wert bin!
X-Message-Flag: "Windows" is not the answer. "Windows" is the question and the
	answer is "no"!
User-Agent: Mutt/1.5.9i
X-Provags-ID: V01U2FsdGVkX1/RWTTLDDlQUoP0GHTaLkqI39KjylX6cusNvE8
	d69FmOLz7TWMx+5BRv9vu1/CS0BzKDZ61Z30tt+vA9uSDdws45
	xKXXjT/C44xW4YGTi33sqAOBo75OzBV
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200706/152
X-Sequence-Number: 28642

am  Wed, dem 20.06.2007, um 22:03:56 -0700 mailte Bryce Nesbitt folgendes:
> I have a bunch of data which is expressed in terms of start and stop dates,
> e.g.:
> 
> +----------------------------------------------------------------+
> | Member         | Start                 | Stop                  |
> |----------------+-----------------------+-----------------------|
> | Fred           | 2007-01-01            | 2007-05-01            |
> |----------------+-----------------------+-----------------------|
> | Joe            | 2005-05-04            | 2007-04-01            |
> |----------------+-----------------------+-----------------------|
> | Freddie        | 2002-02-01            | 2006-04-01            |
> |----------------+-----------------------+-----------------------|
> | ...            | ...                   | ...                   |
> +----------------------------------------------------------------+
> 
> And what I want is a graph over time showing the number of members on each day.
> 
> Thus the input is rows with time ranges, and the output is a scalar for each
> time bucket.  The time bucket might be months, days, hours, or quarter hours.
> Such a data series could then be loaded into a spreadsheet or otherwise
> graphed.
> 
> I've got a perl script that can do this.  But is there a good and fast way to
> do this in the database?  If I had views with the scalar data, then I could do

Yes, play with generate_series like this:

test=*# select * from member;
  name   |   start    |    stop
---------+------------+------------
 Fred    | 2007-01-01 | 2007-05-01
 Joe     | 2005-05-04 | 2007-04-01
 Freddie | 0202-02-01 | 2006-04-01
(3 rows)

test=*# select foo.date, count(1) from member, (select
('2005-01-01'::date + (generate_series(0,20)||'month')::interval)::date)
foo  where foo.date between start and stop group by 1 order by 1;
    date    | count
------------+-------
 2005-01-01 |     1
 2005-02-01 |     1
 2005-03-01 |     1
 2005-04-01 |     1
 2005-05-01 |     1
 2005-06-01 |     2
 2005-07-01 |     2
 2005-08-01 |     2
 2005-09-01 |     2
 2005-10-01 |     2
 2005-11-01 |     2
 2005-12-01 |     2
 2006-01-01 |     2
 2006-02-01 |     2
 2006-03-01 |     2
 2006-04-01 |     2
 2006-05-01 |     1
 2006-06-01 |     1
 2006-07-01 |     1
 2006-08-01 |     1
 2006-09-01 |     1
(21 rows)



Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net