Inserting database data into a chart

Dashboard
1

Hi

I'm trying to complete my weather station dashboard and have the need to insert historic data from mySQL database.

I'm able to connect to the database and to pull out the required data. I then convert it to JSON format. The problem is my chart stays empty!!

Could anyone please assist before my remaining hair is lost.
My flow is:

[{"id":"cbcd6bd7.5b7e38","type":"tab","label":"Flow 2","disabled":false,"info":""},{"id":"6653620d.75d75c","type":"inject","z":"cbcd6bd7.5b7e38","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":true,"onceDelay":0.1,"x":110,"y":140,"wires":[["8dcc9414.24e258"]]},{"id":"e246e357.08c1e","type":"mysql","z":"cbcd6bd7.5b7e38","mydb":"5e0f3b48.32ae84","name":"","x":420,"y":100,"wires":[["759b2a9d.32e384"]]},{"id":"8dcc9414.24e258","type":"function","z":"cbcd6bd7.5b7e38","name":"BETWEEN DATE QUERY","func":"msg.topic = \"SELECT CREATED,AMB_TEMP FROM WEATHER_MEASUREMENT WHERE CREATED BETWEEN '2019-04-01' AND '2019-06-30'\"\nreturn msg;","outputs":1,"noerr":0,"x":150,"y":240,"wires":[["e246e357.08c1e"]]},{"id":"b26cc359.9c772","type":"debug","z":"cbcd6bd7.5b7e38","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":490,"y":240,"wires":[]},{"id":"7a52b4b6.f84a5c","type":"ui_chart","z":"cbcd6bd7.5b7e38","name":"3 Months temperature","group":"a7449be3.fd8418","order":0,"width":"21","height":"6","label":"chart","chartType":"line","legend":"false","xformat":"HH:mm","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"13","removeOlderPoints":"14000","removeOlderUnit":"604800","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"outputs":1,"x":480,"y":380,"wires":[[]]},{"id":"759b2a9d.32e384","type":"change","z":"cbcd6bd7.5b7e38","name":"Format data","rules":[{"t":"set","p":"payload","pt":"msg","to":"(\t $series := [\t {\t \"field\": \"AMB_TEMP\",\t \"label\": \"Temperature\" \t }\t ];\t $xaxis := \"CREATED\";\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":310,"y":320,"wires":[["7a52b4b6.f84a5c","b26cc359.9c772"]]},{"id":"5e0f3b48.32ae84","type":"MySQLdatabase","z":"","name":"","host":"192.168.1.155","port":"3306","db":"weather_station","tz":""},{"id":"a7449be3.fd8418","type":"ui_group","z":"","name":"Default","tab":"b6c1bcde.cfe6d","order":1,"disp":false,"width":"21","collapse":false},{"id":"b6c1bcde.cfe6d","type":"ui_tab","z":"","name":"Home","icon":"dashboard","order":1,"disabled":false,"hidden":false}]

2

Feed what you are giving to the chart into a debug node and check whether the format of the data agrees with that in the docs (https://github.com/node-red/node-red-dashboard/blob/master/Charts.md). In particular check the nested arrays are correct. If you can't see the problem post it here.

3

Thanks Colin for picking this up. As you probably realise i'm a bit of newbie, recently retired and learning lots of new skills. Ive checked the data and it looks ok.

I have attached a screenshot and look forward to your further assistance.

Derek

image
image1366Ă—728 70.8 KB

4

I did suggest you check the nested arrays. Look in the linked docs you will see that data should be an array (one for each series) of arrays (one element for each point). You have it just as an array. Again if you look in the docs you will see that you have the timestamps as a string rather than a javascript milliseconds time. I don't know whether it works with a string or not. Finally I see you have over 13000 points. How many pixels is the chart across? There isn't much point having lots of values for each pixel on the screen. Normally one or two would be enough. That won't stop it working but it may be very slow, depending on how much processor power you have (particularly in the browser).

5

Thanks Colin. Sorry for not exactly following your advice earlier. I have studied the docs you indicated but am still really struggling.
I’ve managed to convert my time stamp to Java millisecond by changing the SQL select query. Ive also reduced the number of points to 142 instead of 13000! The bit I’m struggling with is converting the data to the required format. Do I need to create a function node and follow the document Line Charts example or use the change node with the JSONata expression editor as I did in my first attempt? Sorry to be a pain. Ive been on this project now for nearly 2 years and am desperate to complete it. I usually manage to find me way around the problems but this one has got me stumped.

I think the main thing I am missing is an understanding of how the data will fill the whole chart and not just plot the first point.
Thanks for your help.
Derek

6

As far as I can see the only issue is that you have data as an array instead of an array of arrays. So instead of

data: [ {x: , y: }, {x: , y: }, ...]

you need

data: [ [ {x: , y: }, {x: , y: }, ...] ]

Looking at the Change node I can see that your knowledge of JSONata is much greater than mine so hopefully you know how to make that change.

7

Thanks Colin

My knowledge of JSONata and Java is very limited. My code is usually trial and error with a lot of luck!
Thanks to your last comment I was able to resolve my issue - sorted out my brackets!
I appreciate your help.
Many thanks, Derek

1 Like
8

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