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.

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":"1224c98c.60aba6","type":"ui_chart","z":"6eb37a1d.ee8f34","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,"useOneColor":false,"colors":["#00e68c","#2d2da8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"x":675,"y":627,"wires":[[],[]]},{"id":"260e68c.f78a598","type":"mysql","z":"6eb37a1d.ee8f34","mydb":"febbd048.dd9fe","name":"MYSQL","x":293,"y":676,"wires":[["3694fb99.98d404","3ac40785.9536b8"]]},{"id":"3694fb99.98d404","type":"change","z":"6eb37a1d.ee8f34","name":"Format data","rules":[{"t":"set","p":"payload","pt":"msg","to":"[\t  { \"field\": \"data1\", \"title\": \"data1 label\" },\t  { \"field\": \"data2\", \"title\": \"data2 label\" }\t].\t(\t  $xaxis := \"time\";\t  $yaxis := $.field;\t  $title := $.title;\t  [\t    $$.payload {\t      \"key\": $title,\t      \"values\": $.[ $lookup($, $xaxis), $lookup($, $yaxis) ]\t    }\t  ]\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":496,"y":627,"wires":[["1224c98c.60aba6"]]},{"id":"522e85c7.6da59c","type":"function","z":"6eb37a1d.ee8f34","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":110,"y":677,"wires":[["cd965154.01254"]]},{"id":"cd447d99.56be8","type":"template","z":"6eb37a1d.ee8f34","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":299,"y":730,"wires":[[]]},{"id":"3ac40785.9536b8","type":"debug","z":"6eb37a1d.ee8f34","name":"","active":true,"console":"false","complete":"false","x":496,"y":676,"wires":[]},{"id":"e640b60d.2784c8","type":"inject","z":"6eb37a1d.ee8f34","name":"Timestamp","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":110,"y":627,"wires":[["522e85c7.6da59c"]]},{"id":"5bcaf721.081c28","type":"comment","z":"6eb37a1d.ee8f34","name":"Flow to query database and format for chart","info":"","x":216,"y":577,"wires":[]},{"id":"cc4c7c33.e807a","type":"mysql","z":"6eb37a1d.ee8f34","mydb":"febbd048.dd9fe","name":"MYSQL","x":269,"y":507,"wires":[[]]},{"id":"f3262100.b872a","type":"template","z":"6eb37a1d.ee8f34","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":116,"y":507,"wires":[["cc4c7c33.e807a"]]},{"id":"4475c611.55ac28","type":"comment","z":"6eb37a1d.ee8f34","name":"Flow to insert data into the database","info":"","x":185,"y":457,"wires":[]},{"id":"cd965154.01254","type":"template","z":"6eb37a1d.ee8f34","name":"Format query 1","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT data1,data2,time FROM dbasename WHERE time > {{payload}}","output":"str","x":295,"y":626,"wires":[["260e68c.f78a598"]]},{"id":"8880d363.148ac","type":"ui_group","z":"","name":"Thermostat demo","tab":"db58ad1a.e37a8","order":2,"disp":true,"width":"6"},{"id":"febbd048.dd9fe","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"}]
Paul-Reed

Flow Info

created 3 weeks, 5 days ago

Node Types

Core
  • change (x1)
  • comment (x2)
  • debug (x1)
  • function (x1)
  • inject (x1)
  • template (x3)
Other

Tags

  • chart
  • mysql
Copy this flow JSON to your clipboard and then import into Node-RED using the Import From > Clipboard (Ctrl-I) menu option