I started to build a power consumption measurement sensor following this project (german, how to sense a reflective disk of the Ferraris meter): https://www.kompf.de/tech/emeir.html
Everything worked fine and the sensor is communicating with openhab2.
The problem I face now is: how do I record the collected data / save it to a Database so I can use it to track my KWh power consumption.
First I thought I just need to setup MySQL persistence in openhab2, but that was the wrong approach I think.
Then I thought, well now that I have a MySQL Database, just let the sensor write into the database and openhab2 just needs to connect to the db and read the values. But my investigations tell me that openhab2 does not offer such a solution.
So now I am stuck and I need help from you guys. Any idea how I can reach my goal: Show the actual power consumption per month in openhab2?
thanks for the link, but I don´t see why this is a better choice for my use case. I see one can just build diagrams, but I still face the problem that I cannot query an item to read from that database.
So what I initially wanted to have is a functionality like this:
demot.items:
Number PowerConsumption (gBasement) {some code like: sql query SELECT PowerConsumption FROM ITEM1}
So PowerConsumption should show the value of which is stored in the database PowerConsumption table.
Another thing I would have to do (I thought I could do this by a rule) is to automatically add +1 to the database PowerConsumption when then sensor has read 75 rounds of the farraris meter (75 rounds = 1 KWh).
One way forward would be to “attack” the problem in a similar fashion using the exec binding.
Build 3 components in your OH2 deployment:
(1) an Item bound to the exec binding (command),
(2) an external script that executes a SQL query to your MySQL database and returns the value to the exec bound item which in turn will update the Item state &
(3) a rule that triggers Item updates on a cron schedule (or use the exec interval option) and maybe does more advanced calculations (or use the external exec script to do advanced calcs)
But you don’t need an item ot any SQL statements to query your db.
You have your PowerConsumption Item, who’s states (values) are automatically saved in the persistence DB with timestamp whenever the item’s value changes.
Through a rule you can update the items values, maybe like so:
when
item MySensor recieved update
then
if (MySensor.value==<add your farris meter calculation here>){
PowerConsumption=PowerConsumption+1
}
end
then, with grafana or another tool of choice you can visualise the changes in PowerConsumption.
eval "$(date +'today=%F')"
query="select 0.001*24*sum(value)/count(value) from pvPower where time > '$today' - 1d and time <= '$today'"
echo $query
influx -database 'enviro' -execute "$query"
Get the date, which is the datetime of midnight, starting today.
build a query which adds all the milliwatt readings for 1 day before midnight to midnight, and divides by the number of readings. Assuming the samples are frequent and regular, this is the average power for yesterday. Multiply by 24 hours and divide by 1000 to get watt-hours.
query influx. Getting the quotes correct took some effort.
The result right now was:
$ eval "$(date +'today=%F')"
$ query="select 0.001*24*sum(value)/count(value) from pvPower where time > '$today' - 1d and time <= '$today'"
$ echo $query
select 0.001*24*sum(value)/count(value) from pvPower where time > '2018-12-07' - 1d and time <= '2018-12-07'
$ influx -database 'enviro' -execute "$query"
name: pvPower
time sum_count
---- ---------
1544054400000000001 0.6680972897845726
Which looks right. https://imgur.com/btSGYsQ
This is a small instrument connected to a ~1 watt PV panel. The running total for today
select 0.001*24*sum(value)/count(value) from pvPower where time > '$today' - 1d and time <= now()
