Timescaledb: postgresql (timescaledb) - sliding window - finding mean value of temp variable over every 5 seconds.

[Manjureka]

system information

• OS: [Windows 10 x64]
• PostgreSQL 11

here is an example dataset:

id | temp | datetime
1 | 10 | 2019-10-14 15:14:01
2 | 20 | 2019-10-14 15:14:02
3 | 30 | 2019-10-14 15:14:02.33
4 | 40 | 2019-10-14 15:14:05
5 | 10 | 2019-10-14 15:14:06
6 | 20 | 2019-10-14 15:14:07
7 | 30 | 2019-10-14 15:14:08
8 | 40 | 2019-10-14 15:14:09
9 | 30 | 2019-10-14 15:14:10
10 | 50 | 2019-10-14 15:14:11

Describe the bug
I have a sliding window problem in postgresql. Let's say my window size can be '5 seconds'. I need to find the mean value of temp variable for every 5 second interval based on timestamp. For example, let's take first 5 seconds (id starts from 1 to 4 is 5 seconds) then next 5 seconds means (id starts from 2 to 5 is another 5 seconds) then next 5 seconds means (id starts from 4 to 6 is another 5 seconds) and it goes on. I need to calculate the mean value of temp variable for every 5 seconds group by id.

can someone help me out with this problem using postgresql in timescaledb.

currently, I am using this code,

select * from (select *, (select avg(temp) from data where datetime between m.datetime - '2.5seconds'::INTERVAL and m.datetime + '2.5 seconds'::INTERVAL) as avg_value from data m) n limit 10;

Thanks,
Manjureka

[k-rus]

@Manjureka Do you get different results if you run the same query in PostgreSQL without TimescaleDB than with TimescaleDB?
It seems to me that your question is to get help using PostgreSQL functionality. Not a bug report or feature request to TimescaleDB, right?

Our community slack can be a good place to ask design questions. Have you tried it? https://slack.timescale.com

[Manjureka]

@k-rus answer to your question. I am getting the same result when I run this query with/without timescaledb.

yes, I need a solution using postgresql functionality.

[svenklemm]

@Manjureka you can get sliding windows with window functions
The following query calculates a sliding window over 5 seconds:

select time, avg(temp) OVER (ORDER BY time RANGE BETWEEN '2.5s' PRECEDING AND '2.5s' FOLLOWING) from data LIMIT 10;

[Manjureka]

@svenklemm. Thanks for your solution. I got the result.