CODEWITHSUNDEEP
sqltimecasebuckettimescaledb
Uno1404
Uno1404

Reputation: 89

How to get the bigger value of two within every n-minute time frame in TimescaleDB

I want to compare the sum of two attributes A and B between timestamps x and y for every 15 minutes so I get the bigger value within this 15 minute time frame.

Table

id A B timestamp 1 5 10 2016-05-16 18:00:01 2 5 10 2016-05-16 18:15:00 3 15 5 2016-05-16 18:15:01 4 15 5 2016-05-16 18:30:00

So the query (x = 2016-05-16 18:00:01 and y = 2016-05-16 18:30:00) used with this table should return:

value timestamp 20 2016-05-16 18:00:01 30 2016-05-16 18:15:01

I think that you need some kind of combination of a CASE and a time_bucket() expression.

Upvotes: 2

Views: 191

Answers (1)

Blagoj Atanasovski
Blagoj Atanasovski

Reputation: 863

What you need is the function greatest. It returns the larger of two values, and you can pass the aggregates of the two columns

SELECT 
  greatest(sum(a), sum(b)) as value, 
  time_bucket('15m', t, '1s'::interval) as t
FROM k
WHERE t >= '2016-05-16 18:00:01' AND t <= '2016-05-16 18:30:00'
GROUP BY 2;

Notice that third argument in the time_bucket function. Time bucket by default rounds to the start of the 15m bucket, you use the third argument to specify an offset. From your example I saw that you want buckets to start at 1s offset.

Upvotes: 1

Related Questions