Need help about energy consumption per night / per day

[infoc97f]

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

[grant2]

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.

[infoc97f]

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

[grant2]

@grant2

|> hourSelection(start: 19, stop: 7)

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.

[infoc97f]

nearly solved! But don’t know why?

After playing around hours with many many variants, I’ve found just one pair of queries which are working. My goal was to create a bar chart which shows the energy consumption in 24 hours (from 7:00 in the morning to 7:00 of the next morning). On the same bar chart I want to see (inside the same bars with an other color) the consumption for just the night time (when no solar will be available and the batteries are used) from 19:00 in the evening to 7:00 in the morning.

On Query A, which is the energy consumption for the whole day from 7:00 to 7:00, I’ve needed to add the location option for my location and an offset for aggregateWindow.

For Query B, which should show the night time energy usage I’ve needed to add the location option for my location but no offset (0h offset) for aggregateWindow AND I need to shift the hourSelection start and end times 1 hour to the “left”

I ONLY found this combination which produces nice matching overlay bars with the (nearly, but very close) correct values! I have absolutely no idea why only this exact pair of queries are producing the output I needed - it would be nice to understand why.

Here are the two queries which produces the correct chart(s). I’ve overlayed both bar charts and now I can see my overall energy consumption for the whole day (green) and the used part for the night form 19:00 to 7:00 (yellow).

query A: (24 hours from 7:00 to 7:00)

import "timezone"
option location = timezone.location(name: "America/Phoenix")
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: 24h, fn: max, createEmpty: false, offset: 4h)
  |> difference()

query B: (12 hours night from 19:00 to 7:00)

import "timezone"
option location = timezone.location(name: "America/Phoenix")
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-1, stop: 7-1)
  |> difference()
  |> aggregateWindow(every: 24h, fn: max, createEmpty: false, offset: 0h)

I really don’t know if there would be a better / different solution for this problem and maybe with a clear understanding why it works. I think this use case is not so special and I wonder if someone else may have this problems?

As a side note: As a second step, after this will be working, I want to feed the two “night” points in time depending on the current sun elevation angle because it varys over the seasons (from the time in the evening, when the sun goes below 15 degree before sunset and the time in the morning when the sun will go over 15 degree after sun rise). I already have these values in an other Grafana chart (also from InfluxDB via OpenHAB AstroBinding). At these two sun angles my solar system will climb over 1kW solar production in the morning and will go below 1kW solar production in the evening (I have a total of 18kWp PV panels). I’ve defined these two points for my situation as the solar night-time / day-time borders (depending of house idle power consumption, battery size, etc.).