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

Question

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.

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

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")```

Hi @Amerousful, I didn't saw your comment directly under this post. Here you can find an week of data which is in influxdb 2.0. I hope your offer to have an look is still valid, as so far I didn't get it correctly working. Link to .csv on Google Drive. https://drive.google.com/file/d/1wKZJ77JGqjExCWUZ7V2s8FkmOjoqLQRX/view?usp=sharing — Wim Origin, Sep 01 '21 at 22:22

score 2 · Answer 1 · edited Aug 27 '22 at 10:33

2

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 ?

edited Aug 27 '22 at 10:33

ccellar

10,326
2
38
56

answered Jun 20 '21 at 17:13

Wim Origin

43
5

score 0 · Answer 2 · answered Jun 16 '21 at 14:33

0

Try change your aggregate window, like this:

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

answered Jun 16 '21 at 14:33

Amerousful

2,292
1
12
26

In combination with |> aggregateWindow(every: 1d, fn: mean) |> derivative(unit: 1d, nonNegative: true, columns: ["_value"], timeColumn: "_time") Then it shows the values per day. I only need to fix a timezone issue then. As the time starts at 02:00. Which seem utc +2. – Wim Origin Jun 19 '21 at 14:01
Did you found a solution for the 02:00 problem? – baeckerman83 Apr 29 '22 at 22:07

score 0 · Answer 3 · answered Sep 18 '22 at 17:38

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)

score 0 · Answer 4 · answered Dec 18 '22 at 23:16

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

score 0 · Answer 5 · edited Mar 26 '23 at 17:08

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

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

5 Answers5