[APP][PRO] MySQL-Client

Another example:

My table “power_daily” - power usage with daily usage and meter:
grafik

First Flow to select the last meter value (last day), starting at new day (00:00):

Second flow: Read selected value, calculate gap to new meter value and store the two values (and date) as new line:

I have another one for hourly updates. Every time the same logic, but with time in addition:
grafik

1 Like

PS: I use a Windows client for DB changes. It’s much easier than phpMyAdmin on the NAS.

My Favorite:
https://dev.mysql.com/downloads/workbench/

Nice, too:

1 Like

Thank you for your help ! I still have a lot to learn and more to do on the subject.

Works:

Screenshot_20220412-192148_Homey
Something to play again…:innocent:

1 Like

Sorry, this might be a silly question, but how do i initially connect to a mysql db😔? After installing the mysql client app in homey, i wanted to create a flow, however when adding a card, i dont have the choice of the mysql object, neither in the ‘if’ nor in the ‘then’ section…thanks for your help!

After installation of the app you need to add an ‘device’ which connects to your DB

2 Likes

Thanks !

With test version 0.0.7 you can use a SQL query in flow conditions. Select one (!) field from your DB table and use the result (as number or text) in a flow condition. Compare numbers (greater or lower-equal; equal or not equal) or text (equal or not equal).

grafik

3 Likes

New test version 0.0.9:

  • Added a new flow condition for “SQL query was successful”.
  • Added capabilities/tokens for the quer result:
    grafik
    This capabilities will be set witch each query. You can use these capabilities as global token in your flow:
    grafik

Token:
grafik
grafik

2 Likes

When I use [date-1d] in my query it doesn’t give the date but only ‘2005’

INSERT INTO Power (date, power_usage) VALUES ([date-1d], value)

What is wrong?

I have to check if this calculation is affected by the changed zimezone handling. I assume you have upgraded to 7.4.x?

I running 7.3 , can this be the issue?

No, perhaps I inserted a bug with the 7.4 adaption. But with 7.3 I can test this issue and know where to start my search - have not upgraded yet :grin:

I’ll check it this evening and send you an update. Thanks for the hint.

1 Like

Too easy … it’s not a bug, but a misinterpretation of your field value by SQL.

You missed the quotes around your date value… See the example in post #2:
grafik

With quotes, the date is inserted as string in the format “2022-05-12” and the database is reading this as a valid date.
Without quotes, you will get 2022-05-12 and SQL is interpreting as numbers with substraction - and you get 2005 as result.

So please add quotes arounf the date and it should store the correct date.

Example for date usage: Date Functions in SQL Server and MySQL

1 Like

INSERT INTO Power (date, power_usage) VALUES (‘[date-1d]’ , value);

Give an error.
Error in SQL query: Unknown column “2022’ in ‘field list’

Without quotes no error but 2005 as result

Same result when using the date variable

There’s a difference between ` and ’

Please use simple single quotes, no apostrophe.
Double quotes should be ok, too. But don’t use special Unicode characters. That does not correspond to the SQL syntax.

ok, I wrote the query from my iPad, now I did it from my windows laptop at it is working.

1 Like

New version 0.0.10:
Only some bugfixes

  • token update (synchron to work with toke in the next condition).
  • timezone fix for [date] calculation. The timezone was added two times. That results in a duplicated offset which could affect the result date if the second timezone offset is hitting the 0:00 border.

image

When I place the global tokens also between single quotes it is working fine.

Thanks.

2 Likes

Why write time related entries to an SQL database? Why not use the influxdb app to export all values to an influxdb instance? Then you dont have to create flows to export values, if a device exists, the info is exported

I do see added value in the possibility to write information to a mysql database, but for an alternative of Insights is the influx app and influxdb much better (including grafana for making dashboards, which can show info from both DB’s).

For status information between enviroments is mqtt a very good choice.