Join machine's state and its power consumption

InfluxDB 2
, ,
1

Hi,

I try to combine data stored in InfluxDB. I stored the power consumption of a machine and its state (start, pause, stopped). The state is stored as string when it change:

image
image1390×501 7.41 KB

The power is stored continuously :

image
image1506×585 40.9 KB

I would like to separate the power with respect to the machine’s state.
Like superimposing this image and the last one

image
image1757×522 8.73 KB

How could I join these data ?

Regards
Stéphane

2

@steph2795 While I know this could be answered with a query, I think this could be answered by just layering your existing Grafana graphs over each other. Here’s a thread on the Grafana Community forum that talks about it: Is it possible to overlay 2 graphs? - Time Series Panel - Grafana Labs Community Forums

3

I didn’t explain myself well. What I want is to compute the energy used in started/stopped state. To do that, I’m gonna integrate the power.

I want to be able to separate the power data with respect to the machine’s state, then I will be able to compute the energy used for each of the state.

4

Can you share your queries which you have created for the above graphs?

5 
from(bucket: "test")
        |> range(start: -1h)
        |> filter(fn: (r) => r["_measurement"] == "State")

and

from(bucket: "test")
        |> range(start: -1h)
        |> filter(fn: (r) => r["_measurement"] == "Power")
6

@steph2795 Does this give you what you’re looking for? It will only return two values–the total amount of power produced in each state.

from(bucket: "test")
    |> range(start: -1h)
    |> filter(fn: (r) => r["_measurement"] == "State" or r["_measurement"] == "Power")
    |> pivot(rowKey:["_time"], columnKey: ["_measurement"], valueColumn: "_value")
    |> fill(column: "State", usePrevious: true)
    |> group(columns: ["State"])
    |> sum(column: "Power")
1 Like
7

Thanks it’s closer to the solution but not yet reached.

I modified a bit the code. I really need the integral (taking into account the time interval) not a sum :

data = from(bucket: "test")
    |> range(start: -1w)
    |> filter(fn: (r) => r["_measurement"] == "State" or r._measurement == "Power")
    |> pivot(rowKey:["_time"], columnKey: ["_measurement"], valueColumn: "_value")    
    |> sort(columns: ["_time"], desc: false)
    |> fill(column: "State", usePrevious: true)
    |> fill(column: "Power", usePrevious: true)

started =  data
        |> filter(fn: (r) => r.State == "Start")
    |> integral(column: "Power", unit:1h)
    |> keep(columns: ["Power"])
    |> yield(name: "StartedPower")
	
stopped =  data
        |> filter(fn: (r) => r.State == "Stopped")
    |> integral(column: "Power", unit:1h)
    |> keep(columns: ["Power"])
    |> yield(name: "StoppedPower")

Without the integral, I’m now able to separate the data :

image
image670×510 18.2 KB

But now the calcul of the integral is wrong, because if I compute for the “Start” state, when it is “Stopped”, the power values are not set to 0. There is a hole in the data and so the integral take the last power saved over the time that the machine stopped. To do the correct computation, I should have one serie for each of the states and for each _time it is in the other state, the value of power should be 0.

I’m still working on it

8

I found something that works but it is not very “clean”

data = from(bucket: "test")
    |> range(start: -1w)
    |> filter(fn: (r) => r["_measurement"] == "State" or r._measurement == "Power")
    |> pivot(rowKey:["_time"], columnKey: ["_measurement"], valueColumn: "_value")    
    |> sort(columns: ["_time"], desc: false)
    |> fill(column: "State", usePrevious: true)
    |> fill(column: "Power", usePrevious: true)

started =  data
    |> map(fn: (r) => ({
    _time: r._time,
    _value: if r.State== "Start" then r.Power else 0.0
     }))
    |> window(every: 1y)
    |> integral(unit:1h)
    |> yield(name: "StartedPower")

stopped =  data
    |> map(fn: (r) => ({
    _time: r._time,
    _value: if r.State== "Stopped" then r.Power else 0.0
     }))
    |> window(every: 1y)
    |> integral(unit:1h)
    |> yield(name: "StoppedPower")

I wonder if there’s a cleaner way to do it.
If I have more States, how could I automate it (no copy/paste) ?