Flux: 3phase network - actual power consumtion to actual energy consumption (slow & day shift +1)

InfluxDB 2
, ,
1

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:

image

My Questions:

  1. the transformation toFloat() is very slow, I don’t know how to fix this (in telegraph?)
  2. why are the days shifted by +1 and the time is 01:00 ?

.
Any help is welcome,
thank you in advance!

BG Armin

2

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!
image
Just type in ‘time:DD/MM’ and press Enter, then grafana will accept this value!

Now the Bar chart looks like this:

image

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

3

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.

4

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

5

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)