Hello,
I’m new to flux language and I’m having trouble making an energy meter.

At the moment, the currently measured power (kW) of all 3 phases is sent every 5 seconds with MQTT.

The tables look like this:

Date/Time P_L1_kW P_L2_kW P_L3_kW
actual actual actual
Power Power Power
L1 L2 L3
2023-01-17 11:33:30 14.0 kW 13.0 kW 16.4 kW
2023-01-17 11:33:35 14.4 kW 13.0 kW 12.4 kW
2023-01-17 11:33:40 16.4 kW 11.0 kW 13.4 kW
2023-01-17 11:33:45 16.7 kW 15.0 kW 15.4 kW

The query looks like this:

from(bucket: "POWER")
|> range(start: -7d)
|> filter(fn: (r) => r["_measurement"] == "mqtt_influx")
|> filter(fn: (r) => r["host"] == "srv-mon1")
|> filter(fn: (r) => r["topic"] == "AKT_P_L3_kW" or
r["topic"] == "AKT_P_L2_kW" or
r["topic"] == "AKT_P_L1_kW")
|> toFloat() // -- string to float EXTREMELY SLOW!!!!!!!!!!!!!!!
|> aggregateWindow(fn: mean, every: 1h, createEmpty: false) // -- calculate kWh = 1h mean
|> aggregateWindow(fn: sum, every: 1d, createEmpty: true) // -- calculate sum kWh per day
|> group(columns: ["_time"], mode:"by") // -- group the 3 datasets (L1-L3) to one
|> sum() // -- sum up all the data
|> group() // -- to ungroup your data provide a group without any columns
|> yield(name: "sum_kWh_a_day") // -- dataset is +1 day, because ?!

This is the Bar Chart:

My Questions:

the transformation toFloat() is very slow, I don’t know how to fix this (in telegraph?)

why are the days shifted by +1 and the time is 01:00 ?

So,
for the the 2nd question I found a solution (but I’m not sure if it is the right one ;-):

I presume, the time shift of +1h is related to the first aggregateWindow(fn: mean, every: 1h, createEmpty: false)
So I added a time shift of 1 hour + 1 second and now there is no shift anymore…

(...)
|> aggregateWindow(fn: sum, every: 1d, createEmpty: true) //-- calculate sum kWh per day
|> timeShift(duration: -3601s, columns: ["_time"]) //-- fix, to calculate the right day!!!!

Then I removed the time from the x-axis.
Here I learned, that it is posible to write custom override options, even if they are not available in the drop down menue!

Just type in ‘time:DD/MM’ and press Enter, then grafana will accept this value!

Now the Bar chart looks like this:

The 1st question is under investigation from my side, any solution is very much apprecheated!
BG, Armin

Hi, I read very quickly your questions but for the fact that the timestamps after the aggregation seems to have a shift, read the help of the aggregate function. I think by default it takes as tsource: tstop. You want to explicit the tsource and specify to use tstrat. There is no need for the tjmesift. Same applies for the 1d.

For question 1 it’s better to actually write floats directly with the line protocol. I guess you are now writing strings? You better change at the source so that you avoid the conversion altogether

Also always apply non- pushdown function as late as possible in the code. Read about pushdown function and how to find bottlenecks on your code (keywords: optimize and profiler on the influx docuemntaition website)