I’m currently experiencing the following problem.
I’m writing the current rainfall in an infludb from the openweathermap binding. Every everyMinute is written to the DB.
I want to query the amount of rain in the last 24h in a rule like this:
rule "xyz"
when
Time cron "0 0/1 * 1/1 * ? *"
then
var sum = LocalWeatherAndForecast_Current_Regen.sumSince(now.minusHours(24), "influxdb")
logInfo(sum.toString)
end
Unfortunately a wrong value comes out here.
Over Grafana I get the correct rain amount in 24h with this querry: SELECT sum("value") FROM "LocalWeatherAndForecast_Current_Regen" WHERE time >= now() - 24h GROUP BY time(15m) fill(none)"
You haven’t given your Item definition, but I will guess it is a Number:Length type.
openHAB persistence service is effectively a version 1.x legacy still, and does not properly handle units of measurement.
What gets stored (and read from rules) is only the numeric part of your Item state. Any units are lost.
You’ll have to take into consideration what the weather binding stores into your Item state. What does your events.log tell you for that?
What is getting stored to your persistence? REST API query will tell you that.
Really sure about your units here? That’s barely measurable.
You are persisting the values every minute ( although the binding updates every 10 minutes), a querry over the last 24 houres would find 1440 values to sum up!
As shown above your grafana does the same in steps of 15 minutes, that results in a different outcome,
Additionally how could the .72 mm value be correct, looking at your chart you have 8 different non-zero values, ranging from .1 to .22, and they sum up to .72???
Good point. The Grafana value is going to be wrong too - guessing this is data from OpenWeatherMap, it reports “Rain volume of the last hour”. Recording that 4 times an hour will give a 4x inflated ‘sum’ etc.
Oh…
I didn’t notice that, of course.
I have now grouped the graph in Grafana per hour. The sum of the bars gives about 69, the number from the rule but I don’t think this is the right way
Well, I suppose it might still be helpful to know what your Item is getting updated to (see your events.log) so that we can guess what is getting stored in persistence.
Or you could still use the REST API to actually find out.
here you can see how the item is updated by the binding:
2020-07-28 07:38:54.525 [vent.ItemStateChangedEvent] - LocalWeatherAndForecast_Current_Regen changed from 0.0 mm to 0.22 mm
2020-07-28 08:39:07.468 [vent.ItemStateChangedEvent] - LocalWeatherAndForecast_Current_Regen changed from 0.22 mm to 0.19 mm
2020-07-28 09:19:14.495 [vent.ItemStateChangedEvent] - LocalWeatherAndForecast_Current_Regen changed from 0.19 mm to 0.21 mm
2020-07-28 09:39:15.830 [vent.ItemStateChangedEvent] - LocalWeatherAndForecast_Current_Regen changed from 0.21 mm to 0.15 mm
2020-07-28 10:39:20.719 [vent.ItemStateChangedEvent] - LocalWeatherAndForecast_Current_Regen changed from 0.15 mm to 0.0 mm
2020-07-28 16:39:57.715 [vent.ItemStateChangedEvent] - LocalWeatherAndForecast_Current_Regen changed from 0.0 mm to 0.23 mm
2020-07-28 17:30:03.318 [vent.ItemStateChangedEvent] - LocalWeatherAndForecast_Current_Regen changed from 0.23 mm to 0.16 mm
2020-07-28 18:30:21.026 [vent.ItemStateChangedEvent] - LocalWeatherAndForecast_Current_Regen changed from 0.16 mm to 0.11 mm
2020-07-28 19:30:27.701 [vent.ItemStateChangedEvent] - LocalWeatherAndForecast_Current_Regen changed from 0.11 mm to 0.13 mm
2020-07-28 20:30:33.799 [vent.ItemStateChangedEvent] - LocalWeatherAndForecast_Current_Regen changed from 0.13 mm to 0.0 mm
the result of the rule can be seen here:
2020-07-28 20:21:01.416 [ome.event.ItemCommandEvent] - Item 'SumRainLast24h' received command 68.65
2020-07-28 20:21:01.419 [vent.ItemStateChangedEvent] - SumRainLast24h changed from 68.52 to 68.65
2020-07-28 20:22:01.492 [ome.event.ItemCommandEvent] - Item 'SumRainLast24h' received command 68.78
2020-07-28 20:22:01.493 [vent.ItemStateChangedEvent] - SumRainLast24h changed from 68.65 to 68.78
2020-07-28 20:23:00.452 [ome.event.ItemCommandEvent] - Item 'SumRainLast24h' received command 68.91
2020-07-28 20:23:00.454 [vent.ItemStateChangedEvent] - SumRainLast24h changed from 68.78 to 68.91
2020-07-28 20:24:00.614 [ome.event.ItemCommandEvent] - Item 'SumRainLast24h' received command 69.04
2020-07-28 20:24:00.616 [vent.ItemStateChangedEvent] - SumRainLast24h changed from 68.91 to 69.04
Okay, so Item updates every quarter hour.
Item value represents rainfall over previous hour.
Item value is given in mm.
Your persist config -
Item value will be recorded every minute.
Persistence will discard mm units, just store numeric value.
So in summary, you will store the hourly rate sixty times per hour.
Rule function LocalWeatherAndForecast_Current_Regen.sumSince(now.minusHours(24), "influxdb")
will do what it says, sum.
It will reach back over 24 hours and sum all records it finds.
In this case, that will be 60 x 24 records to be added together.
There won’t be any units, and you just have to “know”/remember that you stored mm values in the first place, but I don’t think that comes into play here so long as the weather service is consistent.
if you divide the sumSince() result by 60, you should get the daily total. That will effectively be a sum of an average previous-hour value across the day.
I don’t know what your Grafana function means, but suspect this 4x too high if it is sampling “previous hour” every fifteen minutes.
