Re: Postgresql Historical data store

From: Masaru Sugawara <rk73(at)sea(dot)plala(dot)or(dot)jp>
To: ben(dot)carlyle(at)invensys(dot)com
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Postgresql Historical data store
Date: 2002-06-13 17:20:09
Message-ID: 20020614005006.3CEA.RK73@sea.plala.or.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

On Wed, 12 Jun 2002 09:35:08 +1000
ben(dot)carlyle(at)invensys(dot)com wrote:

> So based on data of the form[2]:
>
> Time Value
> 1.01 1.005
> 1.02 1.006
> 1.09 2.504
> 2.34 2.459
>
> I should be able to request a sample every 0.02 seconds from 1.01 and get
> a result of
> Time Value
> 1.01 1.005
> 1.03 1.006
> 1.05 1.006
> 1.07 1.006
> 1.09 2.504
> 1.11 2.504
> ... snip ...
> 2.35 2.459
> and be able to do averages and other transforms on that result set, and
> also be able to do the true average calculations which would need to get
> the time difference between each entry and it's next entry:
> Avg = ((1.02-1.01)*1.005 + (1.09-1.02)*1.006 + (2.34 - 1.09) * 2.504) /
> (2.34 - 1.01)
>
> So... coming to the actual question:
> Are these kinds of transforms possible using simple SQL?

Perhaps you will be satisfied with receive the result of that kind of the
transformation by using the following query . But if there is no index
on the timestamp column, the query time do become extremely long.
Therefore to create an index is necessary at least.

BTW, as for getting the output of 0.02sec steps, I would think pl/pgsql
is needed.

create table tbl_test (tm timestamp without time zone default now(),
v float4);
create index idx_tbl_test_tm on tbl_test(tm);
insert into tbl_test values('2002-06-13 22:21:01.01', 1.005);
insert into tbl_test values('2002-06-13 22:21:01.02', 1.006);
insert into tbl_test values('2002-06-13 22:21:01.09', 2.504);
insert into tbl_test values('2002-06-13 22:21:02.34', 2.459);

SELECT
SUM(EXTRACT(MILLISECONDS FROM
( ( SELECT t0.tm FROM tbl_test AS t0 WHERE t1.tm < t0.tm
ORDER BY t0.tm LIMIT 1) - t1.tm ) * t1.v ) )
/ EXTRACT(MILLISECONDS FROM ( MAX(t1.tm) - MIN(t1.tm) ))
AS average
FROM
tbl_test AS t1;

average
------------------
2.41388721804511
(1 row)

Regards,
Masaru Sugawara

> If not... Are these kinds of transforms possible using PL/pgSQL or by
> coding some C into a shared library and introducing that as a fuction? Do
> temporary tables have a part to play in this, and how does one ensure that
> the temporary tables "live" only as long as the query takes to process?
>
> Although I've read most of the documentation relating to these fuctions on
> the postgresql web site it all seems to refer to functions that return
> simple typed values instead of performing any kind of transformation on
> the data set. Ideally I'd like any client application to be able to do a
> select on a data set and be able to trigger the appropriate functions
> internal to the server rather than bolting my own post-processing
> application on to the top of the system.
>
>

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Ben 2002-06-13 18:16:58 chown 'postgres': invalid user (win2000 install)
Previous Message Josh Berkus 2002-06-13 16:25:16 Re: case-(in)sensitive Server