CODEWITHSUNDEEP
influxdbinfluxdb-2
Wim Origin
Wim Origin

Reputation: 43

InfluxDB2.0 How to calculate daily value over bigger timeframe?

I'm migrating my InfluxDB1.8 version to InfluxDB2.0 I'm using a influxDB2.0 database and use grafana to display results. What I insert as data are the results of my P1 meter, altough the results are total values, I would like to calculate and display the daily results. What is being inserting is the current (gas usage) value. By calculating the difference of the begin and end of the day, I have my daily usage result.

I did find out a way to do this for 1 day. With the Spread function. But I don't get it working for a longer timeframe then 1 day.

day result

But now to display this on a daily usage on a longer timeframe. I didn't find the right option to get this working Week results Weekresults

Anyone an idea?

Query for 1 day:

  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "Gas-usage")
  |> filter(fn: (r) => r["_field"] == "value")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> spread(column: "_value")```

Upvotes: 0

Views: 2980

Answers (5)

bless_dis_mann
bless_dis_mann

Reputation: 1

You are a genius. I'm new to SO so it's not letting me attach the image directly but the spread function was the only piece I was missing. The top chart in the pic was used to check manually whether the daily totals were correct.

EDIT: increase() is not what I wanted. I should have been using cumulativeSum(). Query below is edited to be correct.

from (bucket: "${buckets}")
  |> range(start: -7d)
  |> filter(fn: (r) => r._measurement == "Tstat")
  |> filter(fn: (r) => r["Location"] == "${site}")
  |> filter(fn: (r) => r["_field"] == "compHeat1" or r["_field"] == "auxHeat1" or r["_field"] == "compCool1")
  |> cumulativeSum(columns: ["_value"])
  |> aggregateWindow(every: 1d, fn: spread, createEmpty: false)
  |> yield(name: "spread")

barchart i made

Upvotes: 0

rivolity
rivolity

Reputation: 1

from(bucket: "${bucket}")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "system")
  |> filter(fn: (r) => r.host == "${host}")
  |> filter(fn: (r) => r["_field"] == "uptime")
  |> aggregateWindow(every: 1d, fn: spread, createEmpty: false)

result of my grafana

Upvotes: 0

fmt.Println.MKO
fmt.Println.MKO

Reputation: 2050

use the spread function inside your aggreagateWindow function.

should be like this:

|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "Gas-usage")
|> filter(fn: (r) => r["_field"] == "value")
|> aggregateWindow(every: 1d, fn: spread, createEmpty: false)

Upvotes: 0

Wim Origin
Wim Origin

Reputation: 43

I did some checks on the 1.8 one and what works there is:

SELECT spread("value") 
FROM "Gas-usage" 
WHERE $timeFilter 
GROUP BY time(1d) fill(null) tz('Europe/Berlin')

what is the equivalant of this query in influxdb 2.0 ?

Upvotes: 2

Amerousful
Amerousful

Reputation: 2545

Try change your aggregate window, like this:

|> aggregateWindow(every: 1d, fn: mean)

Upvotes: 0

Related Questions