From: Bryce Nesbitt <bryce1@obviously.com>
To: pgsql-sql@postgresql.org
Subject: Technique for turning time ranges into a graph
Date: Wed, 20 Jun 2007 22:03:56 -0700
Message-ID: <467A06BC.6090900@obviously.com> (raw)
<!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>
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: bryce1@obviously.com
Subject: Re: Technique for turning time ranges into a graph
In-Reply-To: <467A06BC.6090900@obviously.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