Good day to all
I am new to Nodered and would appreciate some help on the below:
I have some soil moisture and temperature sensors that send via MQTT their data and I want to log them in MYSQL. Till now i can plot the graphs using NODE RED dashboard, but when my server reboots my data are lost.
Can you please give me a sample of how to configure to log MQTT data to Mysql?
Thanks in advance
Evripidis
This is not very difficult if you already have a database and one or more tables ready. I am using a function node for this. Please confirm that the db and table is ready, and we can continue. You may well also share the flow you have so far.
Here is something you can try. I assume that both mqtt broker and MySQL is up and running, and you have a simple table to store the data into.
var value1= parseFloat(msg.payload);
// replace myTable with your db table name
msg.topic = "INSERT INTO myTable (value1) VALUES (?)";
msg.payload = [value1];
return msg;
I guess you have more than 1 value to be saved to database, but as soon as you are fine with one, it should be easy to expand with more values.
Good luck, and let me know how you progress!
Could someone with admin rights move this to #general? as it’s a general question rather than sharing a completed project.
Unless you have a good reason to use mysql I suggest you also have a look at influxdb which is a database specifically designed for storing time series data.
Dear Solano8
Thank you for your Help. I followed your instructions and it works. I need to put a time stamp and the sensor name too on the function, to be able to log data from Multiple sensors. Can you help me on that too?
I created a table named Sensor_data with the following columns:
idSensor_data (is the primary key integer , auto increment)
Sensor_Val
Time
Sensor_Name
Here below the testing flow:
[{"id":"e30e03f.c9c7","type":"tab","label":"Flow 2","disabled":false,"info":""},{"id":"19f0d47.ed7422c","type":"mqtt in","z":"e30e03f.c9c7","name":"","topic":"qiot/things/admin/unowifi/18ds20","qos":"0","datatype":"auto","broker":"ac11df20.5dcab","x":194.01734924316406,"y":172.0104217529297,"wires":[["6781d396.25f9bc"]]},{"id":"6781d396.25f9bc","type":"delay","z":"e30e03f.c9c7","name":"","pauseType":"rate","timeout":"5","timeoutUnits":"seconds","rate":"5","nbRateUnits":"1","rateUnits":"minute","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":true,"x":429.20837020874023,"y":175.92009925842285,"wires":[["ea451d4a.46b2a"]]},{"id":"28dc929e.5c221e","type":"debug","z":"e30e03f.c9c7","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":755.2118530273438,"y":198.60414505004883,"wires":},{"id":"ea451d4a.46b2a","type":"function","z":"e30e03f.c9c7","name":"","func":"var Sensor_Val= parseFloat(msg.payload);\n\n// replace myTable with your db table name \nmsg.topic = "INSERT INTO Sensor_data (Sensor_Val) VALUES (?)";\nmsg.payload = [Sensor_Val];\nreturn msg;","outputs":1,"noerr":0,"x":589.1978988647461,"y":273.3124294281006,"wires":[["8444ae9c.6aa8d","28dc929e.5c221e"]]},{"id":"23f6a1c3.baa81e","type":"debug","z":"e30e03f.c9c7","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":922.2082824707031,"y":369.6145420074463,"wires":},{"id":"8444ae9c.6aa8d","type":"mysql","z":"e30e03f.c9c7","mydb":"e9320fd5.251b8","name":"","x":735.2047996520996,"y":368.9028072357178,"wires":[["23f6a1c3.baa81e"]]},{"id":"ac11df20.5dcab","type":"mqtt-broker","z":"","name":"","broker":"375002bfab8d.sn.mynetname.net","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"e9320fd5.251b8","type":"MySQLdatabase","z":"c90a7c94.f0457","host":"localhost","port":"3306","db":"node_red","tz":""}]
Thanks in advance!
Seems I can't import you code.
Please post it again and use "Preformatted text" (tools menu) or 3 backticks "```" before and after your code for make it readable for me.
Regards,
See below:
[
{
"id": "e30e03f.c9c7",
"type": "tab",
"label": "Flow 2",
"disabled": false,
"info": ""
},
{
"id": "19f0d47.ed7422c",
"type": "mqtt in",
"z": "e30e03f.c9c7",
"name": "",
"topic": "qiot/things/admin/unowifi/18ds20",
"qos": "0",
"datatype": "auto",
"broker": "ac11df20.5dcab",
"x": 194.01734924316406,
"y": 172.0104217529297,
"wires": [
[
"6781d396.25f9bc"
]
]
},
{
"id": "6781d396.25f9bc",
"type": "delay",
"z": "e30e03f.c9c7",
"name": "",
"pauseType": "rate",
"timeout": "5",
"timeoutUnits": "seconds",
"rate": "5",
"nbRateUnits": "1",
"rateUnits": "minute",
"randomFirst": "1",
"randomLast": "5",
"randomUnits": "seconds",
"drop": true,
"x": 429.20837020874023,
"y": 175.92009925842285,
"wires": [
[
"ea451d4a.46b2a"
]
]
},
{
"id": "28dc929e.5c221e",
"type": "debug",
"z": "e30e03f.c9c7",
"name": "",
"active": false,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"x": 755.2118530273438,
"y": 198.60414505004883,
"wires": []
},
{
"id": "ea451d4a.46b2a",
"type": "function",
"z": "e30e03f.c9c7",
"name": "",
"func": "var Sensor_Val= parseFloat(msg.payload);\n\n// replace myTable with your db table name \nmsg.topic = \"INSERT INTO Sensor_data (Sensor_Val) VALUES (?)\";\nmsg.payload = [Sensor_Val];\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 589.1978988647461,
"y": 273.3124294281006,
"wires": [
[
"8444ae9c.6aa8d",
"28dc929e.5c221e"
]
]
},
{
"id": "23f6a1c3.baa81e",
"type": "debug",
"z": "e30e03f.c9c7",
"name": "",
"active": false,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"x": 922.2082824707031,
"y": 369.6145420074463,
"wires": []
},
{
"id": "8444ae9c.6aa8d",
"type": "mysql",
"z": "e30e03f.c9c7",
"mydb": "bc8ab442.0892a8",
"name": "",
"x": 735.2047996520996,
"y": 368.9028072357178,
"wires": [
[
"23f6a1c3.baa81e"
]
]
},
{
"id": "ac11df20.5dcab",
"type": "mqtt-broker",
"z": "",
"name": "",
"broker": "375002bfab8d.sn.mynetname.net",
"port": "1883",
"clientid": "",
"usetls": false,
"compatmode": true,
"keepalive": "60",
"cleansession": true,
"birthTopic": "",
"birthQos": "0",
"birthPayload": "",
"closeTopic": "",
"closeQos": "0",
"closePayload": "",
"willTopic": "",
"willQos": "0",
"willPayload": ""
},
{
"id": "bc8ab442.0892a8",
"type": "MySQLdatabase",
"z": "",
"host": "127.0.0.1",
"port": "3306",
"db": "node_red",
"tz": ""
}
]
I have added the moment node to the flow to facilitate timestamp. Could be done directly in the function node too with Date(), but if you're not familiar with this node, it's a nice tool, especially if you want to display a more readable date string to dashboard or something.
So you need to download "node-red-contrib-moment" first. Depending on where you are, you might have to adjust the node setting so it displays right time according to your timezone.
Then check if this works for you:
[
{
"id": "aabda12f.41bb8",
"type": "tab",
"label": "Flow 2",
"disabled": false,
"info": ""
},
{
"id": "38bc5ac.397b4a6",
"type": "mqtt in",
"z": "aabda12f.41bb8",
"name": "",
"topic": "qiot/things/admin/unowifi/18ds20",
"qos": "0",
"datatype": "auto",
"broker": "d81bff68.dba84",
"x": 144.01736450195312,
"y": 53.26042938232422,
"wires": [
[
"88ad2489.330128",
"e953c269.280ae"
]
]
},
{
"id": "88ad2489.330128",
"type": "delay",
"z": "aabda12f.41bb8",
"name": "",
"pauseType": "rate",
"timeout": "5",
"timeoutUnits": "seconds",
"rate": "5",
"nbRateUnits": "1",
"rateUnits": "minute",
"randomFirst": "1",
"randomLast": "5",
"randomUnits": "seconds",
"drop": true,
"x": 125.4583740234375,
"y": 159.67011260986328,
"wires": [
[
"1e963d28.6601f3"
]
]
},
{
"id": "8b2d08a8.2a1708",
"type": "function",
"z": "aabda12f.41bb8",
"name": "prepare data to sql",
"func": "var sensorVal= parseFloat(msg.payload);\nvar timeStamp = msg.timestamp;\nvar sensorName = \"DS18B20\"; // defined string. Or should this be aquired from the topic?\n\n// replace myTable with your db table name \nmsg.topic = \"INSERT INTO Sensor_data (Sensor_Val, Time, Sensor_Name) VALUES (?,?,?)\";\nmsg.payload = [sensorVal, timeStamp, sensorName];\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 596.6978149414062,
"y": 159.31246185302734,
"wires": [
[
"3a166572.6d662a",
"8e589c87.fb804"
]
]
},
{
"id": "efc4ba9b.169888",
"type": "debug",
"z": "aabda12f.41bb8",
"name": "",
"active": false,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"x": 851.708251953125,
"y": 226.6145248413086,
"wires": []
},
{
"id": "3a166572.6d662a",
"type": "mysql",
"z": "aabda12f.41bb8",
"mydb": "8713b3e5.9fc17",
"name": "",
"x": 835.2047119140625,
"y": 157.6528549194336,
"wires": [
[
"efc4ba9b.169888"
]
]
},
{
"id": "1e963d28.6601f3",
"type": "moment",
"z": "aabda12f.41bb8",
"name": "",
"topic": "",
"input": "",
"inputType": "msg",
"inTz": "Europe/Oslo",
"adjAmount": 0,
"adjType": "days",
"adjDir": "add",
"format": "",
"locale": "nb_NO",
"output": "timestamp",
"outputType": "msg",
"outTz": "Europe/Oslo",
"x": 337.49993896484375,
"y": 159.50005340576172,
"wires": [
[
"8b2d08a8.2a1708"
]
]
},
{
"id": "e953c269.280ae",
"type": "debug",
"z": "aabda12f.41bb8",
"name": "",
"active": false,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "true",
"targetType": "full",
"x": 104.24993896484375,
"y": 194.7500228881836,
"wires": []
},
{
"id": "8e589c87.fb804",
"type": "debug",
"z": "aabda12f.41bb8",
"name": "complete msg",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "true",
"targetType": "full",
"x": 857.5,
"y": 125.0000228881836,
"wires": []
},
{
"id": "d81bff68.dba84",
"type": "mqtt-broker",
"z": "",
"name": "",
"broker": "375002bfab8d.sn.mynetname.net",
"port": "1883",
"clientid": "",
"usetls": false,
"compatmode": true,
"keepalive": "60",
"cleansession": true,
"birthTopic": "",
"birthQos": "0",
"birthPayload": "",
"closeTopic": "",
"closeQos": "0",
"closePayload": "",
"willTopic": "",
"willQos": "0",
"willPayload": ""
},
{
"id": "8713b3e5.9fc17",
"type": "MySQLdatabase",
"z": "",
"host": "127.0.0.1",
"port": "3306",
"db": "node_red",
"tz": ""
}
]
And if you don't want to use the moment node here, replace the second line in function with:
var timeStamp = new Date();
Thanks for your Valuable help, it works with moment ! The only problem now is that on SQL table date is 1988-01-29 23:00:00. Any clue why this is happening?
Hm. Try to replace the second line in function as mentioned (and bypass moment) to see if that gives the same result.
… and use some debug nodes to check what the date looks like on its way to db. By default, the node only displays msg.payload, but can be instructed to show msg.timestamp instead or in this case complete msg could be of interest. In my flow it shows the right date and time. I assume you know how to see the full debug message in the window to the right.
This is my current working setup. I have removed the db node to avoid error messages (no db connected) since I am only interested in the output from the function for now.
[{"id":"aabda12f.41bb8","type":"tab","label":"dksmar","disabled":false,"info":""},{"id":"38bc5ac.397b4a6","type":"mqtt in","z":"aabda12f.41bb8","name":"","topic":"qiot/things/admin/unowifi/18ds20","qos":"0","datatype":"auto","broker":"d81bff68.dba84","x":144.01736450195312,"y":74.51043701171875,"wires":[["88ad2489.330128","e953c269.280ae"]]},{"id":"88ad2489.330128","type":"delay","z":"aabda12f.41bb8","name":"","pauseType":"rate","timeout":"5","timeoutUnits":"seconds","rate":"5","nbRateUnits":"1","rateUnits":"minute","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":true,"x":394.2083740234375,"y":75.42008972167969,"wires":[["8b2d08a8.2a1708","944b70a8.bf444"]]},{"id":"8b2d08a8.2a1708","type":"function","z":"aabda12f.41bb8","name":"prepare data to sql","func":"var sensorVal= parseFloat(msg.payload);\n// var timeStamp = msg.timestamp;\nvar timeStamp = new Date();\nvar sensorName = \"DS18B20\"; // defined string. Or should this be aquired from the topic?\n\n// replace myTable with your db table name \nmsg.topic = \"INSERT INTO Sensor_data (Sensor_Val, Time, Sensor_Name) VALUES (?,?,?)\";\nmsg.payload = [sensorVal, timeStamp, sensorName];\nreturn msg;","outputs":1,"noerr":0,"x":587.4478149414062,"y":75.56246948242187,"wires":[["8e589c87.fb804"]]},{"id":"e953c269.280ae","type":"debug","z":"aabda12f.41bb8","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":376.74993896484375,"y":32.250030517578125,"wires":[]},{"id":"8e589c87.fb804","type":"debug","z":"aabda12f.41bb8","name":"complete msg","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":813.75,"y":75.75003051757812,"wires":[]},{"id":"944b70a8.bf444","type":"debug","z":"aabda12f.41bb8","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":547.5,"y":37.5,"wires":[]},{"id":"d81bff68.dba84","type":"mqtt-broker","z":"","name":"","broker":"375002bfab8d.sn.mynetname.net","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""}]
Thanks! finally I used you suggestion with var timeStamp = new Date(); and works fine now.
I try to show back in graph the data collected by the Mysql based on example i found in
https://flows.nodered.org/flow/13c55d1aa11e864609e24fa534a1fa26
but unfortunately doesnt work. I tried to adjust it with my limited knowlege on this but still...
any clue how to show back the data?
Here below my complete flow
[
{
"id": "5ed7faf3.7da194",
"type": "tab",
"label": "Flow 2",
"disabled": false,
"info": ""
},
{
"id": "cef973e0.4f14",
"type": "mqtt in",
"z": "5ed7faf3.7da194",
"name": "",
"topic": "qiot/things/admin/unowifi/18ds20",
"qos": "0",
"datatype": "auto",
"broker": "79f88f2d.0eb26",
"x": 144.01736450195312,
"y": 53.26042938232422,
"wires": [
[
"8686ae1f.5c71a",
"c741bdcb.c539f"
]
]
},
{
"id": "8686ae1f.5c71a",
"type": "delay",
"z": "5ed7faf3.7da194",
"name": "",
"pauseType": "rate",
"timeout": "5",
"timeoutUnits": "seconds",
"rate": "10",
"nbRateUnits": "1",
"rateUnits": "hour",
"randomFirst": "1",
"randomLast": "5",
"randomUnits": "seconds",
"drop": true,
"x": 342.45838165283203,
"y": 155.67013597488403,
"wires": [
[
"596ee9c.cc98018"
]
]
},
{
"id": "596ee9c.cc98018",
"type": "function",
"z": "5ed7faf3.7da194",
"name": "prepare data to sql",
"func": "var sensorVal= parseFloat(msg.payload);\n//var timeStamp = msg.timestamp;\nvar timeStamp = new Date();\nvar sensorName = \"UnoWifiDS18B20\"; // defined string. Or should this be aquired from the topic?\n\n// replace myTable with your db table name \nmsg.topic = \"INSERT INTO Sensor_data (Sensor_Val, Time, Sensor_Name) VALUES (?,?,?)\";\nmsg.payload = [sensorVal, timeStamp, sensorName];\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 596.6978149414062,
"y": 159.31246185302734,
"wires": [
[
"9a8874a1.0f8f78",
"c2a3837c.19267"
]
]
},
{
"id": "cf4bf498.e89f28",
"type": "debug",
"z": "5ed7faf3.7da194",
"name": "",
"active": false,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"x": 1111.708251953125,
"y": 184.61454963684082,
"wires": []
},
{
"id": "9a8874a1.0f8f78",
"type": "mysql",
"z": "5ed7faf3.7da194",
"mydb": "bc8ab442.0892a8",
"name": "",
"x": 845.2047119140625,
"y": 157.6528549194336,
"wires": [
[
"cf4bf498.e89f28"
]
]
},
{
"id": "c741bdcb.c539f",
"type": "debug",
"z": "5ed7faf3.7da194",
"name": "",
"active": false,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "true",
"targetType": "full",
"x": 420.2499465942383,
"y": 41.75003433227539,
"wires": []
},
{
"id": "c2a3837c.19267",
"type": "debug",
"z": "5ed7faf3.7da194",
"name": "complete msg",
"active": false,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "true",
"targetType": "full",
"x": 857.5,
"y": 125.0000228881836,
"wires": []
},
{
"id": "f6193383.b7c27",
"type": "mqtt in",
"z": "5ed7faf3.7da194",
"name": "esp8266/18ds20",
"topic": "qiot/things/admin/ESPeasy/ds18b20/Temp",
"qos": "0",
"datatype": "auto",
"broker": "dfce0de9.dd384",
"x": 97.99996948242188,
"y": 361.01040840148926,
"wires": [
[
"846e36fc.fdc7d8"
]
]
},
{
"id": "b846580b.b51268",
"type": "mqtt in",
"z": "5ed7faf3.7da194",
"name": "esp8266 Rssi",
"topic": "qiot/things/admin/ESPeasy/sys_info/rssi",
"qos": "0",
"datatype": "auto",
"broker": "dfce0de9.dd384",
"x": 100.99995422363281,
"y": 969.0104389190674,
"wires": [
[]
]
},
{
"id": "972109d8.7ad7f8",
"type": "mqtt in",
"z": "5ed7faf3.7da194",
"name": "UNO wifi RSSI",
"topic": "arduino",
"qos": "0",
"datatype": "auto",
"broker": "dfce0de9.dd384",
"x": 101.99995422363281,
"y": 824.0107555389404,
"wires": [
[]
]
},
{
"id": "5dfeca1f.5ff2a4",
"type": "mqtt in",
"z": "5ed7faf3.7da194",
"name": "esp8266Soil/18ds20",
"topic": "qiot/things/admin/esp8266Soil/18ds20/Temp",
"qos": "0",
"datatype": "auto",
"broker": "dfce0de9.dd384",
"x": 102.99999237060547,
"y": 263.0106430053711,
"wires": [
[
"a36e7864.d03e58"
]
]
},
{
"id": "9ae687ad.e8bd58",
"type": "mqtt in",
"z": "5ed7faf3.7da194",
"name": "esp8266Soil RSSI",
"topic": "qiot/things/admin/esp8266Soil/sysinfo/rssi",
"qos": "0",
"datatype": "auto",
"broker": "dfce0de9.dd384",
"x": 110.99993896484375,
"y": 901.0107955932617,
"wires": [
[
"92bda667.dbde48",
"a6dab5f8.a5da88"
]
]
},
{
"id": "7935196d.886808",
"type": "comment",
"z": "5ed7faf3.7da194",
"name": "SENSOR RSSI",
"info": "",
"x": 105.18054962158203,
"y": 762.4931545257568,
"wires": []
},
{
"id": "92bda667.dbde48",
"type": "function",
"z": "5ed7faf3.7da194",
"name": "prepare data to sql",
"func": "var SensorRSSI_val= parseFloat(msg.payload);\n//var timeStamp = msg.timestamp;\nvar timeStamp = new Date();\nvar SensorRSSI_Name = \"esp8266Soil_RSSI\"; // defined string. Or should this be aquired from the topic?\n\n// replace myTable with your db table name \nmsg.topic = \"INSERT INTO SensorRSSI (SensorRSSI_val, SensorRSSI_time, SensorRSSI_Name) VALUES (?,?,?)\";\nmsg.payload = [SensorRSSI_val, timeStamp, SensorRSSI_Name];\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 493.25678634643555,
"y": 900.3196086883545,
"wires": [
[
"6832b15.bf2095",
"682a8ce2.833864"
]
]
},
{
"id": "1e5c1ae8.0c68f5",
"type": "debug",
"z": "5ed7faf3.7da194",
"name": "",
"active": false,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"x": 976.2672119140625,
"y": 905.6217641830444,
"wires": []
},
{
"id": "6832b15.bf2095",
"type": "mysql",
"z": "5ed7faf3.7da194",
"mydb": "bc8ab442.0892a8",
"name": "",
"x": 731.7636833190918,
"y": 898.6600017547607,
"wires": [
[
"1e5c1ae8.0c68f5"
]
]
},
{
"id": "682a8ce2.833864",
"type": "debug",
"z": "5ed7faf3.7da194",
"name": "complete msg",
"active": false,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "true",
"targetType": "full",
"x": 754.0589714050293,
"y": 866.0071697235107,
"wires": []
},
{
"id": "a6dab5f8.a5da88",
"type": "debug",
"z": "5ed7faf3.7da194",
"name": "",
"active": false,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"x": 472.18044662475586,
"y": 856.9273343086243,
"wires": []
},
{
"id": "a36e7864.d03e58",
"type": "function",
"z": "5ed7faf3.7da194",
"name": "prepare data to sql",
"func": "var sensorVal= parseFloat(msg.payload);\n//var timeStamp = msg.timestamp;\nvar timeStamp = new Date();\nvar sensorName = \"ESP8266SoilDS18B20\"; // defined string. Or should this be aquired from the topic?\n\n// replace myTable with your db table name \nmsg.topic = \"INSERT INTO Sensor_data (Sensor_Val, Time, Sensor_Name) VALUES (?,?,?)\";\nmsg.payload = [sensorVal, timeStamp, sensorName];\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 621.2567977905273,
"y": 261.65273237228394,
"wires": [
[
"7c1b707.71dbb9",
"a595e30b.e8793"
]
]
},
{
"id": "279343aa.0028fc",
"type": "debug",
"z": "5ed7faf3.7da194",
"name": "",
"active": false,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"x": 1136.267234802246,
"y": 286.9548201560974,
"wires": []
},
{
"id": "7c1b707.71dbb9",
"type": "mysql",
"z": "5ed7faf3.7da194",
"mydb": "bc8ab442.0892a8",
"name": "",
"x": 869.7636947631836,
"y": 259.9931254386902,
"wires": [
[
"279343aa.0028fc"
]
]
},
{
"id": "a595e30b.e8793",
"type": "debug",
"z": "5ed7faf3.7da194",
"name": "complete msg",
"active": false,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "true",
"targetType": "full",
"x": 882.0589828491211,
"y": 227.34029340744019,
"wires": []
},
{
"id": "846e36fc.fdc7d8",
"type": "function",
"z": "5ed7faf3.7da194",
"name": "prepare data to sql",
"func": "var sensorVal= parseFloat(msg.payload);\n//var timeStamp = msg.timestamp;\nvar timeStamp = new Date();\nvar sensorName = \"ESP8266_18DS20\"; // defined string. Or should this be aquired from the topic?\n\n// replace myTable with your db table name \nmsg.topic = \"INSERT INTO Sensor_data (Sensor_Val, Time, Sensor_Name) VALUES (?,?,?)\";\nmsg.payload = [sensorVal, timeStamp, sensorName];\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 631.2567977905273,
"y": 364.65273237228394,
"wires": [
[
"790fd8ab.8981d8",
"e75b426d.812a5"
]
]
},
{
"id": "a8e0b0e6.bfcfa",
"type": "debug",
"z": "5ed7faf3.7da194",
"name": "",
"active": false,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"x": 1146.267234802246,
"y": 389.9548201560974,
"wires": []
},
{
"id": "790fd8ab.8981d8",
"type": "mysql",
"z": "5ed7faf3.7da194",
"mydb": "bc8ab442.0892a8",
"name": "",
"x": 879.7636947631836,
"y": 362.9931254386902,
"wires": [
[
"a8e0b0e6.bfcfa"
]
]
},
{
"id": "e75b426d.812a5",
"type": "debug",
"z": "5ed7faf3.7da194",
"name": "complete msg",
"active": false,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "true",
"targetType": "full",
"x": 892.0589828491211,
"y": 330.3402934074402,
"wires": []
},
{
"id": "df8d3209.6dff4",
"type": "mqtt in",
"z": "5ed7faf3.7da194",
"name": "esp8266Soil/SoilMoisture",
"topic": "qiot/things/admin/esp8266Soil/SoilMoisture1/Soil",
"qos": "0",
"datatype": "auto",
"broker": "dfce0de9.dd384",
"x": 115,
"y": 534.0104370117188,
"wires": [
[
"2f2c34ea.34757c"
]
]
},
{
"id": "52295e66.9a571",
"type": "comment",
"z": "5ed7faf3.7da194",
"name": "SENSOR Soil Moisture",
"info": "",
"x": 105,
"y": 471.0104064941406,
"wires": []
},
{
"id": "2f2c34ea.34757c",
"type": "function",
"z": "5ed7faf3.7da194",
"name": "prepare data to sql",
"func": "var sensorVal= parseFloat(msg.payload);\n//var timeStamp = msg.timestamp;\nvar timeStamp = new Date();\nvar sensorName = \"ESP8266_Soil\"; // defined string. Or should this be aquired from the topic?\n\n// replace myTable with your db table name \nmsg.topic = \"INSERT INTO Soil_data (Soil_Val, Soil_Time, Soil_Sensor_Name) VALUES (?,?,?)\";\nmsg.payload = [sensorVal, timeStamp, sensorName];\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 444.0173645019531,
"y": 531.0104370117188,
"wires": [
[
"b30aa5af.4e73b8",
"90d818d1.58e228"
]
]
},
{
"id": "4ba61c22.c67074",
"type": "debug",
"z": "5ed7faf3.7da194",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"x": 959.0278015136719,
"y": 556.3125247955322,
"wires": []
},
{
"id": "b30aa5af.4e73b8",
"type": "mysql",
"z": "5ed7faf3.7da194",
"mydb": "bc8ab442.0892a8",
"name": "",
"x": 697.524284362793,
"y": 592.3508701324463,
"wires": [
[
"4ba61c22.c67074"
]
]
},
{
"id": "90d818d1.58e228",
"type": "debug",
"z": "5ed7faf3.7da194",
"name": "complete msg",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "true",
"targetType": "full",
"x": 704.8195495605469,
"y": 496.697998046875,
"wires": []
},
{
"id": "5ad2a546.30330c",
"type": "template",
"z": "5ed7faf3.7da194",
"name": "Format query 1",
"field": "topic",
"fieldType": "msg",
"format": "handlebars",
"syntax": "mustache",
"template": "SELECT Sensor_Val,Sensor_Name,Time FROM Sensor_data WHERE time > {{payload}}",
"output": "str",
"x": 419.98268127441406,
"y": 1306.6771240234375,
"wires": [
[
"437443b9.463e5c"
]
]
},
{
"id": "c5e27001.6ffb6",
"type": "ui_chart",
"z": "5ed7faf3.7da194",
"name": "24 hours unowifi",
"group": "92af9b5c.356eb8",
"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"
],
"useOldStyle": false,
"outputs": 1,
"x": 1149.9826736450195,
"y": 1240.6771259307861,
"wires": [
[]
]
},
{
"id": "437443b9.463e5c",
"type": "mysql",
"z": "5ed7faf3.7da194",
"mydb": "bc8ab442.0892a8",
"name": "MYSQL",
"x": 581.9826431274414,
"y": 1303.6771221160889,
"wires": [
[
"637ffcf6.860844",
"a96f7bc5.c35978"
]
]
},
{
"id": "5aff77b9.770f38",
"type": "function",
"z": "5ed7faf3.7da194",
"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": 231.98265075683594,
"y": 1272.677128791809,
"wires": [
[
"5ad2a546.30330c"
]
]
},
{
"id": "637ffcf6.860844",
"type": "debug",
"z": "5ed7faf3.7da194",
"name": "",
"active": true,
"console": "false",
"complete": "false",
"x": 1008.9825973510742,
"y": 1333.6771221160889,
"wires": []
},
{
"id": "37f2ce1d.acc042",
"type": "inject",
"z": "5ed7faf3.7da194",
"name": "Timestamp",
"topic": "",
"payload": "",
"payloadType": "date",
"repeat": "",
"crontab": "",
"once": false,
"x": 113.98265838623047,
"y": 1218.6771297454834,
"wires": [
[
"5aff77b9.770f38"
]
]
},
{
"id": "7c2bf451.d1adec",
"type": "comment",
"z": "5ed7faf3.7da194",
"name": "Flow to query database and format for chart",
"info": "",
"x": 223.98265838623047,
"y": 1175.6771430969238,
"wires": []
},
{
"id": "a96f7bc5.c35978",
"type": "change",
"z": "5ed7faf3.7da194",
"name": "Format data",
"rules": [
{
"t": "set",
"p": "payload",
"pt": "msg",
"to": "(\t $series := [\t { \"field\": \"Sensor_Val\", \"label\": \"Sensor_Name\" }\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": 888.9826240539551,
"y": 1213.6771268844604,
"wires": [
[
"c5e27001.6ffb6"
]
]
},
{
"id": "222645ef.a31b9a",
"type": "comment",
"z": "5ed7faf3.7da194",
"name": "MYSQL example to chart data",
"info": "https://flows.nodered.org/flow/13c55d1aa11e864609e24fa534a1fa26",
"x": 125,
"y": 1091.677131652832,
"wires": []
},
{
"id": "79f88f2d.0eb26",
"type": "mqtt-broker",
"z": "",
"name": "",
"broker": "375002bfab8d.sn.mynetname.net",
"port": "1883",
"clientid": "",
"usetls": false,
"compatmode": true,
"keepalive": "60",
"cleansession": true,
"birthTopic": "",
"birthQos": "0",
"birthPayload": "",
"closeTopic": "",
"closeQos": "0",
"closePayload": "",
"willTopic": "",
"willQos": "0",
"willPayload": ""
},
{
"id": "bc8ab442.0892a8",
"type": "MySQLdatabase",
"z": "",
"host": "127.0.0.1",
"port": "3306",
"db": "node_red",
"tz": ""
},
{
"id": "dfce0de9.dd384",
"type": "mqtt-broker",
"z": "",
"name": "",
"broker": "375002bfab8d.sn.mynetname.net",
"port": "1883",
"clientid": "",
"usetls": false,
"compatmode": true,
"keepalive": "60",
"cleansession": true,
"birthTopic": "",
"birthQos": "0",
"birthPayload": "",
"closeTopic": "",
"closeQos": "0",
"closePayload": "",
"willTopic": "",
"willQos": "0",
"willPayload": ""
},
{
"id": "92af9b5c.356eb8",
"type": "ui_group",
"z": "",
"name": "Flow3 test",
"tab": "8b674cb8.c3c87",
"order": 1,
"disp": true,
"width": "6",
"collapse": false
},
{
"id": "8b674cb8.c3c87",
"type": "ui_tab",
"z": "",
"name": "Test Mysql",
"icon": "dashboard",
"order": 6,
"disabled": false,
"hidden": false
}
]
Thanks once more in advance for all your valuable help on that!
I managed to output the array per 24 H with the following corrected flow
[
{
"id": "69e54f3.f0a15b",
"type": "tab",
"label": "Flow 2",
"disabled": false,
"info": ""
},
{
"id": "c407e2ba.034a",
"type": "template",
"z": "69e54f3.f0a15b",
"name": "Format query ESP8266Soil 24H",
"field": "topic",
"fieldType": "msg",
"format": "html",
"syntax": "mustache",
"template": "SELECT Sensor_Val, Sensor_Name, Time FROM Sensor_data WHERE Time > DATE_SUB(NOW(),INTERVAL 24 HOUR) AND Sensor_Name = \"ESP8266Soil\" AND Sensor_Val!= \"NULL\";",
"output": "str",
"x": 312.0173645019531,
"y": 224.0104217529297,
"wires": [
[
"959130de.226b3"
]
]
},
{
"id": "3f4d51fc.69c03e",
"type": "ui_chart",
"z": "69e54f3.f0a15b",
"name": "24 hours unowifi",
"group": "92af9b5c.356eb8",
"order": 2,
"width": "0",
"height": "0",
"label": "Chart",
"chartType": "line",
"legend": "false",
"xformat": "auto",
"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"
],
"useOldStyle": false,
"outputs": 1,
"x": 1124.0173416137695,
"y": 148.01019954681396,
"wires": [
[]
]
},
{
"id": "959130de.226b3",
"type": "mysql",
"z": "69e54f3.f0a15b",
"mydb": "bc8ab442.0892a8",
"name": "MYSQL",
"x": 558.0174255371094,
"y": 284.01028060913086,
"wires": [
[
"41a931d7.a8624",
"5d2c4fba.dba51"
]
]
},
{
"id": "41a931d7.a8624",
"type": "debug",
"z": "69e54f3.f0a15b",
"name": "",
"active": true,
"console": "false",
"complete": "false",
"x": 790.0173187255859,
"y": 314.01028633117676,
"wires": []
},
{
"id": "5d2c4fba.dba51",
"type": "change",
"z": "69e54f3.f0a15b",
"name": "Format data",
"rules": [
{
"t": "set",
"p": "payload",
"pt": "msg",
"to": "(\t $series := [\t { \"field\": \"Sensor_Val\", \"label\": \"Sensor_Name\" }\t ];\t $xaxis := \"Time\";\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": 875.0172576904297,
"y": 194.01039028167725,
"wires": [
[
"3f4d51fc.69c03e"
]
]
},
{
"id": "3dc75ec1.1fb982",
"type": "inject",
"z": "69e54f3.f0a15b",
"name": "Timestamp",
"topic": "",
"payload": "",
"payloadType": "date",
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": "",
"x": 96.05203247070312,
"y": 291.1212615966797,
"wires": [
[
"c407e2ba.034a"
]
]
},
{
"id": "92af9b5c.356eb8",
"type": "ui_group",
"z": "",
"name": "Flow3 test",
"tab": "8b674cb8.c3c87",
"order": 1,
"disp": true,
"width": "6",
"collapse": false
},
{
"id": "bc8ab442.0892a8",
"type": "MySQLdatabase",
"z": "",
"host": "127.0.0.1",
"port": "3306",
"db": "node_red",
"tz": ""
},
{
"id": "8b674cb8.c3c87",
"type": "ui_tab",
"z": "",
"name": "Test Mysql",
"icon": "dashboard",
"order": 6,
"disabled": false,
"hidden": false
}
]
My only problem now is how to format data from the output of Msql to be able to show them on the plot.
After many tests I managed to get the graphs with the following:
[
{
"id": "769a1ef7.36915",
"type": "tab",
"label": "Mysql Sensor Graphs",
"disabled": false,
"info": ""
},
{
"id": "e9cceac8.681ac8",
"type": "template",
"z": "769a1ef7.36915",
"name": "Format query ESP8266Soil 30 Days",
"field": "topic",
"fieldType": "msg",
"format": "html",
"syntax": "mustache",
"template": "SELECT Sensor_Val,Sensor_SoilVal, Sensor_Name, Time FROM Sensor_data WHERE Time > DATE_SUB(NOW(),INTERVAL 30 DAY) AND Sensor_Name = \"ESP8266Soil\" AND Sensor_Val!= \"NULL\";",
"output": "str",
"x": 348.96539306640625,
"y": 161.0104217529297,
"wires": [
[
"10e9c605.a5df3a"
]
]
},
{
"id": "10e9c605.a5df3a",
"type": "mysql",
"z": "769a1ef7.36915",
"mydb": "bc8ab442.0892a8",
"name": "MYSQL",
"x": 594.9653701782227,
"y": 160.01033973693848,
"wires": [
[
"540fa95b.01e258",
"95b46ba3.0e98c8"
]
]
},
{
"id": "540fa95b.01e258",
"type": "debug",
"z": "769a1ef7.36915",
"name": "",
"active": false,
"console": "false",
"complete": "false",
"x": 770.9653129577637,
"y": 227.01043319702148,
"wires": []
},
{
"id": "95b46ba3.0e98c8",
"type": "change",
"z": "769a1ef7.36915",
"name": "Format data",
"rules": [
{
"t": "set",
"p": "payload",
"pt": "msg",
"to": "(\t $series := [\t { \"field\": \"Sensor_Val\", \"label\": \"ESP8266Soil Temp\" }\t ,{\"field\": \"Sensor_SoilVal\", \"label\": \"ESP8266Soil\" }\t ];\t $xaxis := \"Time\";\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": 818.9652938842773,
"y": 150.01043605804443,
"wires": [
[
"686eaa86.87dac4",
"248c63e8.5c17dc",
"f5d3d372.6ca5"
]
]
},
{
"id": "bf22f0fd.7383",
"type": "inject",
"z": "769a1ef7.36915",
"name": "Timestamp",
"topic": "",
"payload": "",
"payloadType": "date",
"repeat": "60",
"crontab": "",
"once": false,
"onceDelay": "",
"x": 104.00001525878906,
"y": 160.1212673187256,
"wires": [
[
"e9cceac8.681ac8"
]
]
},
{
"id": "686eaa86.87dac4",
"type": "ui_chart",
"z": "769a1ef7.36915",
"name": "24 hours ESP8266Soil temp",
"group": "92af9b5c.356eb8",
"order": 1,
"width": "0",
"height": "0",
"label": "24 hours ESP8266Soil Temp",
"chartType": "line",
"legend": "false",
"xformat": "dd 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"
],
"useOldStyle": true,
"outputs": 1,
"x": 1104.9654159545898,
"y": 127.01036548614502,
"wires": [
[]
]
},
{
"id": "248c63e8.5c17dc",
"type": "debug",
"z": "769a1ef7.36915",
"name": "",
"active": false,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"x": 1066.17728805542,
"y": 228.8161334991455,
"wires": []
},
{
"id": "f5d3d372.6ca5",
"type": "ui_chart",
"z": "769a1ef7.36915",
"name": "1 Week ESP8266Soil temp",
"group": "92af9b5c.356eb8",
"order": 2,
"width": "0",
"height": "0",
"label": "1 Week ESP8266Soil Temp",
"chartType": "line",
"legend": "false",
"xformat": "dd HH:mm",
"interpolate": "linear",
"nodata": "",
"dot": false,
"ymin": "",
"ymax": "",
"removeOlder": "1",
"removeOlderPoints": "",
"removeOlderUnit": "604800",
"cutout": 0,
"useOneColor": false,
"colors": [
"#00e68c",
"#2d2da8",
"#ff7f0e",
"#2ca02c",
"#98df8a",
"#d62728",
"#ff9896",
"#9467bd",
"#c5b0d5"
],
"useOldStyle": true,
"outputs": 1,
"x": 1110.0174255371094,
"y": 179.01057624816895,
"wires": [
[]
]
},
{
"id": "2f020dfd.8ca9e2",
"type": "comment",
"z": "769a1ef7.36915",
"name": "ESP 8266Soil",
"info": "",
"x": 83,
"y": 76.89933204650879,
"wires": []
},
{
"id": "822d60b2.77795",
"type": "template",
"z": "769a1ef7.36915",
"name": "Format query ESP8266Soil 30 Days",
"field": "topic",
"fieldType": "msg",
"format": "html",
"syntax": "mustache",
"template": "SELECT Sensor_Val,Sensor_SoilVal, Sensor_Name, Time FROM Sensor_data WHERE Time > DATE_SUB(NOW(),INTERVAL 30 DAY) AND Sensor_Name = \"ESP8266Soil\" AND Sensor_SoilVal!= \"NULL\";",
"output": "str",
"x": 350.9653625488281,
"y": 336.89926528930664,
"wires": [
[
"4273afdf.76b49"
]
]
},
{
"id": "4273afdf.76b49",
"type": "mysql",
"z": "769a1ef7.36915",
"mydb": "bc8ab442.0892a8",
"name": "MYSQL",
"x": 583.9653701782227,
"y": 336.8992099761963,
"wires": [
[
"38e309f2.426a36",
"253052c0.1c634e"
]
]
},
{
"id": "38e309f2.426a36",
"type": "debug",
"z": "769a1ef7.36915",
"name": "",
"active": false,
"console": "false",
"complete": "false",
"x": 768.9652976989746,
"y": 405.899356842041,
"wires": []
},
{
"id": "253052c0.1c634e",
"type": "change",
"z": "769a1ef7.36915",
"name": "Format data",
"rules": [
{
"t": "set",
"p": "payload",
"pt": "msg",
"to": "(\t $series := [\t { \"field\": \"Sensor_Val\", \"label\": \"ESP8266Soil Temp\" }\t ,{\"field\": \"Sensor_SoilVal\", \"label\": \"ESP8266Soil\" }\t ];\t $xaxis := \"Time\";\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": 816.9652786254883,
"y": 328.89935970306396,
"wires": [
[
"2366410f.1bcede",
"219c1d08.461032",
"b3724d63.5cade"
]
]
},
{
"id": "8149db39.b3d348",
"type": "inject",
"z": "769a1ef7.36915",
"name": "Timestamp",
"topic": "",
"payload": "",
"payloadType": "date",
"repeat": "60",
"crontab": "",
"once": false,
"onceDelay": "",
"x": 107,
"y": 337.01026725769043,
"wires": [
[
"822d60b2.77795"
]
]
},
{
"id": "2366410f.1bcede",
"type": "ui_chart",
"z": "769a1ef7.36915",
"name": "24 hours ESP8266Soil ",
"group": "92af9b5c.356eb8",
"order": 3,
"width": "0",
"height": "0",
"label": "24 hours ESP8266Soil",
"chartType": "line",
"legend": "false",
"xformat": "dd 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"
],
"useOldStyle": true,
"outputs": 1,
"x": 1082.9654006958008,
"y": 305.89928913116455,
"wires": [
[]
]
},
{
"id": "219c1d08.461032",
"type": "debug",
"z": "769a1ef7.36915",
"name": "",
"active": false,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"x": 1064.1772727966309,
"y": 407.70505714416504,
"wires": []
},
{
"id": "b3724d63.5cade",
"type": "ui_chart",
"z": "769a1ef7.36915",
"name": "1 Week ESP8266Soil",
"group": "92af9b5c.356eb8",
"order": 4,
"width": "0",
"height": "0",
"label": "1 Week ESP8266Soil ",
"chartType": "line",
"legend": "false",
"xformat": "dd HH:mm",
"interpolate": "linear",
"nodata": "",
"dot": false,
"ymin": "",
"ymax": "",
"removeOlder": "1",
"removeOlderPoints": "",
"removeOlderUnit": "604800",
"cutout": 0,
"useOneColor": false,
"colors": [
"#00e68c",
"#2d2da8",
"#ff7f0e",
"#2ca02c",
"#98df8a",
"#d62728",
"#ff9896",
"#9467bd",
"#c5b0d5"
],
"useOldStyle": true,
"outputs": 1,
"x": 1088.0174102783203,
"y": 357.8994998931885,
"wires": [
[]
]
},
{
"id": "37d4994b.3d2c46",
"type": "template",
"z": "769a1ef7.36915",
"name": "Format query ESP8266Soil 30 Days",
"field": "topic",
"fieldType": "msg",
"format": "html",
"syntax": "mustache",
"template": "SELECT Sensor_Val,Sensor_SoilVal, Sensor_Name, Time FROM Sensor_data WHERE Time > DATE_SUB(NOW(),INTERVAL 30 DAY) AND Sensor_Name = \"UnoWifiDS18B20\" AND Sensor_Val!= \"NULL\";",
"output": "str",
"x": 343.96539306640625,
"y": 650.1215867996216,
"wires": [
[
"e5e208c2.a35b58"
]
]
},
{
"id": "e5e208c2.a35b58",
"type": "mysql",
"z": "769a1ef7.36915",
"mydb": "bc8ab442.0892a8",
"name": "MYSQL",
"x": 589.9653701782227,
"y": 649.1215047836304,
"wires": [
[
"343f0dcc.dfabb2",
"b62d5cd0.2a34e"
]
]
},
{
"id": "343f0dcc.dfabb2",
"type": "debug",
"z": "769a1ef7.36915",
"name": "",
"active": true,
"console": "false",
"complete": "false",
"x": 765.9653129577637,
"y": 716.1215982437134,
"wires": []
},
{
"id": "b62d5cd0.2a34e",
"type": "change",
"z": "769a1ef7.36915",
"name": "Format data",
"rules": [
{
"t": "set",
"p": "payload",
"pt": "msg",
"to": "(\t $series := [\t { \"field\": \"Sensor_Val\", \"label\": \"UnoWifiDS18B20 Temp\" }\t ,{\"field\": \"Sensor_SoilVal\", \"label\": \"UnoWifiDS18B20 SOIL\" }\t ];\t $xaxis := \"Time\";\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": 813.9652938842773,
"y": 639.1216011047363,
"wires": [
[
"849e4548.50da18",
"fbef03dc.5605f",
"538dd85a.a5b4a8"
]
]
},
{
"id": "9159b627.05f248",
"type": "inject",
"z": "769a1ef7.36915",
"name": "Timestamp",
"topic": "",
"payload": "",
"payloadType": "date",
"repeat": "60",
"crontab": "",
"once": false,
"onceDelay": "",
"x": 99.00001525878906,
"y": 649.2324323654175,
"wires": [
[
"37d4994b.3d2c46"
]
]
},
{
"id": "849e4548.50da18",
"type": "ui_chart",
"z": "769a1ef7.36915",
"name": "24 hours Unowifi temp",
"group": "df397ce5.6fa8a",
"order": 6,
"width": "0",
"height": "0",
"label": "24 hours Unowifi Temp",
"chartType": "line",
"legend": "false",
"xformat": "dd 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"
],
"useOldStyle": true,
"outputs": 1,
"x": 1079.9654159545898,
"y": 616.1215305328369,
"wires": [
[]
]
},
{
"id": "fbef03dc.5605f",
"type": "debug",
"z": "769a1ef7.36915",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"x": 1064.1773147583008,
"y": 729.9273929595947,
"wires": []
},
{
"id": "538dd85a.a5b4a8",
"type": "ui_chart",
"z": "769a1ef7.36915",
"name": "1 Week Unowifi temp",
"group": "df397ce5.6fa8a",
"order": 5,
"width": "0",
"height": "0",
"label": "1 Week Unowifi Temp",
"chartType": "line",
"legend": "false",
"xformat": "dd HH:mm",
"interpolate": "linear",
"nodata": "",
"dot": false,
"ymin": "",
"ymax": "",
"removeOlder": "1",
"removeOlderPoints": "",
"removeOlderUnit": "604800",
"cutout": 0,
"useOneColor": false,
"colors": [
"#00e68c",
"#2d2da8",
"#ff7f0e",
"#2ca02c",
"#98df8a",
"#d62728",
"#ff9896",
"#9467bd",
"#c5b0d5"
],
"useOldStyle": true,
"outputs": 1,
"x": 1080.0173721313477,
"y": 659.1217546463013,
"wires": [
[]
]
},
{
"id": "61800794.639058",
"type": "comment",
"z": "769a1ef7.36915",
"name": "UnoWifi",
"info": "",
"x": 58,
"y": 566.0104970932007,
"wires": []
},
{
"id": "bc8ab442.0892a8",
"type": "MySQLdatabase",
"z": "",
"host": "127.0.0.1",
"port": "3306",
"db": "node_red",
"tz": ""
},
{
"id": "92af9b5c.356eb8",
"type": "ui_group",
"z": "",
"name": "MySQL Sensor Graph",
"tab": "8b674cb8.c3c87",
"order": 1,
"disp": true,
"width": "6",
"collapse": false
},
{
"id": "df397ce5.6fa8a",
"type": "ui_group",
"z": "",
"name": "UnoWifi",
"tab": "8b674cb8.c3c87",
"order": 2,
"disp": true,
"width": "6",
"collapse": false
},
{
"id": "8b674cb8.c3c87",
"type": "ui_tab",
"z": "",
"name": "MySQL Sensor Graph",
"icon": "dashboard",
"order": 6,
"disabled": false,
"hidden": false
}
]
The problem was that needed to tick Use deprecated (pre 2.5.0) data format. on the chart node.
I also excluded on the Graph the temp values for the soil graph and the soil Values on the temp graph because range was very different and wasn't readable.
Hope my solution on this problem can help others.
Thank you once more Solano8 without your comments and assistance I wouldn't get through this!
I am glad you sorted this out!
MySQL have done a nice job for me for all sorts of logging the last two years. It was not an active choice, but I already had a MySql running when I started playing with IoT, so it was easy to get started.
But now I have taken the advice from @Colin some posts above to check out InfluxDB (and Grafana).
Very powerful tools, and might well be the right choice for larger data series. And my guess is that you will find using sensors for logging so fun that you soon end up with an arsenal that could benefit from the functionality and power of InfluxDB and Grafana... :-).
If I find a good way to run InfluxDB and Grafana locally, (maybe containers on a NAS ?), I think I will consider change my setup. And I encourage you to take a look at this too, "Unless you have a good reason to use MySQL", as @Colin said.
Wish you a good Node Red journey!
I run them (and node red) in a Pi.
Everything has been said thoroughly by @solano8 and @Colin. My two bits based on my usage are:
For telemetry type data, InfluxDB becomes advantageous when moving on to processing the data later
You can let the INSERT command generate the timestamp column during the operation unless you have a specific reason to set the value before the command (e.g. GPS data)
Also, perhaps you are using the term MySQL generically here but migration to MariaDB is truly effortless.
Kind regards.
I'm using collectd (+mqtt plugin) + collectd graph panel + rrdtool + rrdcached
/etc/collectd/collectd.conf
Hostname "raspberrypi"
FQDNLookup false
BaseDir "/var/lib/collectd"
PluginDir "/usr/lib/collectd"
CollectInternalStats false
LoadPlugin syslog
<Plugin syslog>
LogLevel info
</Plugin>
LoadPlugin mqtt
LoadPlugin rrdcached
<Plugin mqtt>
<Subscribe "nodered">
Host "127.0.0.1"
Port 1883
ClientId "collectd"
QoS 2
CleanSession true
</Subscribe>
</Plugin>
<Plugin rrdcached>
DaemonAddress "unix:/var/run/rrdcached.sock"
DataDir "/var/lib/rrdcached/db/collectd"
CreateFiles true
CreateFilesAsync true
CollectStatistics true
</Plugin>
node-red
node.send(
{
payload: 'N:' + bat,
topic: 'collectd/raspberrypi/' + msg.payload[i].name + '/percent'
});
This is how it looks in Graph Panel
Copyright OpenJS Foundation and Node-RED contributors. All rights reserved. The OpenJS Foundation has registered trademarks and uses trademarks. For a list of trademarks of the OpenJS Foundation, please see our Trademark Policy and Trademark List. Trademarks and logos not indicated on the list of OpenJS Foundation trademarks are trademarks™ or registered® trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.
The OpenJS Foundation | Terms of Use | Privacy Policy | OpenJS Foundation Bylaws | Trademark Policy | Trademark List | Cookie Policy
