Read Write from/to MySQL Database!?

Tags: #<Tag:0x00007fadf86b4c68> #<Tag:0x00007fadf86b4b28> #<Tag:0x00007fadf86b49e8>

(John Doe) #1

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):
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?

(Oliver Stenzel) #2

I think for this use type you would be better of using InFlux DB:

(John Doe) #3

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

(Oliver Stenzel) #4

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

(John Doe) #5

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:


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).

(Angelos) #6

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)

(John Doe) #7

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

(Oliver Stenzel) #8

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:

       item MySensor recieved update
     if (MySensor.value==<add your farris meter calculation here>){

then, with grafana or another tool of choice you can visualise the changes in PowerConsumption.