Storing, displaying, aggregating time series data
This flow demonstrates a few things:
- How to store multiple data points in the table: generate multiple INSERT INTO
- Back up dashboard graph state to a file and restore on startup: obviously the graph needs another input to feed new data in.
- Example on how to aggregate data stored in database: get max, min and last values daily and store aggregated values separately.
- Generate graphs from values stored in the database using multiple data points in a single graph.
The entire process and the use case is explained in this video: https://youtu.be/ccKspiI8FRw
I am using SQLite for database storage and SQLite node integrating with Node Red.
[{"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}]
