Calculate on relative time

InfluxDB 2
1

Hello,
Im a newbie regarding Flux query language but Im willing to learn.

What I’ve got:

table _measurement _field _value _start _stop _time sensor topic
0 tasmota Power 1952 2023-04-21T17:51:15.147Z 2023-04-21T18:51:15.147Z 2023-04-21T17:51:20.000Z tasmota_waschingmachine tele/tasmota_waschingmachine/SENSOR
0 tasmota Power 1944 2023-04-21T17:51:15.147Z 2023-04-21T18:51:15.147Z 2023-04-21T17:51:30.000Z tasmota_waschingmachine tele/tasmota_waschingmachine/SENSOR
0 tasmota Power 2050 2023-04-21T17:51:15.147Z 2023-04-21T18:51:15.147Z 2023-04-21T17:51:40.000Z tasmota_waschingmachine tele/tasmota_waschingmachine/SENSOR
0 tasmota Power 1845 2023-04-21T17:51:15.147Z 2023-04-21T18:51:15.147Z 2023-04-21T17:51:50.000Z tasmota_waschingmachine tele/tasmota_waschingmachine/SENSOR
0 tasmota Power 1840 2023-04-21T17:51:15.147Z 2023-04-21T18:51:15.147Z 2023-04-21T17:52:00.000Z tasmota_waschingmachine tele/tasmota_waschingmachine/SENSOR

Every 10s I read the consumed power from my smart socket. I use Grafana to display the graphs over time.

What I want to achieve:
I want to calculate the consumption relative to the choosen time frame as a single value.

My approach:

time1int = int(v: v.timeRangeStart)
time2int = int(v: v.timeRangeStop)
time1float = float(v: time1int)
time2float = float(v: time2int)
span = (time2float - time1float)/3600.0

from(bucket: "SmartHome")
  |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
  |> filter(fn: (r) => 
       r._measurement == "tasmota" and 
       r._field == "Power" and 
       r.sensor == "tasmota_waschingmachine")
  |> sum()
  |> map(fn: (r) => ({ r with _value: (r._value*span)/1000.0}))

At first I calculate the seconds in the selected timeframe.
I convert it to float.
I calculate the duration in hours.
I read the data from the bucket, sum it up and calculate the consumption in kWh.

Obviously it doesnt work.

Please point me to the correct direction or show me an alternate approach. Any additional hints how to “echo” the variables or debug fluxQL are appreciated as well.

kind regards
Philipp

2

Hi @beacher_pille88

Am I correct in understanding that the data values in the table you shared (1952, 1944, 2050, etc.) are the power used in a 10-second interval? If yes, I think this would work. Try it in Influx Data Explorer first with the Raw Data view enabled.

from(bucket: "SmartHome")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "tasmota" and r["_field"] == "Power" and r["sensor"] == "tasmota_waschingmachine")
  |> sum(column: "_value")
  |> yield(name: "power_consumed")