Read Write from/to MySQL Database!?

Tags: #<Tag:0x00007f61704efd80> #<Tag:0x00007f61704efbc8> #<Tag:0x00007f61704efa60>

Hi all,

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?

I think for this use type you would be better of using InFlux DB:
http://docs.openhab.org/addons/persistence/influxdb/readme.html

mmh, looks interesting, but again, how would I configure my items so they start a query from the database?

Searching this forum for “InfluxDB” would be a good start:

1 Like

Hi @Oli,

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)

That´s a nice approach and I will test this (not sure how, but I will google a bit…), thanks for this idea!

1 Like

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.

This works in bash, querying influxdb

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"
  1. Get the date, which is the datetime of midnight, starting today.
  2. 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.
  3. 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()