Summary and representation of daily to calendar monthly values

Grafana InfluxDB
21

I think this should do it. Not sure if you have several months worth of data, but in Grafana you should be able to display the monthly totals in a Bar Chart.

from(bucket: "home_assistant/autogen")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "Wh")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["domain"] == "sensor")
  |> filter(fn: (r) => r["entity_id"] == "bkw_yieldday")
  |> filter(fn: (r) => r["friendly_name"] == "BKW YieldDay")
  |> filter(fn: (r) => r["source"] == "HA")
  |> aggregateWindow(every: 1d, fn: max, createEmpty: false) // calculates the amount per day using the max value from the day
  |> aggregateWindow(every: 1mo, fn: sum, createEmpty: false) // sums each of the amounts per day and aggregates by month
  |> yield(name: "monthly data")
22

I have data sets available from mid-February of this year. That should at least be enough for something.
Unfortunately I get a new error message.

Same in influxDB

image
image935×770 68.4 KB

Sorry for the late reply. I seemed to have reached a newbie limit.

@yosiasz The export of the last seven days contains 63419 lines. I think that’s a bit too much for the forum.

23

Hi @say1897

Which version of InfluxDB are you using?

24

MUST READ for schema design that will help reduce memory load. the issue can also be due to improper schema design of influxdb

give us a sampling of 10 rows and we can autogenerate the rest for ex using following site

25

Hi again @say1897

The error message you received ("500 Internal Server Error: {"error":"panic: runtime error: invalid memory address or nil pointer dereference"} appears to happen on older versions of Influx. I was using v2.5 when I successfully used the same query with your sample data. Can you please check and advise?

26

image

as Homeassistant Addon.

Thats annoying

image
image820×195 14.5 KB

27

Hi @say1897

So I believe I have seen this happen in the past, whereby HomeAssistant installs some old version of InfluxDB. There is no Version 5.0.0 of InfluxDB (go ahead and ask them, but you’ll probably have to wait until 2027 or so).

Maybe someone who has deeper familiarity with HomeAssistant and its add-ons can chime in here on how to update InfluxDB to the most current version.

28

I’ll try to check in the next days. I’ll answer soon. Thanks for now

29

First of all, I would like to apologize for responding only now. Unfortunately, something came up in my personal life, and I couldn’t get to it. I hope you can still help me.

A few days ago, I set up a completely new InfluxDB in Home Assistant, and I believe I am now a step further. There aren’t many data points yet, but it should be sufficient for the start.

My code:

from(bucket: "CdJDaten")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "Wh")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["domain"] == "sensor")
  |> filter(fn: (r) => r["entity_id"] == "bkw_yieldday")
  |> filter(fn: (r) => r["friendly_name"] == "BKW YieldDay")
  |> filter(fn: (r) => r["source"] == "HA")
  |> aggregateWindow(every: 1mo, fn: max, createEmpty: false)
  |> yield(name: "monthly data")

This code should actually display calendar months, right? All the data is from July. However, the graph is displayed differently. Am I still doing something wrong here?
And that’s what i got

image
image944×454 16.4 KB

30

Hi @say1897

Yes.

What do you see if you toggle on Table View?

image

31

I see this.

image
image925×71 6.96 KB

That seems to be only partially correct to me. It might possibly display the time until August 1st. That might be the reason why the bar starts in August.

32

Can you remove this line from your query

|> aggregateWindow(every: 1mo, fn: max, createEmpty: false)

and then repost what you see in table view?

33

I got an info message:

A query returned too many datapoints and the results have been truncated at 9311 points to prevent memory issues. At the current graph size, Grafana can only draw 931. Try using the aggregateWindow() function in your query to reduce the number of points returned.

And in the table I got this. This is just a section of the data.

image
image938×216 7.99 KB

34

What happens if you replace your aggregateWindow statement with these two?

  |> aggregateWindow(every: 1d, fn: max, createEmpty: false) // calculates the amount per day using the max value from the day
  |> aggregateWindow(every: 1mo, fn: sum, createEmpty: false) // sums each of the amounts per day and aggregates by month
35 
from(bucket: "CdJDaten")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "Wh")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["domain"] == "sensor")
  |> filter(fn: (r) => r["entity_id"] == "bkw_yieldday")
  |> filter(fn: (r) => r["friendly_name"] == "BKW YieldDay")
  |> filter(fn: (r) => r["source"] == "HA")
|> aggregateWindow(every: 1d, fn: max, createEmpty: false) // calculates the amount per day using the max value from the day
  |> aggregateWindow(every: 1mo, fn: sum, createEmpty: false) // sums each of the amounts per day and aggregates by month
  |> yield(name: "weekly data")

image
image931×79 7.16 KB

image
image892×440 17.5 KB

36

So in table format, you are only getting 1 value (30745)? Is your time picker spanning the past 3 or 6 months?

37

I only have the values from a few days in the database due to the new database. All from July.

Or what did you mean?

38

do you have next month as stop in your time picker? something like this?
time picker

39

image
image916×175 13.6 KB

I don’t think so…

40

So it gives you correct result if you have data only for July (i.e. one value dated 2024-08). You can set timeSrc to _start in your aggregation if you want to get 2024-07 for July:

  |> aggregateWindow(every: 1mo, fn: sum, createEmpty: false, timeSrc: "_start") // sums each of the amounts per day and aggregates by month using _start column for the output timestamps