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:

The power is stored continuously :

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

How could I join these data ?

Regards
Stéphane

scott
August 6, 2024, 4:37pm
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

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.

grant1
August 7, 2024, 9:50am
4
Can you share your queries which you have created for the above graphs?

```
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")
```

scott
August 7, 2024, 5:18pm
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

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 :

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

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) ?