Calculation of the average energy consumption during the night hours from 10 p.m. to 6 a.m

Fluxlang
, ,
1

I run a sensor on my electricity meter that writes the meter reading to an InfluxDB2 via telegraph every 10 seconds.

The data in my bucket “Stromzaehler” looks like this:

import "timezone"
option location = timezone.location(name: "Europe/Berlin")

from(bucket: "Stromzaehler")
  |> range(start: v.timeRangeStart, stop: today())
  |> filter(fn: (r) => r["_measurement"] == "Leistungsdaten")
  |> filter(fn: (r) => r["_field"] == "Gesamtverbrauch")

table		_measurement	_field			_value		_time						host		topic
_RESULT		GROUP			GROUP			NO GROUP	NO GROUP					GROUP		GROUP
			STRING			STRING			DOUBLE		dateTime:RFC3339			STRING		STRING
0			Leistungsdaten	Gesamtverbrauch	26621.733	2022-08-14T11:50:20.000Z	telegraf	tele/Stromzaehler/SENSOR
0			Leistungsdaten	Gesamtverbrauch	26621.734	2022-08-14T11:50:30.000Z	telegraf	tele/Stromzaehler/SENSOR
0			Leistungsdaten	Gesamtverbrauch	26621.735	2022-08-14T11:50:40.000Z	telegraf	tele/Stromzaehler/SENSOR
0			Leistungsdaten	Gesamtverbrauch	26621.736	2022-08-14T11:50:50.000Z	telegraf	tele/Stromzaehler/SENSOR
0			Leistungsdaten	Gesamtverbrauch	26621.738	2022-08-14T11:51:00.000Z	telegraf	tele/Stromzaehler/SENSOR

I would like to calculate now my average energy consumption in the night hours from 22 o’clock to 6 o’clock for all data ever measured.

Can you please help me to create the query in Flux?

2

I tried it myself with the following query. Unfortunately, the query does not take into account the hours selection:

import "timezone"
option location = timezone.location(name: "Europe/Berlin")

from(bucket: "Stromzaehler")
  |> range(start: v.timeRangeStart, stop: today())
  |> filter(fn: (r) => r["_measurement"] == "Leistungsdaten")
  |> filter(fn: (r) => r["_field"] == "Gesamtverbrauch")
  |> hourSelection(start: 22, stop: 6)
  |> aggregateWindow(every: 1d, fn: last, createEmpty: false)
  |> difference(nonNegative: false, columns: ["_value"])
  |> mean(column: "_value")
  |> yield(name: "mean")

table		_measurement	_field				_value				host			topic
_RESULT	GROUP			GROUP				NO GROUP			GROUP		GROUP
			STRING			STRING				DOUBLE			STRING		STRING
0			Leistungsdaten	Gesamtverbrauch		11.88833333333322	telegraf	tele/Stromzaehler/SENSOR

Instead of the hour selection, the query takes the sensor data of the complete day.

3

@AlexanderK What version of InfluxDB/Flux are you using? This bug with hourSelection() should’ve been fixed in Flux v0.167.0 so you’d need to be using InfluxDB v2.3 or newer.

4

@scott I use InfluxDB 2.4.0.

root@influxdb:/# influx version 
Influx CLI 2.4.0 (git: 5c7c34f) build_date: 2022-08-18T19:26:48Z