SBFspot SMA inverter dashboard chart from sqlite DB

Uses node-red-dashboard and node-red-node-sqlite to select and display day data from the SBFspot sqlite DB. One power generation day ist displayed as chart. Date navigation is possible via date picker, today, prev and next day buttons.

Copy[{"id":"5499896695fb8f68","type":"sqlite","z":"d15c6ec1de589c50","mydb":"e419af83f3cd2f61","sqlquery":"msg.topic","sql":"","name":"SBFSpot DB","x":850,"y":400,"wires":[["490441bc6f83a774","c3d18bcba8aba1b9"]]},{"id":"a72f40c49a2576d7","type":"inject","z":"d15c6ec1de589c50","name":"inject","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payloadType":"date","x":210,"y":440,"wires":[["0de66c090e59119c"]]},{"id":"490441bc6f83a774","type":"debug","z":"d15c6ec1de589c50","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1050,"y":460,"wires":[]},{"id":"05cad8aab8717182","type":"function","z":"d15c6ec1de589c50","name":"sql","func":"\nif (msg.topic == 'setdate') {\n    \n    node.warn ('setdate:' + msg.payload);\n    var date = new Date(msg.payload);\n    \n    flow.set('selectedDate', date);\n    \n    var sqllite_date = date.toISOString();\n    \n    msg.topic = \"SELECT datetime(TimeStamp, 'unixepoch','localtime') as DateTime\" + \n                \",TimeStamp \" +\n                \",TotalYield \" + \n                \",Power \" + \n                \"FROM DayData \" +\n                \"WHERE datetime(TimeStamp, 'unixepoch','localtime') BETWEEN datetime('\" + sqllite_date + \"','start of day') \" +\n                \"AND datetime('\" + sqllite_date + \"','start of day', '+1 day') \" +\n                \"ORDER BY TimeStamp ASC \";\n}\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":670,"y":400,"wires":[["5499896695fb8f68","22654e57e29282fc"]]},{"id":"82bfe947bf99a730","type":"ui_chart","z":"d15c6ec1de589c50","name":"","group":"e3e9994998958a58","order":5,"width":"40","height":"10","label":"power","chartType":"line","legend":"false","xformat":"YY-MM-DD hh:mm","interpolate":"bezier","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":1190,"y":400,"wires":[[]]},{"id":"c3d18bcba8aba1b9","type":"function","z":"d15c6ec1de589c50","name":"to array","func":"let rowArray = msg.payload;\nlet dataArray = [];\n\nfor (let row of rowArray) {\n    let dataPoint = {\n        \"x\": row.TimeStamp * 1000,\n        \"y\": row.Power\n    };\n    dataArray.push(dataPoint);\n}\n\nmsg.payload = [{\n\"series\": [\"A\"],\n\"data\": [dataArray],\n\"labels\": [\"Watt\"]\n}];\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1040,"y":400,"wires":[["82bfe947bf99a730"]]},{"id":"9d528623c04a92b2","type":"ui_button","z":"d15c6ec1de589c50","name":"","group":"e3e9994998958a58","order":2,"width":"3","height":"1","passthru":false,"label":"prev","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"datePrev","topicType":"str","x":370,"y":320,"wires":[["75ac7c3ba70f90aa"]]},{"id":"79337bc8f28fbf73","type":"ui_button","z":"d15c6ec1de589c50","name":"","group":"e3e9994998958a58","order":3,"width":"3","height":"1","passthru":false,"label":"next","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"dateNext","topicType":"str","x":370,"y":360,"wires":[["75ac7c3ba70f90aa"]]},{"id":"0de66c090e59119c","type":"ui_button","z":"d15c6ec1de589c50","name":"","group":"e3e9994998958a58","order":1,"width":"3","height":"1","passthru":true,"label":"today","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"date","topic":"setdate","topicType":"str","x":370,"y":440,"wires":[["1dbe97dd3b128404"]]},{"id":"1dbe97dd3b128404","type":"ui_date_picker","z":"d15c6ec1de589c50","name":"","label":"date","group":"e3e9994998958a58","order":4,"width":"5","height":"1","passthru":true,"topic":"setdate","topicType":"str","className":"","x":510,"y":400,"wires":[["05cad8aab8717182"]]},{"id":"640459e905094801","type":"inject","z":"d15c6ec1de589c50","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":true,"onceDelay":0.1,"topic":"","payloadType":"date","x":190,"y":400,"wires":[["1dbe97dd3b128404"]]},{"id":"22654e57e29282fc","type":"debug","z":"d15c6ec1de589c50","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":830,"y":460,"wires":[]},{"id":"75ac7c3ba70f90aa","type":"function","z":"d15c6ec1de589c50","name":"prev next day","func":"if (msg.topic == 'datePrev') {\n  let selectedDate = flow.get('selectedDate');\n  selectedDate.setDate(selectedDate.getDate()-1);\n  selectedDate.setHours(12,0,0,0);\n  msg.payload = selectedDate.getTime() ;\n  //node.warn ('datePrev:' + msg.payload);\n}\n\nif (msg.topic == 'dateNext') {\n  let selectedDate = flow.get('selectedDate');\n  selectedDate.setDate(selectedDate.getDate()+1);\n  selectedDate.setHours(12,0,0,0);\n  msg.payload = selectedDate.getTime();\n  //node.warn ('dateNext:' + msg.payload);\n}\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":540,"y":340,"wires":[["1dbe97dd3b128404"]]},{"id":"c1e98c70dc9ba157","type":"inject","z":"d15c6ec1de589c50","name":"inject","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payloadType":"date","x":210,"y":360,"wires":[["79337bc8f28fbf73"]]},{"id":"049edb59f724a3ea","type":"inject","z":"d15c6ec1de589c50","name":"inject","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payloadType":"date","x":210,"y":320,"wires":[["9d528623c04a92b2"]]},{"id":"e419af83f3cd2f61","type":"sqlitedb","db":"/home/pi/smadata/SBFspot.db","mode":"RO"},{"id":"e3e9994998958a58","type":"ui_group","name":"SMA power","tab":"dcaee38d9b91b3eb","order":1,"disp":true,"width":"40","collapse":false,"className":""},{"id":"dcaee38d9b91b3eb","type":"ui_tab","name":"SMA","icon":"dashboard","disabled":false,"hidden":false}]