For my balcony power plant I have a Shelly that records the kilowatt hours per day.
My problem is getting the whole thing into InfluxDB2 as a monthly value.
The problem here is that I always have to take the last day’s value in the query, but how do I add up all these values to a month?
Actually i have this Query:
from(bucket: "bucket1")
|> range(start:-30d)
|> filter(fn: (r) => r["_measurement"] == "power")
|> filter(fn: (r) => r["_field"] == "todaykwh")
|> aggregateWindow(every: 1d, fn: last, createEmpty: false)
|> yield(name: "last")
So i get the last value of every day the last 30 days
But i need the sum of every last values within a month
Use the sum() function last. That would sum up all your values
1 Like
Ok now it works but i in InfluxDB2 UI but not in Grafana:
from(bucket: "bucket1")
|> range(start:-30d)
|> filter(fn: (r) => r["_measurement"] == "power")
|> filter(fn: (r) => r["_field"] == "todaykwh")
|> aggregateWindow(every: 24h, fn: last)
|> sum()
From Grafana i get the Error:
Data is missing a time field
Table looks like this:
_measurement _field _value _start _stop
grant2
December 12, 2022, 10:33am
4
What do you see when you switch to Table view? (toggle at top of screen)
example from my data:
I see the right Data in Table View about 500 Watthours, but i think the “TimeSeries Vizualisition” need the Time Data from the InfluxDB2 to sort the data.
If i set the Visualisition to “Bar Gauge” it shows the right value.
Only on Time Series there is the Error: Data is missing a time field
yosiasz
December 12, 2022, 11:48am
6
Maybe your datetime field is string and not date time type.
Can you try using transformation and conver that column to time?
I have tried it in InfluxDB2.5 UI and if i use
sum()
I don´t have a Timestamp for it, because it´s a summary of all that have no individual Timestamp.
I have to Import the Timestamp of a single Last value and set it for this “sum()”
But how can i do this
1 Like
I have done it, but have more Questions about the Range:
from(bucket: "bucket1")
|> range(start:-1mo)
|> filter(fn: (r) => r["_measurement"] == "mypower")
|> filter(fn: (r) => r["_field"] == "todaykwh")
|> aggregateWindow(every: 1d, fn: last, createEmpty: false)
|> sum(column: "_value")
|> group (columns: ["_time", "sum"])
|> yield(name: "_value")
But now I have a additional problem. How can I adjust the “range start:” so that this uses the last date of the query
an approximate example:
start_time= _time from query above
|> range(start: start_time())
I hope I was able to explain it roughly
yosiasz
December 13, 2022, 6:06pm
9
What do you mean by last date of the query?
That´s how my Power Meter collects the Data.
I only need the last values of each day summarized to 30days
And visualized as one Bar Graph for 30days or 1 Month
That´s my goal
If i use sum() all of the Data in example Picture is summarized e.g for the “1day in Picture” = 15 but i only need the Data of the last value “5” and for the next day the “7” collected all over the 30days in a Bar Graph
1 Like
yosiasz
December 13, 2022, 7:29pm
11
is there a timestamp associated with 5 & 7?
last(column: "_time")
before group
Yes there is a timestamp associated with 5 and 7
I try it tomorrow
1 Like
last(column: "_time")
works for the latest Data
I have to summarize it with:
|> sum(column: "_value")
Then i have the right value for 1 month, but how to show this in a Bar Graph every month with every month value ?
1 Like
codac
December 19, 2022, 10:37am
14
Hey @starfoxfs : So how does the whole code look like, so far?
Hi @codac ,
here is my latest code:
from(bucket: "bucket1")
|> range(start: -1mo, stop: now())
|> filter(fn: (r) => r["_measurement"] == "power")
|> filter(fn: (r) => r["_field"] == "todaykwh")
|> aggregateWindow(every: 24h, fn: last, createEmpty: false)
|> sum(column: "_value")
|> duplicate(column: "_start", as: "_time")
|> aggregateWindow(every: 1mo, fn: last, period: 1mo)
This works, but I don’t know if a new bar will be created after this month, I hope so.
This is my latest code and it works perfectly (for anyone looking for something similar):
from(bucket: "bucket")
|> range(start: -1mo, stop: now())
|> filter(fn: (r) => r["_measurement"] == "mymeasurement")
|> filter(fn: (r) => r["_field"] == "myfield")
|> aggregateWindow(every: 24h, fn: last, createEmpty: false)
|> truncateTimeColumn(unit: 1m)
|> aggregateWindow(every: 1mo, fn: sum, createEmpty: false)
|> timeShift(duration: -1d)
3 Likes
So helpful, thank you!
I have data aggregates already at the 1 day level and the following worked fine for me (createEmpty: true to create buckets for time periods that don’t have data). Your solution is good to have seen and thought about but I think my use case is probably more normal so for the copy paste folks out there…
Be sure your connection is setup to use Flux and not InfluxQL in grafana
from(bucket: "mybucket")
|> range(start: -3y, stop: now())
|> filter(fn: (r) => r["_measurement"] == "mymeasurement")
|> filter(fn: (r) => r["_field"] == "myfield")
|> aggregateWindow(every: 1mo, fn: sum, createEmpty: true)
type or paste code here
But if anyone else is a beginner, I also wanted to reference how to change the time units to a custom format as you have done – that was not obvious to me at all. Check this forum link https://community.grafana.com/t/how-to-filter-day-moth-year-in-axis/78286/4
