Grafana using +1 or 2h time (zone?) offset and this returns the wrong data

[homemonitor]

• What Grafana version and what operating system are you using?
  the open-source version of Grafana v11.2.0 (2a88694fd3

• What are you trying to achieve?
  I am trying to visualize the per 15m average highest peaks of power draw per month

• How are you trying to achieve it?
  I am logging all my power data in InfluxDB
  I have written a query which gets all the max averages per month

• What happened?
  Although I get the max averages per month they “spill over”, meaning that due to some kind of timerange issues/query (which is off by 1 or 2h depending on DST I think) the returned values are not always visualized correctly because data is included from previous time frames/months

• What did you expect to happen?
  To have the data returned according to the timeframes that i have set and not with an offset of 1 or 2h

• Can you copy/paste the configuration(s) that you are having problems with?
  

  

from(bucket: "EnergyFlow")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "energy")
  |> filter(fn: (r) => r["_field"] == "Total_W")
  |> filter(fn: (r) => r["meter"] == "main")
  |> filter(fn: (r) => r._value >= 0)
  |> aggregateWindow(every: 15m, fn: mean, createEmpty: false)
  |> aggregateWindow(every: 1mo, fn: max)
  |> yield(name: "mean")

• Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
  No, it is valid data, but not form valid data.

• Did you follow any online instructions? If so, what is the URL?
  No

[yosiasz]

is your timestamp in your data UTC?

[homemonitor]

UTC, I think, based on the fact if I run the query in Influx Explorer I get the following results:

[ebabeshko]

Try specifying required timezone in the aggregation function:

import "timezone"    

...

|> aggregateWindow(
        every: 1mo,
        fn: max,
        location: timezone.location(name: "Europe/Amsterdam")
    ) 

[homemonitor]

Thanks! It seems to help, at least for the offset of 2h.

However… There still seems to be a “spill” from one month or at least, there is a mismatch in the months I think:

It now shows as follows:

However, for example for the bar which shows up as July at 4.98kW, this number should be at June, and the June number of 11kW should be labeled as the May one.
I think this is also the reason why the october bar is visualized 2 times, because the 4.31kW is the one of september while the 4.21 is the one of October.

I saw some post of @grant2 but I am unable to arrive at a solution, tried with offset but that is something else I think.

[ebabeshko]

Try adding
timeSrc: “_start”
to your aggregation

[homemonitor]

It works! Oh yeah!

Thanks a million, I salute you from Belgium

For all who want a working example and want to graph their so called “capaciteitstarief” or peak tariff per 15 min aggregated on average and then the maximum of that month, below the query for reference.

import "timezone"
from(bucket: "EnergyFlow")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "energy")
  |> filter(fn: (r) => r["_field"] == "Total_W")
  |> filter(fn: (r) => r["meter"] == "main")
  |> filter(fn: (r) => r._value >= 0)
  |> aggregateWindow(every: 15m, fn: mean, createEmpty: false)
  |> aggregateWindow(every: 1mo, fn: max, location: timezone.location(name: "Europe/Amsterdam"), timeSrc: "_start")
  |> yield(name: "mean")