How is javascript calculating persistence.averageSince

mtrax · June 20, 2026, 1:07am

I running this JavaScript to get the average energy for the last 2 days and I seem to get a different result when I query the persistent table for the same item.

how is javascript getting these averages?

eg

Energy_Mains_Daily = items.getItem("Energy_Mains_Daily");
let weekly_avg=Quantity(Energy_Mains_Daily.persistence.averageSince(time.toZDT().minusDays(2)).state);
console.log("Energy: 2 Weekly Avg= "+ weekly_avg );

outputs

Energy: 2 Weekly Avg= 32.4436316235035 kWh

however when I use SQL to calculate I get a slightly different result

SELECT AVG(value) 
from energy_mains_daily_44 
WHERE time >= NOW() - INTERVAL 2 DAY;

outputs

34.65151666666669

here are the last 3 days for this item in the database

2026-06-19 23:59:30.485 38.030100000000054
2026-06-18 23:59:30.486 31.272933333333324
2026-06-17 23:59:30.487 29.894916666666674

rlkoshak · June 20, 2026, 3:40am

The same as all the rules languages, by calling the persistence actions provided by OH core.

I suspect the difference is that OH calculated a time weighted average which takes the time between entries in the database into account.

The SQL average probably is a straight mean that does not take into account the time between entries.

Consider a case where a value is 1, them once minute later becomes 2, then one minute later becomes 10 for the next two hours. A time weighted average is going to be much closer to 10 than 4 because for the bulk of the time, the state was 10.

Nadahar · June 20, 2026, 5:59am

github.com/openhab/openhab-core

bundles/org.openhab.core.persistence/src/main/java/org/openhab/core/persistence/extensions/PersistenceExtensions.java

9dae5b102


      
          private static @Nullable State internalAverageBetween(Item item, @Nullable ZonedDateTime begin,
                  @Nullable ZonedDateTime end, @Nullable RiemannType type, @Nullable String serviceId) {
              String effectiveServiceId = serviceId == null ? getDefaultServiceId() : serviceId;
              if (effectiveServiceId == null) {
                  return null;
              }
              ZonedDateTime now = ZonedDateTime.now();
              ZonedDateTime beginTime = Objects.requireNonNullElse(begin, now);
              ZonedDateTime endTime = Objects.requireNonNullElse(end, now);
          
              if (beginTime.isEqual(endTime)) {
                  HistoricItem historicItem = internalPersistedState(item, beginTime, effectiveServiceId);
                  return historicItem != null ? historicItem.getState() : null;
              }
          
              Iterable<HistoricItem> result = getAllStatesBetweenWithBoundaries(item, begin, end, effectiveServiceId);
              if (result == null) {
                  return null;
              }
              Iterator<HistoricItem> it = result.iterator();

This file has been truncated. show original

mtrax · June 21, 2026, 1:24am

I’m not quiet understanding the code fragment ie the duration should be 2 days so should be equal ??

BigDecimal.valueOf(Duration.between(begin, end).toMillis());

Nadahar · June 21, 2026, 2:59am

I don’t understand what you’re saying, but my point by showing the code was to illustrate that it isn’t a “simple average”. There are quite a few things that the code deal with, to figure this out. What if there’s no start or end datapoint where you want the average to start or end? Do you project something then? The code also seems to try to differentiate between past and future, if the “range” you’re averaging somehow crosses the current time (future values would have to be predictions then, obviously). I also see that Riemann sums are used here, I assume it’s to “smooth the values” between the datapoints.

So, in short, it’s no wonder why the results aren’t always the same as a simple, mathematical average. I can’t answer why the calculation is done as it is, I can just state that it is. I assume that there are pretty good reasons for it.