Hi,
I want to have an overview how much electricity i use each therefore i want following :
Be able to see hourly for 24h graph how much has been used
Be able to dig into the hour and see by minnute.
Be able to see daily totals by day, month, year.
I get my data from Home assistant and i run influxdb 2.0 but i don’t know exactly how to aggregate data and what is the best practice to do what i want.
and i would expect to see the difference between the first number 20090.51 and 20090.63 but the numbers don’t add up, i get incorrect value. I should get 0,12 but i get 0.065. I have a lot of data not shown above, above is just a small portion of it. I would normally do a 24h/1d window. I tried mean/sum/count but no matter what i never get the right result. What am i doing wrong ?
And i get a Min, Max and range but how can i make it so i only get range so i can show it in a graph? Also i ges if i change the range at the start i get the usage each hour, days, week etc ?
@Harald_Tillmanns Thank you very much, what i meant was i want to have one a graph on the X axis time (the first time in the range) and on Y i want the value e.g range. Is that possible ?
In the end i want to have a graph showing each hour the usage and then one graph showing each week, maybe one showing the usage at night (between a time range) all coming from the same raw data.
Update:
I am guessing i somehow need the aggregateWindow command merged into the solution you made so i can choose a range then use aggregateWindow to get number each e.g hour/day/etc ? I am not sure.
Hello,
okay hope to understand your problem now. What you need is windowing before. Here is an example with my test data:
from(bucket: “veo”)
|> range(start: -1h)
|> filter(fn: ( r ) => r["_measurement"] == “procdata”)
|> filter(fn: ( r ) => r["_field"] == “GT1_TNH”)
|> window(every: 5m)
|> reduce(fn: (r,
accumulator) => ({
min: if r._value < accumulator.min then r._value else accumulator.min,
max: if r._value > accumulator.max then r._value else accumulator.max
}),
identity: {min: 10000000000.0, max: -10000000000.0})
|> map(fn: ( r ) => ({ r with range: r.max - r.min }))
|> duplicate(column: “_stop”, as: “_time”)
|> window(every: inf)
|> drop(columns: ["_start", “_stop”, “min”, “max”, “_measurement”, “_field”])
The duplicate column is necessary to make the unwindowing window(every: inf) work.
@Harald_Tillmanns thank you! that did indeed do the trick. It’s correct now and exactly what i needed. Last question, how can i filter some outliers for example i have some that give enormous values because i get some errors in the data once in a while so i want to filter out values that are below 0 and values above for example 100. The value i want to filter out is the new range calculated if possible if not directly on the _value is also fine, same result i guess.
Hi,
i haven’t tested it but i think you would reach your goal with something like
|> filter(fn: ( r ) => r[“range”] > 0.0 and r[“range”] < 100.0)
at the end of the pipe.
@Harald_Tillmanns Indeed it does work however i moved it to filter out before i do the windowing so i do it on the raw data to filter out as little as possible. I tried to use the filter to take out data from a whole data but it seems i don’t have the syntax correct : |> filter(fn: (r) => r["_time"] != "2020-11-20")
How is that done ? I find the documentation quite sparse on many of these subjects.
Hello,
i think the DateTime constant is the problem. Look into the documentation or try something
like “2018-01-01T00:00:00Z”. The next problem is that “2020-11-20” means “2020-11-20T00:00:00Z”
that is a time point and not a range
r["_time"] < “2020-11-20T00:00:00Z” and r["_time"] > “2020-11-20T23:59:59Z” is better.
