Data from MySQL db to Graph

Dashboard
1

Sorry verrrry new here! Not even sure if this is the correct spot put this post but I couldn't find a "graph" topic. Anyways, I feel accomplished in the last few days I have managed to setup my Arduino to post temp and humidity with MQTT to Node Red and setup a MySQL data base and store the data there and retrieve that data as an array. My array looks like this

array[1]
0: object
atime: "2020-11-17T19:14:28.000Z"
temp: 21.1
humidity: 35.7

Now I am struggling with putting this back into a graph.

I read somewhere on here to use a change node with some code to change it. Am I on the right track?

2

Ok from added research it look like I should send the time stamp as a javascript time stamp instead of a string. I will work with that and post updated payload.

1 Like
3

If you are charting using Dashboard then the Dashboard category is the right one.

4

So if I use

var myDataArray = msg.payload.split(',');
temp1 = myDataArray[0];
humidity1 = myDataArray[1];
date1 = new Date();

msg.payload = [date1,temp1,humidity1];

msg.topic = "INSERT INTO bmetemp (timeNRchar,temp,humidity) VALUES (?,?,?)";

return msg;

to insert the date into my db it it still returns it as a string

array[1]
0: object
atime: "2020-11-18T09:37:23.000Z"
timeNRchar: "2020-11-18 12:37:23.846"
temp: 20.6
humidity: 37.2

Is this workable to put into a chart?

5

Or this?

"[{"timeNRchar":"2020-11-18 13:54:33.492","temp":21.2,"humidity":39.3}]"
6

Hmm not getting anywhere.. this is how I am pulling it from my db.

msg.topic = 'SELECT timeNRchar, temp, humidity FROM bmetemp ORDER BY p_id DESC LIMIT 1'

return msg;

is this the problem?

7

Are you getting any rows back from database?

Are you transforming the data retrieved into the necessary format for the Graph?

8

Are you getting the data needed for the chart? Once you get that then you need to format it correctly.

9

Also @Colin

The format is what I struggling with. This is what I get from the debug node.

array[1]
0: object
timeNRchar: "2020-11-18 18:16:23.957"
temp: 21.6
humidity: 34.2

How do I convert it to this format?

10

You can't make much of a chart with one one data point.

11

I can easy extract more data. I just need some guidance on parsing it to the right format.

array[10]
0: object
timeNRchar: "2020-11-18 18:25:48.906"
temp: 21.6
humidity: 34.2
1: object
timeNRchar: "2020-11-18 18:16:23.957"
temp: 21.6
humidity: 34.2
2: object
timeNRchar: "2020-11-18 18:14:06.429"
temp: 21.6
humidity: 34.2
3: object
timeNRchar: "2020-11-18 18:13:50.943"
temp: 21.6
humidity: 34.2

etc.

12

Hi - firstly, when you copy from the debug output - please use the "copy value" button that appears under your mouse as it provides the helper (me) with data in a useable format.

Right now, i dont have time to finish it but I knocked this up & this should get you closer to a solution....

[{"id":"d3d38637.1f7408","type":"inject","z":"a9fbaedc.8f9c1","name":"Fake DB data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"timeNRchar\":\"2020-11-18 18:25:48.906\",\"temp\":21.6,\"humidity\":34.2},{\"timeNRchar\":\"2020-11-18 18:16:23.957\",\"temp\":21.6,\"humidity\":34.2},{\"timeNRchar\":\"2020-11-18 18:14:06.429\",\"temp\":21.6,\"humidity\":34.2},{\"timeNRchar\":\"2020-11-18 18:13:50.943\",\"temp\":21.6,\"humidity\":34.2}]","payloadType":"json","x":140,"y":620,"wires":[["4221874.591d878"]]},{"id":"4221874.591d878","type":"function","z":"a9fbaedc.8f9c1","name":"BD Data to Chart data","func":"/* DB Data\n[{\n    \"timeNRchar\": \"2020-11-18 18:25:48.906\",\n    \"temp\": 21.6,\n    \"humidity\": 34.2\n},{\n    \"timeNRchar\": \"2020-11-18 18:16:23.957\",\n    \"temp\": 21.6,\n    \"humidity\": 34.2\n},{    \n    \"timeNRchar\": \"2020-11-18 18:14:06.429\",\n    \"temp\": 21.6,\n    \"humidity\": 34.2\n},{    \n    \"timeNRchar\": \"2020-11-18 18:13:50.943\",\n    \"temp\": 21.6,\n    \"humidity\": 34.2\n}]\n*/\n/* Desired format\n[{\n\"series\": [\"timeNRchar\", \"temp\", \"humidity\"],\n\"data\": [\n    [{ \"x\": 1504029632890, \"y\": 5 },\n     { \"x\": 1504029636001, \"y\": 4 },\n    ],\n    [{ \"x\": 1504029633514, \"y\": 6 },\n     { \"x\": 1504029636622, \"y\": 7 },\n    ],\n    [{ \"x\": 1504029634400, \"y\": 7 },\n     { \"x\": 1504029637959, \"y\": 7 },\n    ]\n],\n\"labels\": [\"\"]\n}]\n*/\n\n//loop each row and build an array in the required format\nvar data = msg.payload.map(e => {\n    return  [\n        { \"x\": e.timeNRchar, \"y\": e.temp },\n        { \"x\": e.timeNRchar, \"y\": e.humidity }\n    ]\n})\n\nmsg.payload = [{\n\"series\": [\"timeNRchar\", \"temp\", \"humidity\"],\n\"data\":data,\n\"labels\": [\"\"]\n}]\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":400,"y":620,"wires":[["78b2ba94.731454"]]},{"id":"78b2ba94.731454","type":"debug","z":"a9fbaedc.8f9c1","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":630,"y":620,"wires":[]}]

If you dont have it fixed in an hour or 2, let me know & I (or someone else) will surely pop in and help you along.

13

Thanks @Steve-Mcl for your assistance!
Like you said I'm getting a little closer... When I import with this I get results on the chart. See pic.

image
image876Ă—484 20.9 KB

Notice the hover, I can only get temp data to show in the hover the top of each line though is obviously the humidity. Is the a graph setting or is it in the parsing?

14

Here is the debug.

[{"series":["timeNRchar","temp","humidity"],"data":[[{"x":"2020-11-18 21:48:49.082","y":21},{"x":"2020-11-18 21:48:49.082","y":39.7}],[{"x":"2020-11-18 21:48:43.026","y":21},{"x":"2020-11-18 21:48:43.026","y":39.7}]],"labels":["date","temp","humid"]}]

Is this the format that is the best to work with?

15

OK, I'm back.

Its the parsing, like i said I only had 5 minutes to knock that demo up (but no time to test it)
So another 5 mins & its now pushing the data into the chart data in the correct format.

Try this instead...

[{"id":"d3d38637.1f7408","type":"inject","z":"a9fbaedc.8f9c1","name":"Fake DB data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"timeNRchar\":\"2020-11-18 18:25:48.906\",\"temp\":21.3,\"humidity\":31.2},{\"timeNRchar\":\"2020-11-18 18:16:23.957\",\"temp\":21.6,\"humidity\":32.2},{\"timeNRchar\":\"2020-11-18 18:14:06.429\",\"temp\":21.7,\"humidity\":37.2},{\"timeNRchar\":\"2020-11-18 18:13:50.943\",\"temp\":21.6,\"humidity\":44.2}]","payloadType":"json","x":140,"y":620,"wires":[["4221874.591d878"]]},{"id":"4221874.591d878","type":"function","z":"a9fbaedc.8f9c1","name":"BD Data to Chart data","func":"/* DB Data\n[{\n    \"timeNRchar\": \"2020-11-18 18:25:48.906\",\n    \"temp\": 21.6,\n    \"humidity\": 34.2\n},{\n    \"timeNRchar\": \"2020-11-18 18:16:23.957\",\n    \"temp\": 21.6,\n    \"humidity\": 34.2\n},{    \n    \"timeNRchar\": \"2020-11-18 18:14:06.429\",\n    \"temp\": 21.6,\n    \"humidity\": 34.2\n},{    \n    \"timeNRchar\": \"2020-11-18 18:13:50.943\",\n    \"temp\": 21.6,\n    \"humidity\": 34.2\n}]\n*/\n/* Desired format\n[{\n\"series\": [\"temp\", \"humidity\"],\n\"data\": [\n    [{ \"x\": 1504029632890, \"y\": 5 }, //series 1 data\n     { \"x\": 1504029636001, \"y\": 4 },\n    ],\n    [{ \"x\": 1504029633514, \"y\": 6 }, //series 2 data\n     { \"x\": 1504029636622, \"y\": 7 },\n    ],\n],\n\"labels\": [\"\"]\n}]\n*/\n\nvar series1 = [];\nvar series2 = [];\n\n//loop each row and build an array in the required format\nfor (let index = 0; index < msg.payload.length; index++) {\n    const e = msg.payload[index];\n    var t = new Date(e.timeNRchar).valueOf()\n    series1.push({ \"x\": t, \"y\": e.temp });\n    series2.push({ \"x\": t, \"y\": e.humidity });\n}\n\n\nmsg.payload = [\n    {\n        \"series\": [\"temp\", \"humidity\"],\n        \"data\":   [series1,series2],\n        \"labels\": [\"\"]\n    }\n];\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":400,"y":620,"wires":[["78b2ba94.731454","175fff9a.2987f"]]},{"id":"78b2ba94.731454","type":"debug","z":"a9fbaedc.8f9c1","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":630,"y":620,"wires":[]},{"id":"175fff9a.2987f","type":"ui_chart","z":"a9fbaedc.8f9c1","name":"","group":"dce9e7a2.d20c78","order":4,"width":0,"height":0,"label":"chart","chartType":"line","legend":"true","xformat":"HH:mm:ss","interpolate":"bezier","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"outputs":1,"x":590,"y":680,"wires":[[]]},{"id":"dce9e7a2.d20c78","type":"ui_group","name":"Object detection","tab":"5132060d.4cde48","order":1,"disp":true,"width":"7","collapse":false},{"id":"5132060d.4cde48","type":"ui_tab","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]

word of caution...

Limit your SQL to return only as many rows to no more that your graph is wide in pixels otherwise you will unnecessarily slow the dashboard down.

Let me try that again...

If you decide to SELECT 5000 rows of data from the DB - but your graph is 1000 pixels wide - you will NOT be able to see 4000 of the rows and your dashboard will begin to crawl - there, i think that makes more sense :slight_smile:

2 Likes
16

Wow many thanks!!

17

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.