Custom start and stop times from a table in Grafana with InfluxDB Flux query

Grafana
1

Hi,

I have a table like this in Grafana:

Machine Start Stop

Wash 2023-11-13 07:43:34 2023-11-13 17:28:34

Wash 2023-11-09 21:48:24 2023-11-10 11:53:34

Wash 2023-11-08 15:41:34 2023-11-09 15:13:33

which is generated using the following advanced query in grafana:

data = from(bucket: “Test”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “T4”)
|> filter(fn: (r) => r[“City”] == “City”)
|> filter(fn: (r) => r[“Machine”] == “Wash”)
|> filter(fn: (r) => r[“MeasurementType”] == “Step”)
|> filter(fn: (r) => r[“Position”] == “General”)
|> truncateTimeColumn(unit: 1s)

first = data
|> first()
|> set(key: “Time”, value: “Start”)

last = data
|> last()
|> set(key: “Time”, value: “Stop”)

union(tables: [first, last])
|> pivot(rowKey: [“Machine”], columnKey: [“Time”], valueColumn: “_time”)
|> keep(columns: [“Start”,“Stop”,“Machine”])
|>group()
|>sort(columns: [“Start”])
|> filter(fn: (r) => r[“Start”] != r[“Stop”])
Now i need to modify this table with a new column ElectricityConsumption. I have another query to calculate the electricity consumed over a given time.
I need to calculate power consumed for each row in the table with start and stop in the range() as the start and stop times in each row of the table and then update each row of the new column ElectricityConsumed with the value calculated.

I am newly working in Grafana. Could anyone let me know is it possible to do this in Grafana?

Thank you in advance!

2

What is the formula for power consumed? Please share some sample data from your current query and show what fields from the rows that you want to use in order to calculate power consumed.

3

Hello,
Thank you for your response.

I have another query like this which is readings from the energy meter to calculate the power consumed:

energyConsumption = from(bucket: “Test”)
|> range(start: start._value._time, stop: stop._value._time)
|> filter(fn: (r) => r[“_measurement”] == “T4”)
|> filter(fn: (r) => r[“MeasurementType”] == “ElectricityCounter”)
|> filter(fn: (r) => r[“LocalReference”] == “Wash05”)
|> keep(columns: [“_time”, “_value”, “MeasurementType”])
|> sort(columns: [“_time”], desc: false)
|> aggregateWindow(every: 1h, fn: spread, createEmpty: false, timeSrc: “_start”)
|> map(fn: (r) => ({ _time: r._time, energyConsumed: r._value / 1000.0 }))
|> sum(column: “energyConsumed”)

I need to use this query in the query(data) mentioned in the question but the start and stop times in
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
should be from the table of the query discussed in the question.
The table obtained from the query looks like this:

image
image708×285 2.4 KB