Calculate energy consumption of each discharge event

InfluxDB 2 Dashboards
1

Hi,

I hope someone can help me create a query to dynamic group / window a time series. I like to group specific events with a negative gradient. I already have the gradient, but don’t know how to do the window / grouping.
It would also be ok, if the positive gradients are also grouped.

Example Influx

The code to create the chart is below. Hopefully someone has an idea how to group the red marked areas dynamically.

from(bucket: “data”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“sensor”] == “123”)
|> filter(fn: (r) => r[“_measurement”] == “BatteryCurrent”)
|> window(every:10m)
|> integral(unit: 1h, column: “_value”)
|> duplicate(column: “_stop”, as: “_time”)
|> window(every: inf)
|> cumulativeSum(columns: [“_value”])
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)

Thank you so much

2

Hello @ThiloWenzel,
You could try following logic similar to this:

But that could be overkill.
Perhaps you just want to just use the derivative function to identify periods of negative slope.

4

Hallo Anaisdg,

thanks for your reply. I finally got it to work. Probably very inefficient :slight_smile:

import “math”
from(bucket: “data”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “BatteryCurrent”)
|> aggregateWindow(every: 30s, fn: mean, createEmpty: false)
|> keep(columns: [“_time”, “_value”,“_start”,“_stop”])
COMMENT: Create Bool with -1 and 1 for negative and positive values
|> map(
fn: (r) => ({r with
_chdBOOL: if r._value < 0 then
-1
else
0
}),
)
COMMENT: calculate derivate to find start and endpoints for my groups
|> derivative(unit: 1m, nonNegative: false, columns: [“_chdBOOL”], timeColumn: “_time”)
COMMENT: calculate absolute value and running sum to get a uique number per group
|> map( fn: (r) => ({r with _chdBOOL: math.abs(x: r._chdBOOL)}),)
|> cumulativeSum(columns: [“_chdBOOL”])
|> group(columns: [“_chdBOOL”,“_start”,“_stop”], mode:“by”)
|> integral(unit: 1h, column: “_value”)
|> group()

5

Hello Thilo Wenzel.
I understand that your problem has been solved
This is a nice problem with a large opertunity, you look for a trigger on an edge , problem is that the conclusion comes at the end of the sample and has to be moved to the beginning.

This feature should be built in as standard.
This is strong concept for signal analysis.
I worked out the solution which Aniasdg suggested and it works.
But this solution is not efficient. the derivative comes with the answer to (t_n+1) and should be known at (t_n) . So a table has to be split and shifted in time and then merged.

my universal solution

import "influxdata/influxdb/sample"
import "math"

// ----------------- Custom function ------------------------------------
// add column `slope` 1.0 is positief-slope -1.0 negatief-slope
// 0 no slope or lower than treshold y
slopeByX = (tables=<-, x = 0, y = 0.01) =>
	tables
	|> map(fn: (r) => ({r with slope: (
	if math.abs(x: r._value) < y then 0.0 //treshold
	else if r._value < x then -1.0 // make 0.0 for only pos
	else if r._value > x then 1.0 // make 0.0 for only neg
	else 0.0
)
}))

// =======================================================================
//                           Sample Data

stage_1 = sample.data(set: "airSensor")
	|> range(start: -1h)
	|> filter(fn: (r) => r["_field"] == "co")
	// make a copy `r_value`
	|> map(fn: (r) => ({r with co_value: r._value})) 
	|> keep(columns: ["_time", "_field", "_value", "co_value"])
	|> limit(n: 10)  //for demo
	|> yield(name: "first stage")

// Second stage slope & timeshift
stage_2 = stage_1
	|> filter(fn: (r) => r["_field"] == "co")
	|> difference()

	// define cutome function `slopeByX()`
	// x = level y = noise level floating point
	|> slopeByX(x: 0.0, y: 0.01) 
	|> keep(columns: ["_time", "slope","co_value"])
	
	// sample duration  = 10 seconde 
	// from t_n -- to --> t_n-1 
	// conclusions is on end of sample and need begin of the sample!!.
	|> timeShift(duration: -10s)
	|> yield(name: "second stage")

// Third stage join and output
stage_3 = join(tables: {stage_1: stage_1, stage_2: stage_2}, on: ["_time"])
|> keep(columns: ["_time", "slope", "co_value", "_value"])
|> yield(name: "stage 3")
1 Like