labajo
commented
3 years ago Hola, dejo aquí una solución que aunque no es la mejor, lo mismo te vale. Creo que es la más sencilla con el requisito SQL que has puesto. Luego te dejo otras soluciones aparte.
Pasos
-
Bajar el nodo de SQLite (Base de datos SQL basada en un fichero, o en memoria incluso)
-
Crear un flow como este:
JSON para importar.
[{"id":"2b2f64e2.e4e0cc","type":"tab","label":"Flow 2","disabled":false,"info":""},{"id":"f38d1e92.6add9","type":"mqtt in","z":"2b2f64e2.e4e0cc","name":"Sensor MQTT","topic":"sensor","qos":"0","datatype":"auto","broker":"c32c76ee.aa6fd8","x":390,"y":360,"wires":[["cc8b2972.a137e8","7b655c7a.5a1d04"]]},{"id":"cc8b2972.a137e8","type":"debug","z":"2b2f64e2.e4e0cc","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":600,"y":240,"wires":[]},{"id":"7b655c7a.5a1d04","type":"json","z":"2b2f64e2.e4e0cc","name":"","property":"payload","action":"","pretty":false,"x":590,"y":360,"wires":[["fe98a3a2.1769a","b550c3be.6e5b7"]]},{"id":"fe98a3a2.1769a","type":"debug","z":"2b2f64e2.e4e0cc","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":850,"y":240,"wires":[]},{"id":"b699197a.01cf88","type":"sqlite","z":"2b2f64e2.e4e0cc","mydb":"df91332a.de007","sqlquery":"msg.topic","sql":"","name":"SQL database","x":1040,"y":360,"wires":[["21dd4ae5.8c9b76"]]},{"id":"b550c3be.6e5b7","type":"function","z":"2b2f64e2.e4e0cc","name":"","func":"msg.topic = \"INSERT INTO sensor values(\" + Date.now() +\n \",\" +\n msg.payload.temp +\n \",\" +\n msg.payload.vbat +\n \")\";\n\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":800,"y":360,"wires":[["b699197a.01cf88","f9845051.e3fc9"]]},{"id":"f9845051.e3fc9","type":"debug","z":"2b2f64e2.e4e0cc","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"topic","targetType":"msg","statusVal":"","statusType":"auto","x":1060,"y":240,"wires":[]},{"id":"21dd4ae5.8c9b76","type":"debug","z":"2b2f64e2.e4e0cc","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1290,"y":240,"wires":[]},{"id":"31e94cad.8f5534","type":"inject","z":"2b2f64e2.e4e0cc","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"CREATE TABLE sensor (timestamp NUM PRIMARY KEY,temp NUM NOT NULL,vbatt NUM NOT NULL);","payload":"","payloadType":"date","x":820,"y":500,"wires":[["b699197a.01cf88"]]},{"id":"484eee05.c78f5","type":"comment","z":"2b2f64e2.e4e0cc","name":"1 Crear Tabla en SQLite","info":"","x":780,"y":460,"wires":[]},{"id":"d1a90055.bf84e","type":"comment","z":"2b2f64e2.e4e0cc","name":"2 Insertar dato sensor en tabla SQLite","info":"","x":650,"y":180,"wires":[]},{"id":"52b2a867.5bc998","type":"inject","z":"2b2f64e2.e4e0cc","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT * FROM sensor","payload":"","payloadType":"date","x":820,"y":620,"wires":[["b699197a.01cf88"]]},{"id":"fd5db645.a27518","type":"comment","z":"2b2f64e2.e4e0cc","name":"3 Ver Datos en la tabla (ejemplo)","info":"","x":790,"y":580,"wires":[]},{"id":"c32c76ee.aa6fd8","type":"mqtt-broker","z":"","name":"Localhost","broker":"localhost","port":"1883","clientid":"","usetls":false,"compatmode":false,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"df91332a.de007","type":"sqlitedb","z":"","db":"/tmp/nodered","mode":"RWC"}](Ten cuidado con el path donde se guarda la base de datos. Yo he puesto /tmp/nodered)
Hay como tres pasos en el flow.
- El primer paso es crear la tabla en la base de datos de SQLite.
- El segundo paso ya iría solo. Es el que enlaza los datos que llegan a un topic MQTT (cuidado con formato JSON) e inserta los datos en la tabla(timestamp, temp, vbatt)
- El tercer paso es de testeo para ver que se insertan bien los datos en la tabla del SQLite.
Por último hay conversores de SQLite a Excel o CSV. Conversor online a excel Quizás el CSV convierta bien el dato timestamp.
Ahora te detallo aquí mejores soluciones:
La primera es sustituir SQLite por otro tipo de base de datos:
- Si quieres seguir manteniendo el SQL hay nodos para MySQL, postgresql, etc.
- Puedes cambiar también a bases de datos no SQL como MongoDB
Pero la solución buena desde mi punto de vista es usar InfluxDb, e incluso un stack de software llamado TICK. [TICK Stack](https://wiki.archlinux.org/index.php/TICK_stack_(Espa%C3%B1ol) InfluxDb es una base de datos orientada a métricas y especial para el tema del IOT. La parte importante del stack es Chronograf que tiene parte de gestión de InfluxDb y también puedes pintar gráficas de distintos tipos con los datos. También puedes enlazar un Grafana Grafana contra esa base de datos de InfluxDb. Y luego también está el software Kapacitor, que yo lo veo muy potente. Kapacitor permite generar y liberar alarmas según las métricas que le llegan a la base de datos y tiene conectores de notificación para Telegram, Email, Slack, etc..
Un saludo, Juan
Nancho1950
Explicación de lo que debe hacer el flow en Node RED Leer datos o estado de sensor para crera una base de datos en SQL o bien enviarlos a una planilla excel. Esto nos independiza para crear reportes o graficos y realizar nuestro propio Dashboard.
Notas del Autor Obtener otro beneficio de Home Assistant, no solo realizar automatizaciones o escenas, sino informacion historica para realizar mantencion , estadisticas, debug, o conectarnos a software de terceros.
Saludos y gracias Jota.