Thread: Technique for turning time ranges into a graph

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
timeshowing the number of members on each day.<br /><br /> Thus the input is rows with time ranges, and the output is a
scalarfor each time bucket.  The time bucket might be months, days, hours, or quarter hours. Such a data series could
thenbe loaded into a spreadsheet or otherwise graphed.<br /><br /> I've got a perl script that can do this.  But is
therea good and fast way to do this in the database?  If I had views with the scalar data, then I could do some more
interestingjoining like comparing member growth to advertising dollars spent, or calculating the ratios between members
andcustomer service calls.<br /><br /> It seems like a lot of data is inherently stored as time ranges.  How would you
queryand manipulate such data?<br /><pre class="moz-signature" cols="100">-- 
 
----
Visit <a href="http://www.obviously.com/">http://www.obviously.com/</a>
</pre>

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-01Joe     | 2005-05-04 | 2007-04-01Freddie |
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 |     12005-02-01 |     12005-03-01 |     12005-04-01 |     12005-05-01 |
12005-06-01|     22005-07-01 |     22005-08-01 |     22005-09-01 |     22005-10-01 |     22005-11-01 |     22005-12-01
|    22006-01-01 |     22006-02-01 |     22006-03-01 |     22006-04-01 |     22006-05-01 |     12006-06-01 |
12006-07-01|     12006-08-01 |     12006-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