Jdbc MySQL wrong results AverageSince

I have some rules using averagesince. In my case to get the average of last 7 days in percent of the time a Relais was switched to on.

At least I have got values allover the period, it works fine inclusive arithmetic calculation. But if relevant values out of period, I only got garbage.

Example: averageSince(now.MinusDays(7)):

Now-7 days: 0
Now-1 day: 1

Results in 14% so correct

If one day later values are
Now-8 days: 0
Now-2 days: 1

Results in 100%, because the first 5 days (now-7 to now-2 with value 0) seems to be out of calculation, so only those two days are used.

A workaround is to persist not on change but e.g. every minute. But that will cause unneeded storage.

Maybe this could be solved in openHAB directly?

Using binding jdbc.mysql

Can somebody confirm this behavior?

What you get from the persistence extensions depends rather on Item type … logic says a Switch type is binary and has no “average position”, it is either on or off. I don’t know if that relates to your results, I understand what you are trying to do, get the duty cycle.

I’m not quite sure what data you are showing us, results from historicState()? Log out the timestamps of returns from that,might give a better understanding.
historicState() will never exactly hit a record timestamp, so it should reach back into the past to find the last previous record, and assume that is still valid at “requested instant”.

We’d expect averageSince to do something similar for the same reasons - but averageSince is a time-weighted average, so there must be some complicated maths going on with that oldest record that might be going wrong.

The data was just an example for the problem, because there are a lot of more state changes, would give a long list.

I also had a twin item as number stores 1 (if value changes to on) and 0 (if switch changes to off).

But there same problem.

I had a heating Relais had been off for more than 14 days, then had been on a few hours. Next midnight (averagesince (14d) stores percentage via a rule) shows 28% in my case. But that result was only the last day, because the older off state wasn’t taken into account.

So normally it should take last state before 14 days and the time weighted off from 14 days ago until getting on should be part of averagesince.

I will now persist minutely then it will work.

But think this could be optimized

Okay, not enough info to do more. Be really sure you are asking for jdbc data in your rules, and not getting rrd4j by mistake.

Here what’s stored in MySQL

Test item:
2022-04-12 20:23:14.000 | 0.0
2022-04-14 20:37:58.520 | 1.0

Average since 1 day with rule:
TestA item:
2022-04-14 20:40:00.210 | 1.0

With 23h 58 minutes on 0.0 and
2 minutes 1.0 averagesince should be something under 1 percent

Some later:

2022-04-12 20:23:14.000 | 0.0

2022-04-14 20:37:58.520 | 1.0

2022-04-14 20:49:38.056 | 0.0

Average since 1 day

2022-04-14 20:40:00.210 | 1.0 ==> should be about 0.00138

2022-04-14 20:50:00.204 | 0.4846566770405802 ==> should be about 0.008333

2022-04-14 20:55:00.205 | 0.342344934779183 ==> should be some lower than 0.008333 because 6 further minutes with value 0

AFAIR there is also weighted average option in persistence extensions which should take into account time for which given state was active (stored).

Looking in the docs, averageSince is time weighted. But only time weight the stored values set later then since. So if you have a longer time without persisted values you got garbage.

If persist is set to every minute: averageSince works fine with small failure tolerance, because less 1 minute is not important if you want to have average for longer times.

But with persist on change only, long periods are sometimes not included.

So, it’s true that averageSince() can (and should !) reach a long way into the past for the “first/oldest” value, but then messes up the time weighting? i.e. it should assume to weight that one value from the requested timestamp, and not from the real record timestamp?

That would solve the issue I think