MYSQL example to chart data
Two flows, one to write feed data into a MYSQL database, and a second flow to retrieve the data, and plot it in a chart node.
Inserting data into the database
The flows assume a database called dbasename with 3 columns, data1, data2 and time.data1 & data2 are the data values which you wish to plot, and time is a 13 digit epoch timestamp. Example - 1503622800506.
The 3 values are fed into the 'Format data' template node via msg.data1, msg.data2 & msg.time.
Flow to query database and format for a chart node
The Format data change node uses a jsonata expression to format the MYSQL data so that it plots OK in a chart node. Further data feeds can be added to the expression in the format - { "field": "dataname", "title": "name to display in tooltip" },.
The Format query 1 template node retreives the full 24hrs data feeds, but substituting it with Format query 2 groups the data feeds into hourly intervals, which are averaged, so there is just one datapoint per hour representing an hour's data averaged. This is particularly useful when trying to display a large number of datapoints, such as data over a long timeframe.
Compatibility
Flow intended to be used with dashboard v2.5.0 and later. Not compatible with deprecated (pre v2.5.0) data format.
These code snippets have been kindly developed and contributed by Steve Rickus
Anyone wishing to add to this flow, please contact me via github - @Paul-Reed or Slack - @rossoreed
[{"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"}]
