I’m trying to figure out the best approach for storing and accessing historic energy consumption. My goal is to try to eliminate the need for Grafana and use the OH main UI widgets/Analyze items to get the insights I want but the more I investigate my options, the more I’m bouncing against limitations in OH.
I have a DSMR, this is a serial data logger which currently logs all energy usage (kwh) into different items. I have Gas and Electricity which is dividend into 4 separate values:
- Consumption - Peek
- Consumption - OffPeek
- Producing - Peek
- Producing - OffPeek
And I have a selection of Shelly PM devices gathering current power usage (w) of high consuming devices.
Currently my persistence persists the states every minute.
With Grafana you use queries to fetch and process the persisted data to your liking. And here’s where the first hurdle is within OH, you cant use queries. Nor the REST API, Persistence services and Custom widgets cant use them. in fact, the Label custom widget can only get the current state of an item, not a historical one.
Trying to get around this short come I build a iframe custom widget with a JS backed script which pulls data from the REST API persistence and does some basic calculation. This actually works very well for the DSMR energy usage, as I can pull the first record of this month and the current one and calculate the difference and voila. I can show this number in the iframe widget and takes just 2 calls to the REST API. But for the shelly’s, where I persists the current power usage (and not the hourly consumption) I need to pull all the persisted data and process it and calculate the usage. It works when calculating this for a few days, but when pulling weeks/months of data, it’s just to slow and you pull a lot of MB’s from the REST API. So this approach works for the DSMR, but just for calculating a number, and not for graphing.
To get around this problem I created separate items for day/week/month/year. Then I created Stored Procedures which processes the existing persisted data and fills those tables. I could use rules for this and build the historic data once with some queries, but just to prove the concept I created SP’s. I now have separate items which I can use in widgets and Analyze items. But for the DSMR I would like to do some calculations between the energy usage vs production to get the actual usage. This is currently not possible in Analyze items.
It just feels cumbersome as the data is already persisted in the single items, if OH would support queries at some areas it would make it so much more powerful and yet simplistic.
I’m very interested in how others handle this? Do you have any examples/rules/item structure to get your power usage insights?
And is queries something that will come to OH?