Monthly aggregateWindow always cuts off at 30th

[soeryobadja]

Hi all,

I am using Grafana 10.2.2 with Influxdb v2.7.8 to monitor my energy consumption and display monthly usage. I can say it is working, but only one problem: the monthly cutoff date is always on 30th even though the raw data provides until 31st. Here is one example.

Cutoff date for month December ends at 30th of December, similar thing happened to month of October.

Here is the code I put inside Grafana

option location = {zone: "", offset: 1s}

from(bucket: "gupbdg-powermon")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["entity_id"] == "monthly_total_energy")
  |> filter(fn: (r) => r["domain"] == "sensor")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["_measurement"] == "kWh")
  |> aggregateWindow(every: 1mo, fn: last, createEmpty: false)
  |> yield(name: "last")

Here is the screenshot of the raw data.

I am expecting that, a monthly aggregateWindow function provides end of month cutoff date according the the number of days each month, be it 30, 31 or 28 in February. But why my configuration always cuts off at 30th every month?

Any help to sort this problem is very much appreciated. Thank you in advance.

[yosiasz]

What does the data look like when you run it inside infludb… Does it work there

[soeryobadja]

I am using a template sensor in Home Assistant to monitor monthly electricity consumption. The sensor worked properly by providing measurement up until 31 Dec 2024 23.59.59 (similar case on October whereby raw data at Home Assistant Template Sensor cuts off at 31 Oct 2024 23.59.59) and the Influxdb shows the same (meaning, last timestamtp data was at 31 Dec 2024 at 23.59.59). That’s why I dont’t understand why Grafana cuts off at 30th every month, even though actually the month should have cut off at 31.

[grant2]

Just for kicks, can you replace your aggregateWindow line with this one and see how it affects your query results?
|> aggregateWindow(every: 1mo, offset: 1d, fn: last, timeSrc:"_start")
You might also see how adding createEmpty: true affects the results.

[soeryobadja]

Hi,
Just applying the update and the result as follow:

1. Applying createEmpty = true showed up the previous months where the data is not yet available, so this view is not suitable, at least aesthetically.
2. Applying aggregateWindow(every: 1mo, offset: 1d, fn: last, createEmpty: false) results in the following graph (a small portion of the usage of 1st date is separated, yet the total usage of past month (December) still ends at 30th, 23:59:59).
   
   
   
   As you can see, for this month, it still shows up until 31/01, writing this at 22:06 my local time.
3. Adding timeSrc:"_start" results in No Data.

Something is not right here. Very curious about this.

[soeryobadja]

OK, I made some mistake here.
Here is additional explanation:

1. I have 6 measurement (1-6 in the legend of the graph), each are built using template from Home Assistant.
2. I build up the stack table by creating 6 data queries as follow.
   
   
3. I changed the query as proposed, aggregateWindow(every: 1mo, offset: 1d, fn: last, createEmpty: false) to all 6 queries, and the resulting graph shows like this (full month of December no longer showing, only shows 01-Dec-24, and 01-Jan-25 is separated from the rest of this mont, still showing up to 31-Jan-25 at 22.18 my local time):
   
   

Each of my queries are as follow:

option location = {zone: "", offset: 1s}

from(bucket: "gupbdg-powermon")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["entity_id"] == "monthly_energy_1_office")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["_measurement"] == "kWh")
  |> filter(fn: (r) => r["domain"] == "sensor")
  |> aggregateWindow(every: 1mo, offset: 1d, fn: last, createEmpty: false)
  |> yield(name: "last")

Intresting to see how the graph will change in the middle of the night.

Appreciate very much on any solution for this situation.