Need help about energy consumption per night / per day

Hi,
I’m pretty new to grafana/influxdb and even after searching and testing hours of hours, I’m not able to find a solution.

I’ve getting data from OpenHAB about the total energy consumption in kWh (about 1 update per hour). The query in influxql looks like this:

SELECT max(“value”) FROM “vHOUS_House_Energy_Meter_L1_kwh” WHERE $timeFilter GROUP BY time($__interval) fill(null)

It was relatively easy to create a bar graph to show the daily energy consumption with the following influxql query:

SELECT difference(distinct(“value”)) FROM “vHOUS_House_Energy_Meter_Total_kwh” WHERE $timeFilter GROUP BY time(24h) fill(none)

I’ve found in the documentation, that I have to use flux instead of influxql if I want to use “hourSelection” function because it’s not supported in influxql to be able to select a specific hour time range.

First, just for testing, I’ve changed my original query (still without hourSelection) from the above influxql to a flux query like this:

from(bucket: v.bucket)
** |> range(start: v.timeRangeStart, stop: v.timeRangeStop)**
** |> filter(fn: (r) => r[“_measurement”] == “vHOUS_House_Energy_Meter_Total_kwh”)**
** |> filter(fn: (r) => r[“_field”] == “value”)**
** |> filter(fn: (r) => r[“item”] == “vHOUS_House_Energy_Meter_Total_kwh”)**
** |> aggregateWindow(every: 1d, fn: last, createEmpty: true)**
** |> difference()**

This works so far, even if the values between the influxql and flux differ a little bit and the bar charts have very slim bars instead of the wide bars compared to the influxql query (but still don’t know why these differences are there). But that’s not a real issue.

Now I’ve tried to add the hourSelection like this to get the energy consumption for night time beginning at 19:00 in the evening until 7:00 in the morning (I wan’t to seen if my solar battery capacity will be big enough):

from(bucket: v.bucket)
** |> range(start: v.timeRangeStart, stop: v.timeRangeStop)**
** |> filter(fn: (r) => r[“_measurement”] == “vHOUS_House_Energy_Meter_Total_kwh”)**
** |> filter(fn: (r) => r[“_field”] == “value”)**
** |> filter(fn: (r) => r[“item”] == “vHOUS_House_Energy_Meter_Total_kwh”)**
** |> hourSelection(start: 19, stop: 7)**
** |> aggregateWindow(every: 1d, fn: last, createEmpty: true)**
** |> difference()**

But it looks like the hourSelection is ignored?

After playing aground I found that the following query gives me at least (nearly) one bar which comes close to what I want to achieve (the first bar for each day)
from(bucket: v.bucket)
** |> range(start: v.timeRangeStart, stop: v.timeRangeStop)**
** |> filter(fn: (r) => r[“_measurement”] == “vHOUS_House_Energy_Meter_Total_kwh”)**
** |> filter(fn: (r) => r[“_field”] == “value”)**
** |> filter(fn: (r) => r[“item”] == “vHOUS_House_Energy_Meter_Total_kwh”)**
** |> hourSelection(start: 19, stop: 7)**
** |> difference()**

Now I’m totally lost. How can I achieve to query to show just the consumed energy in a specific, hourly defined, time range for every day?

Thanks in advance for your help

update:
Grafana version: v9.4.3
InfluxDB version: v2.6.1

Welcome @infoc97f

Try inserting an aggregateWindow line, such as the one shown below, and see how that changes things.

from(bucket: v.bucket)
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "vHOUS_House_Energy_Meter_Total_kwh")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["item"] == "vHOUS_House_Energy_Meter_Total_kwh")
  |> aggregateWindow(every: 1d, fn: last, createEmpty: false)
  |> hourSelection(start: 19, stop: 7)
  |> difference()

Note that the hourSelection function is now AFTER the aggregateWindow function. In your post above, it was before.

Thanks for the hint, but unfortunately the result is exactly the same like the query without the hourSelection line. I only see the whole 24h consumption. It’s like the hourSelection statement is completely ignored?!

Also, I’ve updated to the newest Grafana version v9.5.6 and the newest InfluxDB version v2.7.1 but it makes no difference.

Here is the query like you’ve mentioned with it’s result graphic:

from(bucket: v.bucket)
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "vHOUS_House_Energy_Meter_Total_kwh")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["item"] == "vHOUS_House_Energy_Meter_Total_kwh")
  |> aggregateWindow(every: 1d, fn: last, createEmpty: false)
  |> hourSelection(start: 19, stop: 7)
  |> difference()

And here is the query without the hourSelection statement with it’s result graphic:

from(bucket: v.bucket)
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "vHOUS_House_Energy_Meter_Total_kwh")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["item"] == "vHOUS_House_Energy_Meter_Total_kwh")
  |> aggregateWindow(every: 1d, fn: last, createEmpty: false)
  |> difference()

Hi @infoc97f

You actually had it correct originally, i.e. with the hourSelection function BEFORE the aggregateWindow function. I had not realized that your above hour selection actually spans two different days (the last 5 hours of day n and the first 7 hours of day n+1). Unfortunately, this is not what Influx had in mind when they developed that function. Have a read through this thread and see if that can bring you closer to the answer.