Power Consumption with irregular values over time

Grafana InfluxDB
,
1

  • What Grafana version and what operating system are you using?
    v8.3.3 docker alpine mint

  • What are you trying to achieve?
    I want to show the amount of power production in Kw/h for PV. Accumulate the past 3 days. and display every day.

  • How are you trying to achieve it?

  from(bucket: "iobroker/global")
  |> range(start:  -10d)
  |> filter(fn: (r) => r["_measurement"] == "totalPV")
  |> filter(fn: (r) => r["_field"] == "value")
  |> aggregateWindow(every: 1d, fn: sum, createEmpty: false)
  |> yield(name: "daily sum or total")
  • What happened?
    I have some values but these are not right. Because i can’t do it with the aggregateWindow function because the value are only written in db when the value actually changes.
  • What did you expect to happen?
    Some function to calculate the kwh over time for all values per day

2023-02-21T15:01:40.000Z | 3015

2023-02-21T15:01:50.000Z | 2945

these are two values and the time to the next value is not static.
I would appreciate help in calculating the values over a day so that each value is calculated with the previous value and time.

{value}/{timerange to previous value}

Best regards

2

Welcome,

So in the above example what is your expected output?

3

i need a method to sum up all values from one day and calculate every value with the time to the previous value to get KW/h maybe its better to convert the watts to kw and the minutes or seconds to hours and than sum all calculated values to KW/h per day. I hope I have explained it understandably

4

Can you answer the question posed by @yosiasz ?

5 
15:01:50.000Z - 15:01:40.000Z 
// actuall value time minus previous value time
= 0.00278 //hours difference

3015 / 1000 = 3.015 kw
3.015 kw * 0.00278h = 0,008062 KWh

that calculation over every value in timerange -1day

sum all values = [kwh] per day

6

Hi @brummbaerrocket,
Try with this query:

import "date"

from(bucket: "iobroker/global")
  |> range(start: date.truncate(t: v.timeRangeStart, unit: 1d), stop: v.timeRangeStop) // date.truncate with unit: 1d is used in order to get whole starting day (e.g. 2023-02-15T00:00:00.000Z till now() ). If you use only v.timeRangeStart you will get time range e.g. 2023-02-15T20:04:15.683Z till now()
  |> filter(fn: (r) => r["_measurement"] == "totalPV")
  |> filter(fn: (r) => r["_field"] == "value")
  |> elapsed(unit: 1s)  // this function calculates time difference between subsequent data points and drops the first row (because there is no previous time to derive the elapsed time from) 
  |> map(fn: (r) => ({ r with _value: (float(v: r._value) / 1000.0) * (float(v: r.elapsed) / 3600.0) })) // formula 
  |> aggregateWindow(every: 1d, fn: sum, createEmpty: false)
  |> yield(name: "result")

 
Notice: Instead of using v.timeRangeStart I used date.truncate(t: v.timeRangeStart, unit: 1d) because I think that seeing value changes for first day of time range would be confusing. This way only value for last day updates when new data point arrives (i.e at dashboard refresh).

 

Best regards,
ldrascic

3 Likes
7

Wow thanks a lot that works like a charm. I see i have to dig deeper in flux thanks a lot