surfrock66
commented
2 years ago Hmm, I don't know Domoticz, but the jist is rather than one "raw_logs" table, the table names are appended with the year and month. Because of that, this would become a loop with a 2 part process; the first would be getting a list of tables whose names are like "rawlogs", then doing a for-each through them. Here's the format of the table list:
Database changed
mysql> show tables;
+----------------------+
| Tables_in_hda_torque |
+----------------------+
| raw_logs_2014_10 |
| raw_logs_2014_11 |
| raw_logs_2014_12 |
| raw_logs_2015_01 |
| raw_logs_2015_02 |
| raw_logs_2015_03 |
| raw_logs_2015_04 |
| raw_logs_2015_05 |
| raw_logs_2015_06 |
| raw_logs_2015_07 |
| raw_logs_2015_08 |
| raw_logs_2015_09 |
| raw_logs_2015_10 |
| raw_logs_2015_11 |
| raw_logs_2015_12 |
| raw_logs_2016_01 |
| raw_logs_2016_02 |
| raw_logs_2016_03 |
| raw_logs_2016_04 |
| raw_logs_2016_05 |
| raw_logs_2016_06 |
| raw_logs_2016_07 |
| raw_logs_2016_08 |
| raw_logs_2016_09 |
| raw_logs_2016_10 |
| raw_logs_2016_11 |
| raw_logs_2016_12 |
| raw_logs_2017_01 |
| raw_logs_2017_02 |
| raw_logs_2017_03 |
| raw_logs_2017_04 |
| raw_logs_2017_05 |
| raw_logs_2017_06 |
| raw_logs_2017_07 |
| raw_logs_2017_08 |
| raw_logs_2017_09 |
| raw_logs_2017_10 |
...So, if you ran that in the following way, you get a pretty clean list of tables:
mysql -u root hda_torque -N -e "SHOW TABLES" | grep -e "raw_logs_"
From there, you could for-each them, swapping in the table name; I'm not sure if you need them all at once, or if you're running it once a month at the end or something, but that should get you code that will work with the split tables. Does that help?
Need help (little understanding of the use of databases):
Before the breakdown by month I used these commands to extract data and put them in Domoticz:
!/bin/sh
result=
mysql -u UTILISATEUR -pMOTDEPASSE torque -N -e "SELECT kff126b FROM raw_logs ORDER BY time DESC LIMIT 1"echo $result kmrestant=mysql -u UTILISATEUR -pMOTDEPASSE torque -N -e "SELECT kff126a FROM raw_logs ORDER BY time DESC LIMIT 1"echo $kmrestant curl -g "http://192.168.0.21:8080/json.htm?type=command¶m=udevice&idx=376&nvalue=0&svalue=$result" curl -g "http://192.168.0.21:8080/json.htm?type=command¶m=udevice&idx=377&nvalue=0&svalue=$kmrestant"Please can you help me to convert using new DB structure? Thank Aldo