Daily view showing 2 columns for today

Grafana InfluxDB
1

I’m 98% sure this is completely my lack of knowledge and understanding, but I need some assistance in fine tuning/correcting my Influx query. I am running Grafana 11.2.3 and Influx 2.7.11. With SunPower’s bankruptcy and SunStrong’s recent announcement of charging for the historical data, I started exporting my HomeAssistant Solar and Energy data into Influx. My hourly views are spot on, but my daily views are showing two columns for the current day and I can’t figure out why.

My query:

import "timezone"
option location = timezone.location(name: "America/Los_Angeles")

from(bucket: "home-assistant")
  |> range(start: -7d, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "W")
  |> filter(fn: (r) => r["entity_id"] == "solar_power_total")
  |> filter(fn: (r) => r["_field"] == "value")
  |> map(fn: (r) => ({ r with _value: r._value / 1000.0 }))
  |> aggregateWindow (every: 1h, fn: mean, createEmpty: false)
  |> truncateTimeColumn(unit: 1h)
  |> aggregateWindow (every: 1d, fn: sum, createEmpty: true)

My thoughts/logic on this query is that I pull the values from each hour throughout the day and add it all up to give me a total for that day.
My Grafana view:

image
image979×314 48.7 KB

As you can see in the bottom right, I have two columns for today, 3/20. The far right column looks like it is today so far, but I’m not sure how/where the next column over is getting its data from. My guess is the last 24 hours from that point backwards.

Is there something I am querying wrong? Or just a limitation of Influx/Grafana? Again, I am very new to this and learned a lot along the way, but I’m stumped at the moment.

2

Hi @UncleStu

Just a few ideas…

  1. Does changing your aggregateWindow function to this make a difference?

|> aggregateWindow(every: 1d, fn: sum, timeSrc: "_start")

  1. Does adding this help?
import "date"

from(bucket: "home-assistant")
  |> range(start: -7d, stop: date.truncate(t: now(), unit: 1d))
3

I knew you would have the answer @grant2. Thanks.

Adding "timeSrc: “_start”, to my daily aggregate line appears to be what I am after. The bar values appear to be the same with or without it, but it definitely puts the dates with their appropriate columns.

I messed around with this more last night, before your response, and I tried the date.truncate in the range. Maybe not that exact string you have, but something really similar. That resulted in an error. I did end up defining the date before the bucket, but needed to offset by 2 days so it would only show me the last 7 “completed” days. I’ll run with the timeSrc: addition and make sure I’m seeing what I expect. As a work around, the following did completely remove the current day from the graph, which also works, but doesn’t show Today So Far.

import "timezone"
import "date"
option location = timezone.location(name: "America/Los_Angeles")
todayStart = date.truncate(t: date.sub(d: 1d, from: now()), unit: 1d)
from(bucket: "home-assistant")
  |> range(start: -7d, stop: todayStart)
4

The only negative thing about using the timeSrc: is the time formatting of the axis column. I can’t seem to figure out how to remove the 00:00 from the display. I don’t see a Time Format under the Axis section and an Override on the panel doesn’t seem to be doing anything either. What am I missing?

image
image451×319 7.02 KB

5

I concur that the Override does not seem to do anything if Time Series visualization is being used, but does work if Bar chart visualization is used. No idea why this is the case. Specific instructions here.

image
image491×328 12.3 KB

1 Like
6

Custom unit is what I needed. I was looking at “time”, but not seeing the MM/DD I was looking for. Manually typing it into that field worked. Thanks again.

image
image206×110 5.77 KB