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)
2 Likes
