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:
- aggregate the sensors’ data
- define SUM(), AVG(), … for a given sensor (temperature would be AVG, consumption SUM, …)
- use the aggregated data
- 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:
- define start and end date
- SELECT the existing timestamps within an interval
- INSERT SUM or AVG of that interval in a new table
- repeat until end date is reached
- 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?