# Aggregate and export persistence in intervalls

**URL:** <https://community.openhab.org/t/aggregate-and-export-persistence-in-intervalls/125660>\
**Category:** Persistence Services\
**Tags:** mysql, persistence\
**Created:** [August 19, 2021, 7:12am UTC](https://community.openhab.org/t/aggregate-and-export-persistence-in-intervalls/125660 "2021-08-19T07:12:53Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![binderth](https://community.openhab.org/user_avatar/community.openhab.org/binderth/32/56257_2.png) [@binderth](https://community.openhab.org/u/binderth)\
**Post date:** [August 19, 2021, 7:12am UTC](https://community.openhab.org/t/aggregate-and-export-persistence-in-intervalls/125660/1 "2021-08-19T07:12:53Z")

</div>

I persist most of my items in MySQL Database - and **every** sensor data. Most sensors don’t have a given interval, but fuzzy timestamps and sometimes the intervals are rapid.

So my use case is the following:

1. aggregate the sensors’ data
2. define SUM(), AVG(), … for a given sensor (temperature would be AVG, consumption SUM, …)
3. use the aggregated data
4. _even “compact” old data that way for the item’s persistence_

I know, that’s not really a OH3-core question, but a MySQL or script question.  
What I did right now in a python script:

1. define start and end date
2. SELECT the existing timestamps within an interval
3. INSERT SUM or AVG of that interval in a new table
4. repeat until end date is reached

but:

- slow as hell
- I tried it with my smartmeter persistence and sometimes I get negative consumptions?

Is there a more elegant way to do that? like in pure MySQL functions or is there already a “compact/aggregate” function in OH3 for that, that I didn’t see?

---

<div class="post-metadata">

**Author:** ![mstormi](https://community.openhab.org/user_avatar/community.openhab.org/mstormi/32/55857_2.png) [@mstormi](https://community.openhab.org/u/mstormi)\
**Post date:** [August 19, 2021, 7:44am UTC](https://community.openhab.org/t/aggregate-and-export-persistence-in-intervalls/125660/2 "2021-08-19T07:44:31Z")

</div>

> [@binderth](#):
>
> is there already a “compact/aggregate” function in OH3 for that, that I didn’t see?

No that is a DB task.  
What you _can_ do is to calculate the average yourself.  
Introduce ‘average’ virtual items, call them from cron every minute and calculate `<original item>.averageSince(now.minusMinutes(1))`, then remove persistence of the original items.  
This is also quite useful when you want to act on time-averaged sensor value rather than single value  
Think a lux sensor you use to close/reopen your blinds, on a cloudy day you don’t want it to act upon every change.

Or consider moving to rrd4j it offers aggregation per default (you can apply some tweaking to intervals).

---

<div class="post-metadata">

**Author:** ![binderth](https://community.openhab.org/user_avatar/community.openhab.org/binderth/32/56257_2.png) [@binderth](https://community.openhab.org/u/binderth)\
**Post date:** [August 19, 2021, 7:50am UTC](https://community.openhab.org/t/aggregate-and-export-persistence-in-intervalls/125660/3 "2021-08-19T07:50:15Z")

</div>

The most immanent use case is to have aggregated 1min intervals of my smartmeter for calculating my planned Photovoltaic. 😉  
So I’m dependend on existing data, but for the future, the proxy item with an average would be worth considering. So I’ll have to dig deeper into MySQL functions…

---

<div class="post-metadata">

**Author:** ![mstormi](https://community.openhab.org/user_avatar/community.openhab.org/mstormi/32/55857_2.png) [@mstormi](https://community.openhab.org/u/mstormi)\
**Post date:** [August 19, 2021, 9:11am UTC](https://community.openhab.org/t/aggregate-and-export-persistence-in-intervalls/125660/4 "2021-08-19T09:11:00Z")

</div>

> [@binderth](#):
>
> The most immanent use case is to have aggregated 1min intervals of my smartmeter for calculating my planned Photovoltaic.

?? You must not dimension your PV based on consumption, that’ll lead to suboptimal results  
(best possible advice is: put as many panels onto your roof as possible).  
For battery size maybe ok but that you don’t need 1 min intervals for.

For the future, why not get [my EMS](https://storm.house/energiemanagement-system/). It uses right that, proxy items to average PV yield, consumption etc. And it does all the optimization computations in real time, and much more.

---

<div class="post-metadata">

**Author:** ![binderth](https://community.openhab.org/user_avatar/community.openhab.org/binderth/32/56257_2.png) [@binderth](https://community.openhab.org/u/binderth)\
**Post date:** [August 19, 2021, 9:19am UTC](https://community.openhab.org/t/aggregate-and-export-persistence-in-intervalls/125660/5 "2021-08-19T09:19:41Z")

</div>

yes, it’s for calculating the battery size. and of course I’ll put as much modules up there as possible! 😉

> [@mstormi](#):
>
> For the future, why not get [my EMS](https://storm.house/energiemanagement-system/). It uses right that, proxy items to average PV yield, consumption etc. And it does all the optimization computations in real time, and much more.

yes, that’s top of the list after ordering the PV system! 😉

---

<div class="post-metadata">

**Author:** ![mstormi](https://community.openhab.org/user_avatar/community.openhab.org/mstormi/32/55857_2.png) [@mstormi](https://community.openhab.org/u/mstormi)\
**Post date:** [August 19, 2021, 9:25am UTC](https://community.openhab.org/t/aggregate-and-export-persistence-in-intervalls/125660/6 "2021-08-19T09:25:41Z")

</div>

> [@binderth](#):
>
> yes, it’s for calculating the battery size. and of course I’ll put as much modules up there as possible! 😉

I’ve even put some modules on top of my northern side. It’s borderline but worth considering.

---

<div class="post-metadata">

**Author:** ![binderth](https://community.openhab.org/user_avatar/community.openhab.org/binderth/32/56257_2.png) [@binderth](https://community.openhab.org/u/binderth)\
**Post date:** [August 19, 2021, 9:27am UTC](https://community.openhab.org/t/aggregate-and-export-persistence-in-intervalls/125660/7 "2021-08-19T09:27:46Z")

</div>

> [@mstormi](#):
>
> I’ve even put some modules on top of my northern side. It’s borderline but worth considering.

I’ll do that. 28° slope allows for a reasonable yield there also. and also I do have solarthermic modules on the south side already, so there’s not THAT much space there anymore.

---

<div class="post-metadata">

**Author:** ![rlkoshak](https://community.openhab.org/user_avatar/community.openhab.org/rlkoshak/32/75251_2.png) [@rlkoshak](https://community.openhab.org/u/rlkoshak)\
**Post date:** [August 19, 2021, 2:18pm UTC](https://community.openhab.org/t/aggregate-and-export-persistence-in-intervalls/125660/8 "2021-08-19T14:18:40Z")

</div>

> [@mstormi](#):
>
> Introduce ‘average’ virtual items, call them from cron every minute and calculate `<original item>.averageSince(now.minusMinutes(1))`, then remove persistence of the original items.

You can’t remove persistence on the original Items or it will break the call to `averageSince()`. In this case you could save the original Items to RRD4J which won’t grow too big and lets you calculate the average like this and only save the virtual Items to MySQL. But then, as you say RRD4J offers aggregation by default so maybe it’s worth moving over entirely.

> [@binderth](#):
>
> slow as hell

If this is a one time operation does that really matter? Once you’ve processed all your data and created the new tables you are done and never have to run the tool again, or at least need to run it rarely.

I can’t help with the negative values except to say look in your data for negative entries. It’s either the data or somehow adding numbers is broken in some way.

---

<div class="post-metadata">

**Author:** ![binderth](https://community.openhab.org/user_avatar/community.openhab.org/binderth/32/56257_2.png) [@binderth](https://community.openhab.org/u/binderth)\
**Post date:** [August 19, 2021, 2:41pm UTC](https://community.openhab.org/t/aggregate-and-export-persistence-in-intervalls/125660/9 "2021-08-19T14:41:26Z")

</div>

> [@rlkoshak](#):
>
> If this is a one time operation does that really matter? Once you’ve processed all your data and created the new tables you are done and never have to run the tool again, or at least need to run it rarely.
> 
> I can’t help with the negative values except to say look in your data for negative entries. It’s either the data or somehow adding numbers is broken in some way.

you’re right. for a one-off it should work with the slow. What makes me wonder, my script relies on the smartmeter read-outs - and I don’t think my provider would like it to have negative consumption (without me having PV right now! 😉 )…

But perhaps I have some logic error here (python):

```auto
start = (2020, 8, 1, 0, 0, 0, 0, 0, 0)
end = (2021, 8, 1, 0, 0, 0, 0, 0, 0)
reading = 50839.0817
intervalMinutes = 1

start_date = int(time.mktime(start))
end_date = int(time.mktime(end))
current_date = start_date

while (current_date < end_date):
    current_datetime = str(datetime.fromtimestamp(current_date ))
    queryreading = ('SELECT * FROM openHAB.item0303 WHERE time > STR_TO_DATE("' + current_datetime + '", "%Y-%m-%d %H:%i:%s.%f") ORDER by time asc LIMIT 1')
    print queryreading

    cur.execute(queryreading)
    rowsreading = cur.fetchone()
    current_reading = rowsreading[1]

    consumption = current_reading - reading 
    reading = current_reading
    
    insertconsumption = ('INSERT INTO `openHAB`.`consumption_' + str(intervalMinutes) + '` (`time`, `value`) VALUES (STR_TO_DATE("' + str(datetime.fromtimestamp(current_date)) + '", "%Y-%m-%d %H:%i:%s.%f"), "' + str(consumption) + '")')
    print insertconsumption 

    cur2.execute(insertconsumption)
    con2.commit()
    
    current_date = current_date + (intervalMinutes * 60)

```

from what I see, there should not be something like negative “insertconsumptions”, but the overall consumption over one year is correct… 🤷‍♂️

---

<div class="post-metadata">

**Author:** ![rlkoshak](https://community.openhab.org/user_avatar/community.openhab.org/rlkoshak/32/75251_2.png) [@rlkoshak](https://community.openhab.org/u/rlkoshak)\
**Post date:** [August 19, 2021, 3:00pm UTC](https://community.openhab.org/t/aggregate-and-export-persistence-in-intervalls/125660/10 "2021-08-19T15:00:46Z")

</div>

> [@binderth](#):
>
> What makes me wonder, my script relies on the smartmeter read-outs - and I don’t think my provider would like it to have negative consumption

But this assumes that what ever API or scraping you are doing to get the read-outs is without error and not reporting negative numbers. Looking to see if you actually have negative numbers in the DB can prove that at least one problem is with the source data or whether it can only be a logic error.

> [@binderth](#):
>
> `consumption = current_reading - reading `

OK so this isn’t just summing up the values but subtracting. That changes things and makes it more likely that the there is a logic error that stems from an assumption about the readings.

current\_reading is initial-zed to the second result returned by the SQL query (the first entry in an array is 0). Then you subtract 50839.0817 for every hour between the current date and the end date.

What’s 50839.0817? Is the assumption that current\_reading \> (number of hours between start time and end time \* 50839.0817) correct for all end\_dates? The negative values seem to indicate the answer is no. At least some of the time that assumption is false resulting in negative numbers.

---

<div class="post-metadata">

**Author:** ![binderth](https://community.openhab.org/user_avatar/community.openhab.org/binderth/32/56257_2.png) [@binderth](https://community.openhab.org/u/binderth)\
**Post date:** [August 19, 2021, 3:13pm UTC](https://community.openhab.org/t/aggregate-and-export-persistence-in-intervalls/125660/11 "2021-08-19T15:13:59Z")

</div>

> [@rlkoshak](#):
>
> current\_reading is initial-zed to the second result returned by the SQL query (the first entry in an array is 0). Then you subtract 50839.0817 for every hour between the current date and the end date.

```auto
start = (2020, 8, 1, 0, 0, 0, 0, 0, 0)
=> the reading on that date (2020-08-01) was:
reading = 50839.0817

```

so, from that point on, it should go like this:

1. Query `current_reading` from the `current_date` timestamp
2. then substract `reading` from `current_reading` =\> that should be the consumption of the `intervalMinutes`=\> write that away
3. give `reading` the `current_reading` value
4. move `current_date` to the next interval
5. repeat 1-4 until we reached the end-datetime.

but, of course:

> [@rlkoshak](#):
>
> But this assumes that what ever API or scraping you are doing to get the read-outs is without error and not reporting negative numbers. Looking to see if you actually have negative numbers in the DB can prove that at least one problem is with the source data or whether it can only be a logic error.

I use the “optical” readout from my smartmeter, which then is going through USB to my OH3, which uses the “smartmeter”-binding to update my item0303! 😉  
It _could_ be, however unlikely, that there’s some non linear read-out and some later timestamped value of item0303 is in fact lower than the timestamped value before…?