|
[{"id":"d205c5ce.1feca8","type":"ui_chart","z":"a444a9ff.e7a408","name":"24 hours data","group":"8880d363.148ac","order":2,"width":"0","height":"0","label":"Chart","chartType":"line","legend":"false","xformat":"HH:mm","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"24","removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"colors":["#00e68c","#2d2da8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"x":678,"y":2177,"wires":[[],[]]},{"id":"2a63c806.ae4db8","type":"mysql","z":"a444a9ff.e7a408","mydb":"a844720c.608d6","name":"MYSQL","x":296,"y":2226,"wires":[["3d0af460.41906c","a03b0066.3ff5a"]]},{"id":"86ab4360.50c6c","type":"function","z":"a444a9ff.e7a408","name":"Criteria","func":"var timeE = msg.payload;\n//Restrict the query to pull the last 24hrs\n//of data instead of the whole db\nmsg.payload = (timeE - (1000*60*60*24));\n node.status({text:msg.payload});\nreturn msg;","outputs":1,"noerr":0,"x":113,"y":2227,"wires":[["308dd6b1.2a193a"]]},{"id":"11c8f8a2.d97147","type":"template","z":"a444a9ff.e7a408","name":"Format query 2","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT\n CEILING(time/3600000)*3600000 AS timestamp,\n AVG(data1) AS `data1`,\n AVG(data2) AS `data2`\nFROM dbasename\nWHERE time > {{payload}}\nGROUP BY `timestamp`;","output":"str","x":302,"y":2280,"wires":[[]]},{"id":"3d0af460.41906c","type":"debug","z":"a444a9ff.e7a408","name":"","active":true,"console":"false","complete":"false","x":499,"y":2226,"wires":[]},{"id":"272494b.eb3d36c","type":"inject","z":"a444a9ff.e7a408","name":"Timestamp","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":113,"y":2177,"wires":[["86ab4360.50c6c"]]},{"id":"755e08a5.08de88","type":"comment","z":"a444a9ff.e7a408","name":"Flow to query database and format for chart","info":"","x":219,"y":2127,"wires":[]},{"id":"433d9ca4.076774","type":"mysql","z":"a444a9ff.e7a408","mydb":"a844720c.608d6","name":"MYSQL","x":272,"y":2057,"wires":[[]]},{"id":"d59a31e8.d3772","type":"template","z":"a444a9ff.e7a408","name":"Format data","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO `dbasename` (`data1`,`data2`,`time`) VALUES ({{data1}},{{data2}},{{time}})","output":"str","x":119,"y":2057,"wires":[["433d9ca4.076774"]]},{"id":"2b71188f.3e2428","type":"comment","z":"a444a9ff.e7a408","name":"Flow to insert data into the database","info":"","x":188,"y":2007,"wires":[]},{"id":"308dd6b1.2a193a","type":"template","z":"a444a9ff.e7a408","name":"Format query 1","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT data1,data2,time FROM dbasename WHERE time > {{payload}}","output":"str","x":298,"y":2176,"wires":[["2a63c806.ae4db8"]]},{"id":"a03b0066.3ff5a","type":"change","z":"a444a9ff.e7a408","name":"Format data","rules":[{"t":"set","p":"payload","pt":"msg","to":"(\t $series := [\t { \"field\": \"data1\", \"label\": \"data1 label\" },\t { \"field\": \"data2\", \"label\": \"data2 label\" }\t ];\t $xaxis := \"timestamp\";\t [\t {\t \"series\": $series.label,\t \"data\": $series.[\t (\t $yaxis := $.field;\t $$.payload.{\t \"x\": $lookup($, $xaxis),\t \"y\": $lookup($, $yaxis)\t }\t )\t ]\t }\t ]\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":502,"y":2177,"wires":[["d205c5ce.1feca8"]]},{"id":"8880d363.148ac","type":"ui_group","z":"","name":"Thermostat demo","tab":"db58ad1a.e37a8","order":2,"disp":true,"width":"6"},{"id":"a844720c.608d6","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"nodered","tz":""},{"id":"db58ad1a.e37a8","type":"ui_tab","z":"","name":"Test stuff","icon":"dashboard"}] |
I managed to get it working with data1, data2 as varchar(50) and time as bigint(20). data type int is limited to a length of 11.
However i could get the 24 hour display somehow. not sure what is wrong. nevertheless good example or me to get started
[{"id":"7aa8d134.a3b1","type":"tab","label":"Flow 2","disabled":false,"info":""},{"id":"c4caed52.577e1","type":"ui_chart","z":"7aa8d134.a3b1","name":"24 hours data","group":"5d39add5.eaaaf4","order":1,"width":0,"height":0,"label":"Chart","chartType":"line","legend":"false","xformat":"HH:mm","interpolate":"linear","nodata":"","dot":false,"ymin":"0","ymax":"100","removeOlder":"24","removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#00e68c","#2d2da8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"outputs":1,"x":1020,"y":360,"wires":[[]]},{"id":"2c06ef25.9d119","type":"mysql","z":"7aa8d134.a3b1","mydb":"3b28a473.7549ac","name":"DB Query","x":620,"y":360,"wires":[["2d5fabdc.b8db94","a4e4c9d5.643de8"]]},{"id":"96805c4.3f7faa","type":"function","z":"7aa8d134.a3b1","name":"Criteria","func":"var timeE = msg.payload;\n//Restrict the query to pull the last 24hrs\n//of data instead of the whole db\nmsg.payload = (timeE - (1000*60*60*24));\n node.status({text:msg.payload});\nreturn msg;","outputs":1,"noerr":0,"x":260,"y":360,"wires":[["ef795a3d.b6d828"]]},{"id":"2d5fabdc.b8db94","type":"debug","z":"7aa8d134.a3b1","name":"DB Query Debug","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":830,"y":420,"wires":[]},{"id":"e0101809.361478","type":"inject","z":"7aa8d134.a3b1","name":"Timestamp","topic":"","payload":"","payloadType":"date","repeat":"1","crontab":"","once":false,"onceDelay":"","x":110,"y":360,"wires":[["96805c4.3f7faa"]]},{"id":"bcd50ae3.78ea48","type":"comment","z":"7aa8d134.a3b1","name":"Flow to query database and format for chart","info":"","x":361,"y":290,"wires":[]},{"id":"a18db9fa.ef7cf8","type":"mysql","z":"7aa8d134.a3b1","mydb":"3b28a473.7549ac","name":"DB Insert","x":500,"y":140,"wires":[["c664bb3e.3055c8"]]},{"id":"3f2d1631.0d699a","type":"comment","z":"7aa8d134.a3b1","name":"Flow to insert data into the database","info":"","x":330,"y":90,"wires":[]},{"id":"a4e4c9d5.643de8","type":"change","z":"7aa8d134.a3b1","name":"Format data","rules":[{"t":"set","p":"payload","pt":"msg","to":"(\t $series := [\t { \"field\": \"data1\", \"label\": \"data1 label\" },\t { \"field\": \"data2\", \"label\": \"data2 label\" }\t ];\t $xaxis := \"timestamp\";\t [\t {\t \"series\": $series.label,\t \"data\": $series.[\t (\t $yaxis := $.field;\t $$.payload.{\t \"x\": $lookup($, $xaxis),\t \"y\": $lookup($, $yaxis)\t }\t )\t ]\t }\t ]\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":810,"y":360,"wires":[["c4caed52.577e1","e372cef6.464e8"]]},{"id":"b3c9b218.5c706","type":"ui_form","z":"7aa8d134.a3b1","name":"User Form","label":"","group":"ef46267e.8456b8","order":1,"width":0,"height":0,"options":[{"label":"data1","value":"data1","type":"number","required":true,"rows":null},{"label":"data2","value":"data2","type":"number","required":true,"rows":null}],"formValue":{"data1":"","data2":""},"payload":"","submit":"submit","cancel":"cancel","topic":"","x":90,"y":140,"wires":[["abb295e9.ef6d58","f4e75565.656a98"]]},{"id":"abb295e9.ef6d58","type":"debug","z":"7aa8d134.a3b1","name":"Form Debug","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":290,"y":220,"wires":[]},{"id":"2d08a329.f9753c","type":"debug","z":"7aa8d134.a3b1","name":"Query Debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":510,"y":220,"wires":[]},{"id":"f4e75565.656a98","type":"function","z":"7aa8d134.a3b1","name":"Query","func":"var data1 = msg.payload.data1\nvar data2 = msg.payload.data2\nvar datatime = Date.now()\nquery = \"INSERT INTO
charttest(
data1,
data2,
datatime) VALUES (\" + data1 + \",\" + data2 + \",\" + datatime + \")\"\n// query = \"INSERT INTO
charttest(
data1,
data2) VALUES (\" + data1 + \",\" + data2 + \")\"\nmsg.topic = query; //Put query into the msg.topic\nreturn msg; //Return the query","outputs":1,"noerr":0,"x":270,"y":140,"wires":[["a18db9fa.ef7cf8","2d08a329.f9753c"]]},{"id":"c664bb3e.3055c8","type":"debug","z":"7aa8d134.a3b1","name":"DB Insert Debug","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":770,"y":220,"wires":[]},{"id":"e372cef6.464e8","type":"debug","z":"7aa8d134.a3b1","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1020,"y":300,"wires":[]},{"id":"ef795a3d.b6d828","type":"function","z":"7aa8d134.a3b1","name":"Format Query 1","func":"var datatime = msg.payload\nquery = \"SELECT data1,data2,datatime FROM charttest WHERE datatime > \" + datatime\nmsg.topic = query; //Put query into the msg.topic\nreturn msg; //Return the query","outputs":1,"noerr":0,"x":440,"y":360,"wires":[["2c06ef25.9d119"]]},{"id":"5d39add5.eaaaf4","type":"ui_group","z":"","name":"Data Display","tab":"176c2e20.abe2e2","order":2,"disp":true,"width":8,"collapse":false},{"id":"3b28a473.7549ac","type":"MySQLdatabase","z":"","name":"Database","host":"localhost","port":"3306","db":"noderedtest","tz":""},{"id":"ef46267e.8456b8","type":"ui_group","z":"","name":"User Form","tab":"176c2e20.abe2e2","order":2,"disp":true,"width":8,"collapse":false},{"id":"176c2e20.abe2e2","type":"ui_tab","z":"","name":"User Form Test","icon":"dashboard","disabled":false,"hidden":false}]