pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTechnique for turning time ranges into a graph
2+ messages / 2 participants
[nested] [flat]
* Technique for turning time ranges into a graph
@ 2007-06-21 05:03 Bryce Nesbitt <bryce1@obviously.com>
0 siblings, 1 reply; 2+ messages in thread
From: Bryce Nesbitt @ 2007-06-21 05:03 UTC (permalink / raw)
To: pgsql-sql
<!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>
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Technique for turning time ranges into a graph
@ 2007-06-21 05:58 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Bryce Nesbitt <bryce1@obviously.com>
0 siblings, 0 replies; 2+ messages in thread
From: A. Kretschmer @ 2007-06-21 05:58 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-06-21 05:58 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-21 05:03 Technique for turning time ranges into a graph Bryce Nesbitt <bryce1@obviously.com>
2007-06-21 05:58 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
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