MYSQL example to chart data

README.md

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

flow.json

[{"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"}]

Thank you for these useful flows.

On the example provided above and at the official page, the node Format query 1 should definemsg.topic, not msg.payload.

Thanks, I've edited the typo, and also updated the flow to work with dashboard v2.5.0.
NOTE: - no longer works with deprecated (pre v2.5.0) data format.

Hello, what is exactly the mysql table data types?
I use data1, data2 as mediumtext and time as int (13).

What is wrong while i get just:

"Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ',)' at line 1"

Thankyou!

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}]