-
What Grafana version and what operating system are you using?
Grafana 9.3.2 -
What are you trying to achieve?
Visualize in a bar graph the weekly consumption based on daily values for one month -
How are you trying to achieve it?
import “timezone”
option location = timezone.location(name: “Europe/Berlin”)
from(bucket: “iobroker”)
|> range(start: -1mo, stop: now())
|> filter(fn: (r) => r[“_measurement”] == “HausverbrauchKwh” and r[“_field”] == “value”)
|> aggregateWindow(every: 24h, fn: last, createEmpty: false)
|> truncateTimeColumn(unit: 1m)
|> aggregateWindow(every: 1w, fn: sum, createEmpty: false)
|> timeShift(duration: -1d) -
What happened?
-
What did you expect to happen?
Every week bar should last from Monday (including) until Sunday (including).
So this week starting with 12.6. (last bar → OK).
Penultimate bar starting with 5.6. (not OK, as currently 7.6.).
In general the values per day are accumulated per day in the source system. So the highest value is the last for the day (therefore I used last).
This week (12.6. - 13.6.): 29,49 kWh + 43,58 kWh = 73,07 kWh (not 172)
Last week (5.6. - 11.6.): 24,19+38,98+26,29+42,49+29,25+40,77+3,26 = 205,23 kWh -
Did you follow any online instructions? If so, what is the URL?
tried with: https://localhost:3000/t/influxdb2-power-consumption-group-by-month/77822/15
what do you see when you run this query in influx itself? please post screen shot if possible?
and how are you writing to influxdb and are you sure the dates are utc?
yep. and does it show accurately in influxdb explorer?
you have not answered this critical question
I write to influxdb via iobroker-adapter
Not quiet sure if the dates are really utc.
How can I find out?
And no, in influxdb explorer it is also not correct
Check iobroker adapter config maybe for time settings or server is is being run on etc
Run it in debug mode or see what it is dumping test mode to test bucket and compare to real utc
Could also be 
just checked the settings in iobroker-adapter: nothing to set there regarding time
server itself shows the right time
timedatectl
Local time: Wed 2023-06-14 08:50:08 CEST
Universal time: Wed 2023-06-14 06:50:08 UTC
when I run it without timezone-settings I receive
with timezone-setting
so obviously both not correct …
Debug mode: In grafana or indlux?? Anyway: How can I enable it?
as additional information:
When I just evaluate the last value per day everything is OK:
so it has something to do with the sum over one week
Is there a reason you chose in the above line 24h instead of 1d?
actually not … but I just tried and it didn’t make a difference
Hi @dieterlind
You wrote that when you evaluate the last value per day, everything is OK:
Can you show your Flux query for the above graph? Also, please remove the Relative Time = 1d in the query options just to get the “plain” results.
The Flux query for the above graph is:
import “timezone”
option location = timezone.location(name: “Europe/Berlin”)
from(bucket: “iobroker”)
|> range(start: -8d)
|> filter(fn: (r) => r[“_measurement”] == “HausverbrauchKwh” and r[“_field”] == “value”)
|> aggregateWindow(every: 24h, fn: last, timeSrc: “_start”)
current graph without relative time = 1d:
Just kicking around ideas here…
Since you want to aggregate the data by week starting on a Monday, and knowing the last value in a given week (the highest value) is the actual usage for the week, how about this:
import "timezone"
option location = timezone.location(name: "Europe/Berlin")
from(bucket: "iobroker")
|> range(start: -8d)
|> filter(fn: (r) => r["_measurement"] == "HausverbrauchKwh" and r["_field"] == "value")
|> aggregateWindow(every: 1w, offset: -3d, fn: last) // see comment below
Flux increments weeks from the Unix epoch, which was a Thursday. Because of this, by default, all 1w windows begin on Thursday. Use the offset parameter of -3d to shift the start of weekly windows to Monday.
Just thought of a question…Does the “counter” which reaches the highest value at 23:59 every day reset to 0 at 00:00?
How about this?
import "timezone"
option location = timezone.location(name: "Europe/Berlin")
from(bucket: "iobroker")
|> range(start: -8d)
|> filter(fn: (r) => r["_measurement"] == "HausverbrauchKwh" and r["_field"] == "value")
|> aggregateWindow(every: 1d, fn: spread, createEmpty: false) // calculates the amount per day using the spread
|> aggregateWindow(every: 1w, offset: -3d, fn: sum) // sums the 7 days of each week to get the total used per week
result:
somehow not what I expect
I would expect the last 4 weeks, last week starting with 12.6. summing up to approx. 165 kWh
Change the above to -28d or something more and see how each weekly brick looks.
OK … getting closer.
With this flux:
import "timezone"
option location = timezone.location(name: "Europe/Berlin")
from(bucket: "iobroker")
|> range(start: -1mo)
|> filter(fn: (r) => r["_measurement"] == "HausverbrauchKwh" and r["_field"] == "value")
|> aggregateWindow(every: 1d, fn: last, createEmpty: false) // calculates the amount per day using the spread
|> aggregateWindow(every: 1w, offset: -3d, fn: sum) // sums the 7 days of each week to get the total used per week
I get this result
Which is not completly wrong but also not really right 
Why: What I would like to have that e.g. the last bar hast the label 12.6. with all the consumption since including 12.6. including today. With my figures this would mean 181 kWh. What it does is to sum up from 11.6. until 16.6 (today missing).
The penultimate bar should have the label 5.6. summing up the values from 5.6. until 11.6. which would be 205 kWh. What at the moment happens is the sum from 4.6. until 10.6.
Maybe to make it easier to understand here the values form the last month:
| Date | kWh | expected values | current value |
|---|---|---|---|
| 18.05.2023 | 34,7 | 177,9 | 120,3 |
| 19.05.2023 | 37,3 | ||
| 20.05.2023 | 48,3 | ||
| 21.05.2023 | 57,6 | 322,2 | |
| 22.05.2023 | 45,2 | 300,2 | |
| 23.05.2023 | 44,8 | ||
| 24.05.2023 | 46 | ||
| 25.05.2023 | 45,9 | ||
| 26.05.2023 | 39,6 | ||
| 27.05.2023 | 43,1 | ||
| 28.05.2023 | 35,6 | 270,6 | |
| 29.05.2023 | 35,1 | 274 | |
| 30.05.2023 | 40,2 | ||
| 31.05.2023 | 26,7 | ||
| 01.06.2023 | 47,1 | ||
| 02.06.2023 | 42,6 | ||
| 03.06.2023 | 43,3 | ||
| 04.06.2023 | 39 | 241,1 | |
| 05.06.2023 | 24,2 | 205,36 | |
| 06.06.2023 | 39 | ||
| 07.06.2023 | 26,3 | ||
| 08.06.2023 | 42,5 | ||
| 09.06.2023 | 29,3 | ||
| 10.06.2023 | 40,8 | ||
| 11.06.2023 | 3,26 | 183,96 | |
| 12.06.2023 | 29,5 | 180,7 | |
| 13.06.2023 | 47,1 | ||
| 14.06.2023 | 26,1 | ||
| 15.06.2023 | 40,6 | ||
| 16.06.2023 | 26,1 | ||
| 17.06.2023 | 11,3 |