pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFiltering data based on timestamp
3+ messages / 2 participants
[nested] [flat]
* Filtering data based on timestamp
@ 2006-02-07 15:40 Panos Kassianidis <pkas@inaccessnetworks.com>
2006-02-07 15:49 ` Re: Filtering data based on timestamp Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 3+ messages in thread
From: Panos Kassianidis @ 2006-02-07 15:40 UTC (permalink / raw)
To: pgsql-sql
Hello everybody,
I am building a data logging application and I am facing the following
problem. I have a table which holds timestamp-value pairs for a recorded
variable. I would like to be able to select a range of values between
a starting
and an ending timestamp but also specify an interval over which values
should be averaged. For example let's say I have 180 values recorded
over the last 3 hours
(1 per minute)
and I would like to retrieve values recorded in the last 2 hours but
with an interval of 5 minutes, which means that my query should return
one tuple for every 5 tuples in the table and the value of this tuple
should be an average of 5 values.
Does anyone have any clue how I can make this query? I have though
about somehow grouping the tuples with group by and use the AVG
function but the tuples don't have anything in common to be grouped by.
Thank you very much in advance.
Panos
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Filtering data based on timestamp
2006-02-07 15:40 Filtering data based on timestamp Panos Kassianidis <pkas@inaccessnetworks.com>
@ 2006-02-07 15:49 ` Richard Huxton <dev@archonet.com>
2006-02-07 17:03 ` Re: Filtering data based on timestamp Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 3+ messages in thread
From: Richard Huxton @ 2006-02-07 15:49 UTC (permalink / raw)
To: Panos Kassianidis <pkas@inaccessnetworks.com>; +Cc: pgsql-sql
Panos Kassianidis wrote:
> and I would like to retrieve values recorded in the last 2 hours but
> with an interval of 5 minutes, which means that my query should return
> one tuple for every 5 tuples in the table and the value of this tuple
> should be an average of 5 values.
> Does anyone have any clue how I can make this query? I have though
> about somehow grouping the tuples with group by and use the AVG
> function but the tuples don't have anything in common to be grouped by.
You'll need to write an enhanced version of date_trunc() that can group
timestamps into arbitrary units, e.g.
my_date_trunc('... 12:34', 'm', 5) = '...12:30'
Then you can group by your new value.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Filtering data based on timestamp
2006-02-07 15:40 Filtering data based on timestamp Panos Kassianidis <pkas@inaccessnetworks.com>
2006-02-07 15:49 ` Re: Filtering data based on timestamp Richard Huxton <dev@archonet.com>
@ 2006-02-07 17:03 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 3+ messages in thread
From: Richard Huxton @ 2006-02-07 17:03 UTC (permalink / raw)
To: Panos Kassianidis <pkas@inaccessnetworks.com>; pgsql-sql
Panos Kassianidis wrote:
> Thanks very much for the answer.
> Could you also please tell me how am I supposed to write such a function?
> How is date_trunc implemented so that I can extend it? In C in SQL?
I'd probably write it in plpgsql unless you need the speed.
> And where can I find its source code?
With the source - available from the website.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-02-07 17:03 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-07 15:40 Filtering data based on timestamp Panos Kassianidis <pkas@inaccessnetworks.com>
2006-02-07 15:49 ` Richard Huxton <dev@archonet.com>
2006-02-07 17:03 ` Richard Huxton <dev@archonet.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