Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 54CCF9FB4F8 for ; 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 ; 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 ; 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 ; 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 ; 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 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 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 some more interesting joining like comparing member growth to advertising dollars spent, or calculating the ratios between members and customer service calls.

It seems like a lot of data is inherently stored as time ranges. How would you query and manipulate such data?

-- 
----
Visit http://www.obviously.com/