Another example:
My table “power_daily” - power usage with daily usage and meter:
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:
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:
Thank you for your help ! I still have a lot to learn and more to do on the subject.
Works:
Something to play again…
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
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).
New test version 0.0.9:
Token:
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 
I’ll check it this evening and send you an update. Thanks for the hint.
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:
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
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.
New version 0.0.10:
Only some bugfixes
When I place the global tokens also between single quotes it is working fine.
Thanks.
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.
