[{"id":"27f25c95.8feb34","type":"tab","label":"Flow 1"},{"id":"bd59b85.434d948","type":"function","z":"27f25c95.8feb34","name":"Save to DB","func":"var sql = "";\nvar d = new Date();\nvar epoch = d.getTime();\nvar outputs = [];\n\nsql = "INSERT INTO sensor_data (device,sensor,value,epoch) " +\n "VALUES ('miflora','temp',"+msg.payload.temperature+","+epoch+")";\noutputs.push({topic:sql}); \nsql = "INSERT INTO sensor_data(device,sensor,value,epoch) " +\n "VALUES ('miflora','sunlight',"+msg.payload.sunlight+","+epoch+")";\noutputs.push({topic:sql}); \nsql = "INSERT INTO sensor_data(device,sensor,value,epoch) " +\n "VALUES ('miflora','moisture',"+msg.payload.moisture+","+epoch+")";\noutputs.push({topic:sql}); \nsql = "INSERT INTO sensor_data(device,sensor,value,epoch) " +\n "VALUES ('miflora','fertility',"+msg.payload.fertility+","+epoch+")"; \noutputs.push({topic:sql}); \nsql = "INSERT INTO sensor_data(device,sensor,value,epoch) " +\n "VALUES ('miflora','battery',"+msg.payload.battery+","+epoch+")"; \noutputs.push({topic:sql}); \n \n// Update the status with current timestamp\nvar now = new Date();\nvar yyyy = now.getFullYear();\nvar mm = now.getMonth() < 9 ? "0" + (now.getMonth() + 1) : (now.getMonth() + 1); // getMonth() is zero-based\nvar dd = now.getDate() < 10 ? "0" + now.getDate() : now.getDate();\nvar hh = now.getHours() < 10 ? "0" + now.getHours() : now.getHours();\nvar mmm = now.getMinutes() < 10 ? "0" + now.getMinutes() : now.getMinutes();\nvar ss = now.getSeconds() < 10 ? "0" + now.getSeconds() : now.getSeconds();\nnode.status({fill:"blue",shape:"ring",text:"Last update: "+dd + "." + mm + "." + yyyy + " " + hh + ":" + mmm + ":" + ss}); \n \nreturn [ outputs ];","outputs":1,"noerr":0,"x":131,"y":94,"wires":[["ee6dfa23.54e328"]]},{"id":"ee6dfa23.54e328","type":"sqlite","z":"27f25c95.8feb34","mydb":"1c25415d.b8427f","name":"Node Red DB","x":375,"y":94,"wires":[[]]},{"id":"99dd1a93.c13038","type":"comment","z":"27f25c95.8feb34","name":"Example on how to save multiple data points in DB","info":"This function node creates multiple INSERT INTO\ncommands for data points that are stored in \nattributes of the payload.","x":237,"y":46,"wires":[]},{"id":"bf26f95a.fe9078","type":"comment","z":"27f25c95.8feb34","name":"Data aggregation","info":"Below example extract max, min and last from the\nsensor log table, and inserts them into an\naggregate table.","x":131.25,"y":347.5,"wires":[]},{"id":"fbe82347.73043","type":"inject","z":"27f25c95.8feb34","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"00 3 * * ","once":false,"x":120.25,"y":444.5000476837158,"wires":[["aad5ab51.b05f78"]]},{"id":"aad5ab51.b05f78","type":"function","z":"27f25c95.8feb34","name":"Get Max values","func":"var d = new Date();\nvar epoch = d.getTime();\n// Go to previous day\nd.setTime(d.getTime()-(2460601000));\n// calculate begining of the day\nd.setHours(0);\nd.setMinutes(0);\nd.setSeconds(0);\nd.setMilliseconds(0);\nvar fromdate = d.getTime();\n// calculate end of the day\nd.setHours(23);\nd.setMinutes(59);\nd.setSeconds(59);\nd.setMilliseconds(999);\nvar enddate = d.getTime();\n\nmsg.topic = "SELECT device,sensor,max(value) AS value FROM sensor_data WHERE epoch >= " + fromdate + " AND epoch <= " + enddate + " AND (sensor='temp' or sensor='power' or sensor='moisture') GROUP BY sensor";\nreturn msg;","outputs":1,"noerr":0,"x":308.25,"y":443.5000476837158,"wires":[["5f7eb893.60baa8"]]},{"id":"5f7eb893.60baa8","type":"sqlite","z":"27f25c95.8feb34","mydb":"1c25415d.b8427f","name":"DB","x":495.25,"y":442.5000476837158,"wires":[["9ae6ccc1.5ae62"]]},{"id":"a558b020.258de","type":"function","z":"27f25c95.8feb34","name":"Get Min values","func":"var d = new Date();\nvar epoch = d.getTime();\n// Go to previous day\nd.setTime(d.getTime()-(2460601000));\n// calculate begining of the day\nd.setHours(0);\nd.setMinutes(0);\nd.setSeconds(0);\nd.setMilliseconds(0);\nvar fromdate = d.getTime();\n// calculate end of the day\nd.setHours(23);\nd.setMinutes(59);\nd.setSeconds(59);\nd.setMilliseconds(999);\nvar enddate = d.getTime();\n\nmsg.topic = "SELECT device,sensor,MIN(value) as value FROM sensor_data WHERE epoch >= " + fromdate + " AND epoch <= " + enddate + " AND (sensor='temp' OR sensor='battery') GROUP BY sensor";\nreturn msg;","outputs":1,"noerr":0,"x":308.25,"y":544.5000476837158,"wires":[["2b6fc149.ea12be"]]},{"id":"2b6fc149.ea12be","type":"sqlite","z":"27f25c95.8feb34","mydb":"1c25415d.b8427f","name":"DB","x":495.25,"y":543.5000476837158,"wires":[["e6cc72c.c49f89"]]},{"id":"b64bf25c.43bb3","type":"function","z":"27f25c95.8feb34","name":"Get Last values","func":"var output = [];\nvar d = new Date();\nvar epoch = d.getTime();\n// Go to previous day\nd.setTime(d.getTime()-(2460601000));\n// calculate begining of the day\nd.setHours(0);\nd.setMinutes(0);\nd.setSeconds(0);\nd.setMilliseconds(0);\nvar fromdate = d.getTime();\n// calculate end of the day\nd.setHours(23);\nd.setMinutes(59);\nd.setSeconds(59);\nd.setMilliseconds(999);\nvar enddate = d.getTime();\n\noutput.push({ topic:"SELECT device,sensor,value FROM sensor_data WHERE epoch >= " + fromdate + " AND epoch <= " + enddate + " AND device='growatt' AND sensor='today' ORDER BY id DESC LIMIT 1 " });\noutput.push({ topic:"SELECT device,sensor,value FROM sensor_data WHERE epoch >= " + fromdate + " AND epoch <= " + enddate + " AND device='miflora' AND sensor='moisture' ORDER BY id DESC LIMIT 1 " });\n\nreturn [output];","outputs":1,"noerr":0,"x":308.25,"y":660.5000476837158,"wires":[["5c78701f.0ff3d"]]},{"id":"5c78701f.0ff3d","type":"sqlite","z":"27f25c95.8feb34","mydb":"1c25415d.b8427f","name":"DB","x":495.25,"y":659.5000476837158,"wires":[["a8ca789d.37b488"]]},{"id":"418be32f.f2be6c","type":"inject","z":"27f25c95.8feb34","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"01 3 * * ","once":false,"x":125.25,"y":548.5000476837158,"wires":[["a558b020.258de"]]},{"id":"b7be665d.311de8","type":"inject","z":"27f25c95.8feb34","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"02 3 * * ","once":false,"x":119.25,"y":663.5000476837158,"wires":[["b64bf25c.43bb3"]]},{"id":"a8ca789d.37b488","type":"function","z":"27f25c95.8feb34","name":"SQL Updates","func":"var output = [];\nvar d = new Date();\nvar epoch = d.getTime();\n// Go to previous day\nd.setTime(d.getTime()-(2460601000));\n// calculate begining of the day\nd.setHours(0);\nd.setMinutes(0);\nd.setSeconds(0);\nd.setMilliseconds(0);\nvar fromdate = d.getTime();\n\nmsg.topic = "INSERT OR REPLACE INTO sensor_aggr (epoch,device,sensor,value) VALUES("+fromdate+",'"+msg.payload[0].device+"','"+msg.payload[0].sensor+"',"+msg.payload[0].value+")";\n\nreturn msg;","outputs":1,"noerr":0,"x":675.2500152587891,"y":705.5000953674316,"wires":[["c1c4ff21.c72f4"]]},{"id":"9ae6ccc1.5ae62","type":"function","z":"27f25c95.8feb34","name":"SQL Updates","func":"var output = [];\nvar d = new Date();\nvar epoch = d.getTime();\n// Go to previous day\nd.setTime(d.getTime()-(2460601000));\n// calculate begining of the day\nd.setHours(0);\nd.setMinutes(0);\nd.setSeconds(0);\nd.setMilliseconds(0);\nvar fromdate = d.getTime();\n\nfor (var i=0; i<msg.payload.length; i++) {\n output.push({ topic:"INSERT OR REPLACE INTO sensor_aggr (epoch,device,sensor,value) VALUES("+fromdate+",'"+msg.payload[i].device+"','"+msg.payload[i].sensor+"_max',"+msg.payload[i].value+")" });\n}\n\nreturn [output];","outputs":1,"noerr":0,"x":656.2500152587891,"y":497.50009536743164,"wires":[["c1c4ff21.c72f4"]]},{"id":"e6cc72c.c49f89","type":"function","z":"27f25c95.8feb34","name":"SQL Updates","func":"var output = [];\nvar d = new Date();\nvar epoch = d.getTime();\n// Go to previous day\nd.setTime(d.getTime()-(2460601000));\n// calculate begining of the day\nd.setHours(0);\nd.setMinutes(0);\nd.setSeconds(0);\nd.setMilliseconds(0);\nvar fromdate = d.getTime();\n\nfor (var i=0; i<msg.payload.length; i++) {\n output.push({ topic:"INSERT OR REPLACE INTO sensor_aggr (epoch,device,sensor,value) VALUES("+fromdate+",'"+msg.payload[i].device+"','"+msg.payload[i].sensor+"_min',"+msg.payload[i].value+")" });\n}\n\nreturn [output];","outputs":1,"noerr":0,"x":670.2500152587891,"y":599.5000953674316,"wires":[["c1c4ff21.c72f4"]]},{"id":"c1c4ff21.c72f4","type":"sqlite","z":"27f25c95.8feb34","mydb":"1c25415d.b8427f","name":"DB","x":908.5833282470703,"y":560.5000953674316,"wires":[[]]},{"id":"a4093916.fa4c38","type":"comment","z":"27f25c95.8feb34","name":"Calculate aggregated values (min,max,last)","info":"","x":395.5833282470703,"y":396.5000476837158,"wires":[]},{"id":"d8b33397.29f5b","type":"comment","z":"27f25c95.8feb34","name":"Insert aggregated values to new table","info":"","x":756.5833282470703,"y":448.5000476837158,"wires":[]},{"id":"45b3039d.9b2fac","type":"ui_chart","z":"27f25c95.8feb34","name":"Temperature","group":"27c3d951.18a4e6","order":2,"width":"","height":"","label":"","chartType":"line","xformat":"%H:%M:%S","interpolate":"linear","nodata":"No Data","ymin":"","ymax":"","removeOlder":"1","removeOlderUnit":"86400","x":628.25,"y":248.75,"wires":[["42239353.a0ebdc"],[]]},{"id":"42239353.a0ebdc","type":"file","z":"27f25c95.8feb34","name":"Chart dump","filename":"/home/pi/charts/miflora_temp.dump","appendNewline":true,"createDir":false,"overwriteFile":"true","x":822.25,"y":242.75,"wires":[]},{"id":"6a1cb72d.8b6348","type":"file in","z":"27f25c95.8feb34","name":"Chart dump","filename":"/home/pi/charts/miflora_temp.dump","format":"utf8","x":270.25,"y":250.75,"wires":[["f8940a20.feef98"]]},{"id":"294483f1.957e7c","type":"inject","z":"27f25c95.8feb34","name":"Startup","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":true,"x":116.25,"y":251.75,"wires":[["6a1cb72d.8b6348"]]},{"id":"f8940a20.feef98","type":"json","z":"27f25c95.8feb34","name":"","x":422.25,"y":250.75,"wires":[["45b3039d.9b2fac"]]},{"id":"9937da83.369da8","type":"comment","z":"27f25c95.8feb34","name":"Dashboard graph backup example","info":"This flow section shows how to back up the graph\nstate to a local file and restore that at startup.\nThis loads the last graph state so you don't get\nthe "No data" screen after node red restart.","x":187.5,"y":191.25,"wires":[]},{"id":"8b341a53.4d5ed8","type":"function","z":"27f25c95.8feb34","name":"SQL","func":"// This will handle any device and any attribute as long as it is in the DB\nvar Week = 604800000 ; //7 Days\nvar Day = 86400000 ; // 1 Days\nvar d = new Date();\nvar epoch = d.getTime();\nvar fromdate = epoch - Day;\nvar enddate = epoch;\nvar output = [];\n\nfor (var property in msg.payload) {\n if (msg.payload.hasOwnProperty(property)) {\n //output.push({ payload: property + " has value "+msg.payload[property] });\n if (msg.payload[property]) {\n var parts = property.split("/");\n if (parts[0]==="period") {\n switch (parts[1]) {\n case "today":\n fromdate = epoch-Day;\n enddate = epoch;\n break;\n case "yesterday":\n fromdate = epoch-2Day;\n enddate = epoch-Day;\n break;\n case "week":\n fromdate = epoch-Week;\n enddate = epoch;\n break;\n }\n } else {\n output.push({ topic: "SELECT * FROM sensor_data WHERE device='"+parts[0]+"' AND sensor='"+parts[1]+"' AND epoch >= " + fromdate + " AND epoch <= " + enddate });\n }\n }\n }\n}\n\noutput[output.length-1].complete=true;\n\nreturn [ output ];\n\n//msg.topic = "SELECT * FROM sensor_data WHERE device='growatt' AND sensor='power' AND epoch >= " + fromdate + " AND epoch <= " + epoch ;\n//return msg;","outputs":1,"noerr":0,"x":316.25,"y":895,"wires":[["bbf81e68.a7ee1"]]},{"id":"bbf81e68.a7ee1","type":"sqlite","z":"27f25c95.8feb34","mydb":"1c25415d.b8427f","name":"DB","x":462.25,"y":896,"wires":[["ee672a38.931748"]]},{"id":"2caa3402.536a0c","type":"ui_chart","z":"27f25c95.8feb34","name":"Chart","group":"bd903d6.d9fc7c","order":0,"width":0,"height":0,"label":"","chartType":"line","legend":"false","xformat":"%a %H:%M","interpolate":"linear","nodata":"","ymin":"","ymax":"","removeOlder":"1","removeOlderUnit":"604800","x":809.25,"y":996,"wires":[[],[]]},{"id":"ac39fc2.3779d","type":"function","z":"27f25c95.8feb34","name":"Prep","func":"var msg2 = [];\n\n// this is the logic when there are multiple data sets are received\nfor (var i=0; i<msg.payload.length; i++) {\n var output = [];\n for (var j=0; j<msg.payload[i].length; j++) {\n output.push([msg.payload[i][j].epoch, msg.payload[i][j].value]);\n }\n msg2.push({ key: msg.payload[i][0].device+"/"+msg.payload[i][0].sensor, values : output});\n //msg2.push({ key: "test", values : output});\n}\n\nmsg.payload=msg2;\n//msg.payload = [ { key: "Power", values : output} ];\n//msg.topic = "Power";\nreturn msg;","outputs":1,"noerr":0,"x":793.25,"y":898,"wires":[["2caa3402.536a0c"]]},{"id":"18961c51.640204","type":"inject","z":"27f25c95.8feb34","name":"Reset chart","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":151.25,"y":1018,"wires":[["379bf862.755b58"]]},{"id":"379bf862.755b58","type":"function","z":"27f25c95.8feb34","name":"Empty payload","func":"msg.payload = [];\nreturn msg;","outputs":1,"noerr":0,"x":479.25,"y":1017,"wires":[["2caa3402.536a0c"]]},{"id":"45a070cf.ba722","type":"ui_form","z":"27f25c95.8feb34","name":"Data points","label":"","group":"1e17cd0b.862863","order":0,"width":0,"height":0,"options":[{"label":"Period: today","value":"period/today","type":"checkbox","required":false},{"label":"Period: yesterday","value":"period/yesterday","type":"checkbox","required":false},{"label":"Period: this week","value":"period/week","type":"checkbox","required":false},{"label":"Miflora temperature","value":"miflora/temp","type":"checkbox","required":false},{"label":"Miflora battery level","value":"miflora/battery","type":"checkbox","required":false},{"label":"Miflora sunlight","value":"miflora/sunlight","type":"checkbox","required":false},{"label":"Miflora soil moisture","value":"miflora/moisture","type":"checkbox","required":false},{"label":"Miflora soil fertility","value":"miflora/fertility","type":"checkbox","required":false},{"label":"Solar power","value":"growatt/power","type":"checkbox","required":false},{"label":"Solar voltage","value":"growatt/voltage","type":"checkbox","required":false}],"formValue":{"period/today":false,"period/yesterday":false,"period/week":false,"miflora/temp":false,"miflora/battery":false,"miflora/sunlight":false,"miflora/moisture":false,"miflora/fertility":false,"growatt/power":false,"growatt/voltage":false},"payload":"","topic":"","x":121.25,"y":842,"wires":[["8b341a53.4d5ed8"]]},{"id":"ee672a38.931748","type":"join","z":"27f25c95.8feb34","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\n","timeout":"","count":"","x":604.25,"y":896,"wires":[["ac39fc2.3779d"]]},{"id":"9c5689c9.61db68","type":"comment","z":"27f25c95.8feb34","name":"Dynamic report example","info":"This flow can generate graph based from data stored\nin the database and display multiple data points\nin a single graph.","x":153.75,"y":787.5,"wires":[]},{"id":"1c25415d.b8427f","type":"sqlitedb","z":"","db":"/home/pi/sqlite/nodered"},{"id":"27c3d951.18a4e6","type":"ui_group","z":"","name":"MiFlora","tab":"156af96f.f8fd27","order":2,"disp":true,"width":"6"},{"id":"bd903d6.d9fc7c","type":"ui_group","z":"","name":"Report","tab":"1e3fe400.0baf5c","order":2,"disp":true,"width":"18"},{"id":"1e17cd0b.862863","type":"ui_group","z":"","name":"Selection","tab":"1e3fe400.0baf5c","order":1,"disp":true,"width":"5"},{"id":"156af96f.f8fd27","type":"ui_tab","z":"","name":"Home","icon":"home","order":"1"},{"id":"1e3fe400.0baf5c","type":"ui_tab","z":"","name":"Reports","icon":"dashboard","order":9}]
-
-
Save AmazingBuckEye/a8282453b7214a2130151060b6bbba8c to your computer and use it in GitHub Desktop.
Test sql lite
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
[{"id":"27f25c95.8feb34","type":"tab","label":"Flow 1"},{"id":"bd59b85.434d948","type":"function","z":"27f25c95.8feb34","name":"Save to DB","func":"var sql = \"\";\nvar d = new Date();\nvar epoch = d.getTime();\nvar outputs = [];\n\nsql = \"INSERT INTO sensor_data (device,sensor,value,epoch) \" +\n \"VALUES ('miflora','temp',\"+msg.payload.temperature+\",\"+epoch+\")\";\noutputs.push({topic:sql}); \nsql = \"INSERT INTO sensor_data(device,sensor,value,epoch) \" +\n \"VALUES ('miflora','sunlight',\"+msg.payload.sunlight+\",\"+epoch+\")\";\noutputs.push({topic:sql}); \nsql = \"INSERT INTO sensor_data(device,sensor,value,epoch) \" +\n \"VALUES ('miflora','moisture',\"+msg.payload.moisture+\",\"+epoch+\")\";\noutputs.push({topic:sql}); \nsql = \"INSERT INTO sensor_data(device,sensor,value,epoch) \" +\n \"VALUES ('miflora','fertility',\"+msg.payload.fertility+\",\"+epoch+\")\"; \noutputs.push({topic:sql}); \nsql = \"INSERT INTO sensor_data(device,sensor,value,epoch) \" +\n \"VALUES ('miflora','battery',\"+msg.payload.battery+\",\"+epoch+\")\"; \noutputs.push({topic:sql}); \n \n// Update the status with current timestamp\nvar now = new Date();\nvar yyyy = now.getFullYear();\nvar mm = now.getMonth() < 9 ? \"0\" + (now.getMonth() + 1) : (now.getMonth() + 1); // getMonth() is zero-based\nvar dd = now.getDate() < 10 ? \"0\" + now.getDate() : now.getDate();\nvar hh = now.getHours() < 10 ? \"0\" + now.getHours() : now.getHours();\nvar mmm = now.getMinutes() < 10 ? \"0\" + now.getMinutes() : now.getMinutes();\nvar ss = now.getSeconds() < 10 ? \"0\" + now.getSeconds() : now.getSeconds();\nnode.status({fill:\"blue\",shape:\"ring\",text:\"Last update: \"+dd + \".\" + mm + \".\" + yyyy + \" \" + hh + \":\" + mmm + \":\" + ss}); \n \nreturn [ outputs ];","outputs":1,"noerr":0,"x":131,"y":94,"wires":[["ee6dfa23.54e328"]]},{"id":"ee6dfa23.54e328","type":"sqlite","z":"27f25c95.8feb34","mydb":"1c25415d.b8427f","name":"Node Red DB","x":375,"y":94,"wires":[[]]},{"id":"99dd1a93.c13038","type":"comment","z":"27f25c95.8feb34","name":"Example on how to save multiple data points in DB","info":"This function node creates multiple INSERT INTO\ncommands for data points that are stored in \nattributes of the payload.","x":237,"y":46,"wires":[]},{"id":"bf26f95a.fe9078","type":"comment","z":"27f25c95.8feb34","name":"Data aggregation","info":"Below example extract max, min and last from the\nsensor log table, and inserts them into an\naggregate table.","x":131.25,"y":347.5,"wires":[]},{"id":"fbe82347.73043","type":"inject","z":"27f25c95.8feb34","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"00 3 * * *","once":false,"x":120.25,"y":444.5000476837158,"wires":[["aad5ab51.b05f78"]]},{"id":"aad5ab51.b05f78","type":"function","z":"27f25c95.8feb34","name":"Get Max values","func":"var d = new Date();\nvar epoch = d.getTime();\n// Go to previous day\nd.setTime(d.getTime()-(24*60*60*1000));\n// calculate begining of the day\nd.setHours(0);\nd.setMinutes(0);\nd.setSeconds(0);\nd.setMilliseconds(0);\nvar fromdate = d.getTime();\n// calculate end of the day\nd.setHours(23);\nd.setMinutes(59);\nd.setSeconds(59);\nd.setMilliseconds(999);\nvar enddate = d.getTime();\n\nmsg.topic = \"SELECT device,sensor,max(value) AS value FROM sensor_data WHERE epoch >= \" + fromdate + \" AND epoch <= \" + enddate + \" AND (sensor='temp' or sensor='power' or sensor='moisture') GROUP BY sensor\";\nreturn msg;","outputs":1,"noerr":0,"x":308.25,"y":443.5000476837158,"wires":[["5f7eb893.60baa8"]]},{"id":"5f7eb893.60baa8","type":"sqlite","z":"27f25c95.8feb34","mydb":"1c25415d.b8427f","name":"DB","x":495.25,"y":442.5000476837158,"wires":[["9ae6ccc1.5ae62"]]},{"id":"a558b020.258de","type":"function","z":"27f25c95.8feb34","name":"Get Min values","func":"var d = new Date();\nvar epoch = d.getTime();\n// Go to previous day\nd.setTime(d.getTime()-(24*60*60*1000));\n// calculate begining of the day\nd.setHours(0);\nd.setMinutes(0);\nd.setSeconds(0);\nd.setMilliseconds(0);\nvar fromdate = d.getTime();\n// calculate end of the day\nd.setHours(23);\nd.setMinutes(59);\nd.setSeconds(59);\nd.setMilliseconds(999);\nvar enddate = d.getTime();\n\nmsg.topic = \"SELECT device,sensor,MIN(value) as value FROM sensor_data WHERE epoch >= \" + fromdate + \" AND epoch <= \" + enddate + \" AND (sensor='temp' OR sensor='battery') GROUP BY sensor\";\nreturn msg;","outputs":1,"noerr":0,"x":308.25,"y":544.5000476837158,"wires":[["2b6fc149.ea12be"]]},{"id":"2b6fc149.ea12be","type":"sqlite","z":"27f25c95.8feb34","mydb":"1c25415d.b8427f","name":"DB","x":495.25,"y":543.5000476837158,"wires":[["e6cc72c.c49f89"]]},{"id":"b64bf25c.43bb3","type":"function","z":"27f25c95.8feb34","name":"Get Last values","func":"var output = [];\nvar d = new Date();\nvar epoch = d.getTime();\n// Go to previous day\nd.setTime(d.getTime()-(24*60*60*1000));\n// calculate begining of the day\nd.setHours(0);\nd.setMinutes(0);\nd.setSeconds(0);\nd.setMilliseconds(0);\nvar fromdate = d.getTime();\n// calculate end of the day\nd.setHours(23);\nd.setMinutes(59);\nd.setSeconds(59);\nd.setMilliseconds(999);\nvar enddate = d.getTime();\n\noutput.push({ topic:\"SELECT device,sensor,value FROM sensor_data WHERE epoch >= \" + fromdate + \" AND epoch <= \" + enddate + \" AND device='growatt' AND sensor='today' ORDER BY id DESC LIMIT 1 \" });\noutput.push({ topic:\"SELECT device,sensor,value FROM sensor_data WHERE epoch >= \" + fromdate + \" AND epoch <= \" + enddate + \" AND device='miflora' AND sensor='moisture' ORDER BY id DESC LIMIT 1 \" });\n\nreturn [output];","outputs":1,"noerr":0,"x":308.25,"y":660.5000476837158,"wires":[["5c78701f.0ff3d"]]},{"id":"5c78701f.0ff3d","type":"sqlite","z":"27f25c95.8feb34","mydb":"1c25415d.b8427f","name":"DB","x":495.25,"y":659.5000476837158,"wires":[["a8ca789d.37b488"]]},{"id":"418be32f.f2be6c","type":"inject","z":"27f25c95.8feb34","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"01 3 * * *","once":false,"x":125.25,"y":548.5000476837158,"wires":[["a558b020.258de"]]},{"id":"b7be665d.311de8","type":"inject","z":"27f25c95.8feb34","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"02 3 * * *","once":false,"x":119.25,"y":663.5000476837158,"wires":[["b64bf25c.43bb3"]]},{"id":"a8ca789d.37b488","type":"function","z":"27f25c95.8feb34","name":"SQL Updates","func":"var output = [];\nvar d = new Date();\nvar epoch = d.getTime();\n// Go to previous day\nd.setTime(d.getTime()-(24*60*60*1000));\n// calculate begining of the day\nd.setHours(0);\nd.setMinutes(0);\nd.setSeconds(0);\nd.setMilliseconds(0);\nvar fromdate = d.getTime();\n\nmsg.topic = \"INSERT OR REPLACE INTO sensor_aggr (epoch,device,sensor,value) VALUES(\"+fromdate+\",'\"+msg.payload[0].device+\"','\"+msg.payload[0].sensor+\"',\"+msg.payload[0].value+\")\";\n\nreturn msg;","outputs":1,"noerr":0,"x":675.2500152587891,"y":705.5000953674316,"wires":[["c1c4ff21.c72f4"]]},{"id":"9ae6ccc1.5ae62","type":"function","z":"27f25c95.8feb34","name":"SQL Updates","func":"var output = [];\nvar d = new Date();\nvar epoch = d.getTime();\n// Go to previous day\nd.setTime(d.getTime()-(24*60*60*1000));\n// calculate begining of the day\nd.setHours(0);\nd.setMinutes(0);\nd.setSeconds(0);\nd.setMilliseconds(0);\nvar fromdate = d.getTime();\n\nfor (var i=0; i<msg.payload.length; i++) {\n output.push({ topic:\"INSERT OR REPLACE INTO sensor_aggr (epoch,device,sensor,value) VALUES(\"+fromdate+\",'\"+msg.payload[i].device+\"','\"+msg.payload[i].sensor+\"_max',\"+msg.payload[i].value+\")\" });\n}\n\nreturn [output];","outputs":1,"noerr":0,"x":656.2500152587891,"y":497.50009536743164,"wires":[["c1c4ff21.c72f4"]]},{"id":"e6cc72c.c49f89","type":"function","z":"27f25c95.8feb34","name":"SQL Updates","func":"var output = [];\nvar d = new Date();\nvar epoch = d.getTime();\n// Go to previous day\nd.setTime(d.getTime()-(24*60*60*1000));\n// calculate begining of the day\nd.setHours(0);\nd.setMinutes(0);\nd.setSeconds(0);\nd.setMilliseconds(0);\nvar fromdate = d.getTime();\n\nfor (var i=0; i<msg.payload.length; i++) {\n output.push({ topic:\"INSERT OR REPLACE INTO sensor_aggr (epoch,device,sensor,value) VALUES(\"+fromdate+\",'\"+msg.payload[i].device+\"','\"+msg.payload[i].sensor+\"_min',\"+msg.payload[i].value+\")\" });\n}\n\nreturn [output];","outputs":1,"noerr":0,"x":670.2500152587891,"y":599.5000953674316,"wires":[["c1c4ff21.c72f4"]]},{"id":"c1c4ff21.c72f4","type":"sqlite","z":"27f25c95.8feb34","mydb":"1c25415d.b8427f","name":"DB","x":908.5833282470703,"y":560.5000953674316,"wires":[[]]},{"id":"a4093916.fa4c38","type":"comment","z":"27f25c95.8feb34","name":"Calculate aggregated values (min,max,last)","info":"","x":395.5833282470703,"y":396.5000476837158,"wires":[]},{"id":"d8b33397.29f5b","type":"comment","z":"27f25c95.8feb34","name":"Insert aggregated values to new table","info":"","x":756.5833282470703,"y":448.5000476837158,"wires":[]},{"id":"45b3039d.9b2fac","type":"ui_chart","z":"27f25c95.8feb34","name":"Temperature","group":"27c3d951.18a4e6","order":2,"width":"","height":"","label":"","chartType":"line","xformat":"%H:%M:%S","interpolate":"linear","nodata":"No Data","ymin":"","ymax":"","removeOlder":"1","removeOlderUnit":"86400","x":628.25,"y":248.75,"wires":[["42239353.a0ebdc"],[]]},{"id":"42239353.a0ebdc","type":"file","z":"27f25c95.8feb34","name":"Chart dump","filename":"/home/pi/charts/miflora_temp.dump","appendNewline":true,"createDir":false,"overwriteFile":"true","x":822.25,"y":242.75,"wires":[]},{"id":"6a1cb72d.8b6348","type":"file in","z":"27f25c95.8feb34","name":"Chart dump","filename":"/home/pi/charts/miflora_temp.dump","format":"utf8","x":270.25,"y":250.75,"wires":[["f8940a20.feef98"]]},{"id":"294483f1.957e7c","type":"inject","z":"27f25c95.8feb34","name":"Startup","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":true,"x":116.25,"y":251.75,"wires":[["6a1cb72d.8b6348"]]},{"id":"f8940a20.feef98","type":"json","z":"27f25c95.8feb34","name":"","x":422.25,"y":250.75,"wires":[["45b3039d.9b2fac"]]},{"id":"9937da83.369da8","type":"comment","z":"27f25c95.8feb34","name":"Dashboard graph backup example","info":"This flow section shows how to back up the graph\nstate to a local file and restore that at startup.\nThis loads the last graph state so you don't get\nthe \"No data\" screen after node red restart.","x":187.5,"y":191.25,"wires":[]},{"id":"8b341a53.4d5ed8","type":"function","z":"27f25c95.8feb34","name":"SQL","func":"// This will handle any device and any attribute as long as it is in the DB\nvar Week = 604800000 ; //7 Days\nvar Day = 86400000 ; // 1 Days\nvar d = new Date();\nvar epoch = d.getTime();\nvar fromdate = epoch - Day;\nvar enddate = epoch;\nvar output = [];\n\nfor (var property in msg.payload) {\n if (msg.payload.hasOwnProperty(property)) {\n //output.push({ payload: property + \" has value \"+msg.payload[property] });\n if (msg.payload[property]) {\n var parts = property.split(\"/\");\n if (parts[0]===\"period\") {\n switch (parts[1]) {\n case \"today\":\n fromdate = epoch-Day;\n enddate = epoch;\n break;\n case \"yesterday\":\n fromdate = epoch-2*Day;\n enddate = epoch-Day;\n break;\n case \"week\":\n fromdate = epoch-Week;\n enddate = epoch;\n break;\n }\n } else {\n output.push({ topic: \"SELECT * FROM sensor_data WHERE device='\"+parts[0]+\"' AND sensor='\"+parts[1]+\"' AND epoch >= \" + fromdate + \" AND epoch <= \" + enddate });\n }\n }\n }\n}\n\noutput[output.length-1].complete=true;\n\nreturn [ output ];\n\n//msg.topic = \"SELECT * FROM sensor_data WHERE device='growatt' AND sensor='power' AND epoch >= \" + fromdate + \" AND epoch <= \" + epoch ;\n//return msg;","outputs":1,"noerr":0,"x":316.25,"y":895,"wires":[["bbf81e68.a7ee1"]]},{"id":"bbf81e68.a7ee1","type":"sqlite","z":"27f25c95.8feb34","mydb":"1c25415d.b8427f","name":"DB","x":462.25,"y":896,"wires":[["ee672a38.931748"]]},{"id":"2caa3402.536a0c","type":"ui_chart","z":"27f25c95.8feb34","name":"Chart","group":"bd903d6.d9fc7c","order":0,"width":0,"height":0,"label":"","chartType":"line","legend":"false","xformat":"%a %H:%M","interpolate":"linear","nodata":"","ymin":"","ymax":"","removeOlder":"1","removeOlderUnit":"604800","x":809.25,"y":996,"wires":[[],[]]},{"id":"ac39fc2.3779d","type":"function","z":"27f25c95.8feb34","name":"Prep","func":"var msg2 = [];\n\n// this is the logic when there are multiple data sets are received\nfor (var i=0; i<msg.payload.length; i++) {\n var output = [];\n for (var j=0; j<msg.payload[i].length; j++) {\n output.push([msg.payload[i][j].epoch, msg.payload[i][j].value]);\n }\n msg2.push({ key: msg.payload[i][0].device+\"/\"+msg.payload[i][0].sensor, values : output});\n //msg2.push({ key: \"test\", values : output});\n}\n\nmsg.payload=msg2;\n//msg.payload = [ { key: \"Power\", values : output} ];\n//msg.topic = \"Power\";\nreturn msg;","outputs":1,"noerr":0,"x":793.25,"y":898,"wires":[["2caa3402.536a0c"]]},{"id":"18961c51.640204","type":"inject","z":"27f25c95.8feb34","name":"Reset chart","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":151.25,"y":1018,"wires":[["379bf862.755b58"]]},{"id":"379bf862.755b58","type":"function","z":"27f25c95.8feb34","name":"Empty payload","func":"msg.payload = [];\nreturn msg;","outputs":1,"noerr":0,"x":479.25,"y":1017,"wires":[["2caa3402.536a0c"]]},{"id":"45a070cf.ba722","type":"ui_form","z":"27f25c95.8feb34","name":"Data points","label":"","group":"1e17cd0b.862863","order":0,"width":0,"height":0,"options":[{"label":"Period: today","value":"period/today","type":"checkbox","required":false},{"label":"Period: yesterday","value":"period/yesterday","type":"checkbox","required":false},{"label":"Period: this week","value":"period/week","type":"checkbox","required":false},{"label":"Miflora temperature","value":"miflora/temp","type":"checkbox","required":false},{"label":"Miflora battery level","value":"miflora/battery","type":"checkbox","required":false},{"label":"Miflora sunlight","value":"miflora/sunlight","type":"checkbox","required":false},{"label":"Miflora soil moisture","value":"miflora/moisture","type":"checkbox","required":false},{"label":"Miflora soil fertility","value":"miflora/fertility","type":"checkbox","required":false},{"label":"Solar power","value":"growatt/power","type":"checkbox","required":false},{"label":"Solar voltage","value":"growatt/voltage","type":"checkbox","required":false}],"formValue":{"period/today":false,"period/yesterday":false,"period/week":false,"miflora/temp":false,"miflora/battery":false,"miflora/sunlight":false,"miflora/moisture":false,"miflora/fertility":false,"growatt/power":false,"growatt/voltage":false},"payload":"","topic":"","x":121.25,"y":842,"wires":[["8b341a53.4d5ed8"]]},{"id":"ee672a38.931748","type":"join","z":"27f25c95.8feb34","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","timeout":"","count":"","x":604.25,"y":896,"wires":[["ac39fc2.3779d"]]},{"id":"9c5689c9.61db68","type":"comment","z":"27f25c95.8feb34","name":"Dynamic report example","info":"This flow can generate graph based from data stored\nin the database and display multiple data points\nin a single graph.","x":153.75,"y":787.5,"wires":[]},{"id":"1c25415d.b8427f","type":"sqlitedb","z":"","db":"/home/pi/sqlite/nodered"},{"id":"27c3d951.18a4e6","type":"ui_group","z":"","name":"MiFlora","tab":"156af96f.f8fd27","order":2,"disp":true,"width":"6"},{"id":"bd903d6.d9fc7c","type":"ui_group","z":"","name":"Report","tab":"1e3fe400.0baf5c","order":2,"disp":true,"width":"18"},{"id":"1e17cd0b.862863","type":"ui_group","z":"","name":"Selection","tab":"1e3fe400.0baf5c","order":1,"disp":true,"width":"5"},{"id":"156af96f.f8fd27","type":"ui_tab","z":"","name":"Home","icon":"home","order":"1"},{"id":"1e3fe400.0baf5c","type":"ui_tab","z":"","name":"Reports","icon":"dashboard","order":9}] |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment