This is the forum archive of Homey. For more information about Homey, visit the Official Homey website.

The Homey Community has been moved to https://community.athom.com.

This forum is now read-only for archive purposes.

MySQL power device

RebtorRebtor Member
edited May 2017 in Apps
MySQL power device
Today i wrote a little app that can present energy readings and powermeter readings from a SQL database in homey. I use this app to get data from my smart meter and solarpanels to homey.

The app can be found here: https://github.com/rebtor/nl.rebtor.sql

After setting up the connection with the MySQL server homey will poll the server every x seconds for new data.


The data will be presented as a device.

can be used in a flow



Or used in a graph.

Just sharing this app here because it provides a solution for me for now. I'm sure there are people far more qualified to make a proper MySQL integration for homey. But for me this does the trick for now.


Comments

  • B3rtB3rt Member
    maybe an idea to use this app for only mysql connections?

    In settings you only specify the remote login of the mysql server and the query you do in flow cards.

    So make an option to create/update/delete/insert records in a remote mysql database.

    Could look something like this Flow card:

    IF:
    $result = "SELECT myrow FROM MyTable WHERE id='something'"


    AND:
    $result = x

    THEN
    do this (activate flow)
    of
    "UPDATE myrow SET row='new (or cards)' WHERE id ='something'

    I think this app could be very usefull if you can use cards with yoru own queries, would be great to use this to log remotely into a db, or use this the sameway as betterlogic but restore it in a (external) mysql db which also can be updated from other devices.

    The results can be in plain values or json, this can be used in other flow cards as well!


  • Nice but a krat bier naar keus voor je,i have a Davis Advantage Pro Wireless weather station,every minute it stores his readings in a mysql database,could you make an app that represents on the same way you did with the app you made
    you will help a lot of Davis lovers to make it possible to connect homey to the Davis
  • satboppersatbopper Member
    edited June 2017
    ps Advantage must be Vantage hi and a copy of the database,no problem for me to give
  • RebtorRebtor Member
    Sorry voor the late response. Busy times at work and my wife and i expect a child within 2 weeks so busy but in a good way. :)

    @satbopper Its not that mutch work to add a Weather station sql device. what kind of measurements are you looking for? Just temp, humidity or do you need more? If you let me know i will see if i can put something together later this week.


    @B3rt ; I like that idea i need something like that for my own logging also. The thing is that its not that hard for me to get a value from a database and place it in a value within a virtual homey device. I just haven't done a lot of flow stuf yet and haven't figured out how to put a homey variable(s) within a query that can be defined by a user.

    I will look into this for sure (if someone does not beat me to it) but first this child has to be born and things have to calm down a little at work.
  • B3rtB3rt Member
    Rebtor said:
    Sorry voor the late response. Busy times at work and my wife and i expect a child within 2 weeks so busy but in a good way. :)

    congrats!
  • I'm working on a app which can export Insights to an MySQL database.
    I'm planning on having the user select the data which the user would like to export and an action card to do the actual export.
  • B3rt said:
    Rebtor said:
    Sorry voor the late response. Busy times at work and my wife and i expect a child within 2 weeks so busy but in a good way. :)

    congrats!
    Thanks!

    @RobinVanKekem. I'm looking forward tot that.

    I tried making a card that could write device parameters tot a database. The SQL part is easy. But building An interface to acces the data is still a challange foto me
  • Are you on Slack @Rebtor?
    If you join the developer channel you can ask questions to most of the major developers.
  • @Rebtor ;
    Yesterday I've implemented your solution for reading the P1 interface. My situation is dat through a RJ11 cable to USB I read out the P1 port on my NAS and send this to a MySQL database (MariaDB). Therefor this works great and I really like this app!

    The only two things I miss in this app, which seem to be added quit simply I suppose, are the following:
    1. I have a meter with two rates. In my database there is a record with says high or low rate. Is it possible to add this?
    2. Is it possible to also add gas for the current meaterreading, without a 'power' value and maybe a different logo?



  • Today i released version 2.0.0 of the mysql app to beta branch. I'm sure there will be some bugs but its running stable here for 48 hours here. 

    Besides of the implementation of SDK2 i added support the gasmeter and weather device.

    in the query use:
    Power device: power and meter
    gas device: meter
    weather device: temp and humidity

    Gas only has a meter value as homey does not support current usage.
  • CarosCaros Member
    @Rebtor, great work thanks! I've just updated, seem to work stable. If I'll find any issues, I'll let you know.

    Regarding to the Gas meter: This sounds like a decent implementation, since the Gasmeters only sends his meter value twice an hour. The power meter sends the meter and power every 10 seconds.
  • RobinVanKekemRobinVanKekem Member
    edited March 2018
    Rebtor said:
    Today i released version 2.0.0 of the mysql app to beta branch. I'm sure there will be some bugs but its running stable here for 48 hours here. 

    Besides of the implementation of SDK2 i added support the gasmeter and weather device.

    in the query use:
    Power device: power and meter
    gas device: meter
    weather device: temp and humidity

    Gas only has a meter value as homey does not support current usage.
    https://github.com/athombv/node-homey-lib/issues/82
    I've talked to Emile and there wasn't any specific reason for this capability not to be there so he asked me to create an issue for it.
    Hope they implement it rather soon.

  • https://github.com/athombv/node-homey-lib/issues/82
    I've talked to Emile and there wasn't any specific reason for this capability not to be there so he asked me to create an issue for it.
    Hope they implement it rather soon.
    Well like Caros said, the gas meter only transmits his usage every 2 hours. So filling a current usage will be tricky.
  • luccie_007luccie_007 Member
    edited March 2018
    Kun je een voorbeeld (of jouw) SQL string hier even kopieren?

    Ik krijg het niet aan de gang.
    Maar aanpassen moet zeker lukken


    EDIT:
    Gelukt
    Power:
    SELECT p1_current_power_in as power, (p1_meterreading_in_1 + p1_meterreading_in_2) as meter FROM p1_log ORDER BY p1_log.p1_timestamp DESC

    Gas:
    SELECT p1_channel_1_meterreading as meter FROM p1_log ORDER BY p1_log.p1_timestamp DESC

    Bedankt voor de app,

    Is het mogelijk dat je bij de power meter intern meerdere tellers maakt?
    Laag tarief kwh
    hoog tarief kwh
    laag tarief teruglevering kwh
    hoog tarief kwh
    current power usage
    Total power usage  ( die je dan zelf can configureren met (1+2) - (1+2) of 1+2 als je niet terugleverd.


Sign In or Register to comment.