Homeassistant gas calculation daily base

Grafana InfluxDB
1

Hi,

i am using a home assitant helper to get daily consumption of my gas.
I try to get it via influxdb 2 in grafana.
My query looks like:

import "timezone"
option location = timezone.location(name: "Europe/Berlin")
from(bucket: "HomeAssistant")

  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["friendly_name"] == "Gaszähler täglich")
  |> filter(fn: (r) => r["_field"] == "value")
  |> set(key: "_field", value: "VerbrauchHa2")
  |> aggregateWindow(every: 24h, fn: max, createEmpty: false)

image
image268×749 13.2 KB

I have two problems:

  1. i want to hide TODAY → I only want to have “full” days
  2. The date displayed has an offset of one today to much.
    I mean the last tag of the day before is mapped to the actual day.
2

Hi @janm and welcome to the Grafana forum. Looks like you got off to a good start…

How about this?

import "timezone"
option location = timezone.location(name: "Europe/Berlin")
from(bucket: "HomeAssistant")

import "experimental/date/boundaries"

from(bucket: "HomeAssistant")
  |> range(start: v.timeRangeStart, stop: boundaries.yesterday().stop)

I think you can use an offset in the aggregateWindow function. Something like the function shown below (you may need to play around with the + / - and value). I would also see if every: 1d vs. every: 24h makes a difference.

|> aggregateWindow(every: 1d, offset: -1d, fn: max, createEmpty: false)
3

Hello janm,
I think you are using ViCare inegration, right?
I have the same issue regarding the one-day offset.
This must be related to the way ViCare reports consumption data just after midnight. Instead of resetting the values immediately to 0.0 m3 at 0:00 time, ViCare adds a new day and copies the consumption from the previous day.

Freitag+Samstag
Freitag+Samstag660×617 56.6 KB

4

After over half an hour the displayed values and bar graph are correct but it is well past midnight.

1706917439598
17069174395981080×2280 119 KB

5

Perhaps the best way would be to read database values at 23:59 for each of the previous days? Is it possible in FLUX?

6

Yes, I think that timeshift would do this.

7

Thanks.
I will try to learn TIMESHIFT.

8

Thanks for your help @grant2 .
with yesterday in range it fixed double today for me.
now i have only the problem that the days are connected to wrong days :confused:
so i want to try timeshift . I think i only need 1 second or am I wrong in my mind:

import "timezone"
import "experimental/date/boundaries"
option location = timezone.location(name: "Europe/Berlin")
from(bucket: "HomeAssistant")

  |> range(start: v.timeRangeStart, stop: boundaries.yesterday().stop)
  |> filter(fn: (r) => r["friendly_name"] == "Gaszähler täglich")
  |> filter(fn: (r) => r["_field"] == "value")
  |> set(key: "_field", value: "VerbrauchHa2")
  |> aggregateWindow(every: 24h, fn: max, createEmpty: false)

so
image
is in Homeassistant correct and “yesterday”

9

ok - after “playing” with the values i think i’ve found a solution:

import "timezone"
import "experimental/date/boundaries"
option location = timezone.location(name: "Europe/Berlin")
from(bucket: "HomeAssistant")

  |> range(start: v.timeRangeStart, stop: boundaries.yesterday().stop)
  |> filter(fn: (r) => r["friendly_name"] == "Gaszähler täglich")
  |> filter(fn: (r) => r["_field"] == "value")
  |> set(key: "_field", value: "VerbrauchHa2")
  |> timeShift(duration: -1d)
  |> aggregateWindow(every: 24h, fn: max, createEmpty: false)
1 Like