Charts in node-red with data from mysql database

hello,

I am trying to build a chart with some data from mysql database.
I have a query to the database and it returns a object with temperatures that I want to send to a chart and make a line chart. So that I can see the change of temperature through the last 30 days.

This is the output of the query:
Capture

I am trying to do as the example in end of that github page you sent but no luck yet. In the x axis I wanted the months and in the y axis the temperature são that the line or bars would show the average temperature changes through the months. I found that guide not very well explained, could have at least a graphical example.

I receive the month and the respective average temperature. Ou is it possible to put it in a line chart or bar chart if it makes sense?

*********************************************** EDIT *********************************************

I've almost got it but I can't make the bars show up.
image

msg.payload = [{
    series: "",
    data: [[10, 20, 5, 30]],
    labels: [1, 2, 3, 4]
}];
return msg;

works fine for me.

Could you post your flow please?

BTW: You don't need to open a new topic, please use the old one.
Maybe someone could merge the two topics?

I know... my mistake I thought this one had been deleted.
My flow is as follows:

the data node as this code:

var m={
    "series":["X"],
    "data": msg.payload,
    "labels":["Jul","Aug","Sept", "Oct", "Nov"]
};
return {payload:[m]};

What is msg.payload?
And with flow I meant not an image, but the real flow

I am guessing now
try this in your function node

var data = [];
var month = [];
msg.payload.forEach(function(value) {
    data.push(value['avg(temp)']);
    month.push(value['the_month']);
});

msg.payload = [{
    series: "",
    data: [data],
    labels: month
}];
return msg;

It should be the data that comes from json node.

image

I ran the flow with this function and it returns an error:
TypeError: msg.payload.forEach is not a function

then your msg.payload must look different.

Does that work for you?

[{"id":"1f684225.9dbe3e","type":"inject","z":"a4a36ed.3108e9","name":"","topic":"","payload":"","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":270,"y":80,"wires":[["baf84eec.9a3c58"]]},{"id":"a3ab6a8d.94345","type":"function","z":"a4a36ed.3108e9","name":"","func":"var data = [];\nvar month = [];\nmsg.payload.forEach(function(value) {\n    data.push(value['avg(temp)']);\n    month.push(value['the_month']);\n});\n\nmsg.payload = [{\n    series: \"\",\n    data: [data],\n    labels: month\n}];\nreturn msg;\n","outputs":1,"noerr":0,"x":590,"y":80,"wires":[["be07eb33.1be9b"]]},{"id":"be07eb33.1be9b","type":"ui_chart","z":"a4a36ed.3108e9","name":"","group":"d45b5707.43bce8","order":7,"width":0,"height":0,"label":"chart","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","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":730,"y":80,"wires":[[]]},{"id":"baf84eec.9a3c58","type":"function","z":"a4a36ed.3108e9","name":"","func":"msg.payload = [{\"avg(temp)\": 30, \"the_month\": 1}, \n               {\"avg(temp)\": 35, \"the_month\": 2},\n               {\"avg(temp)\": 28, \"the_month\": 3}];\nreturn msg;","outputs":1,"noerr":0,"x":430,"y":80,"wires":[["a3ab6a8d.94345"]]},{"id":"d45b5707.43bce8","type":"ui_group","z":"","name":"Default","tab":"362d9368.12de9c","disp":true,"width":"6","collapse":false},{"id":"362d9368.12de9c","type":"ui_tab","z":"","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]

Could you please show your flow here?

Yes the one you sent works.

Here is the flow:

[{"id":"91fd570.30fa0a8","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"f4dda377.599e3","type":"function","z":"91fd570.30fa0a8","name":"TempInsideVariation30","func":"msg.topic = \"select avg(temp), the_month FROM (select month(noted_date) the_month, temp from temperature where out_in='In') tmp group by the_month\"\nreturn msg;","outputs":1,"noerr":0,"x":720,"y":300,"wires":[["ac8a7ca2.e45e4"]]},{"id":"ac8a7ca2.e45e4","type":"mysql","z":"91fd570.30fa0a8","mydb":"13acdda0.2cac92","name":"temperature","x":970,"y":300,"wires":[["1f2c008f.d8baaf"]]},{"id":"1f2c008f.d8baaf","type":"json","z":"91fd570.30fa0a8","name":"","property":"payload","action":"","pretty":false,"x":1160,"y":300,"wires":[["f02b1242.cba26"]]},{"id":"ce5a9cfc.2b176","type":"ui_chart","z":"91fd570.30fa0a8","name":"Monthly Avg Temp In","group":"4bb7a134.bcfe2","order":4,"width":12,"height":4,"label":"Inside Temperature Chart","chartType":"bar","legend":"false","xformat":"auto","interpolate":"linear","nodata":"","dot":false,"ymin":"0","ymax":"40","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":1560,"y":300,"wires":[["aa81c32a.36236"]]},{"id":"f02b1242.cba26","type":"function","z":"91fd570.30fa0a8","name":"data","func":"var data = [];\nvar month = [];\nmsg.payload.forEach(function(value) {\n    data.push(value['avg(temp)']);\n    month.push(value['the_month']);\n});\n\nmsg.payload = [{\n    series: \"\",\n    data: [data],\n    labels: month\n}];\nreturn msg;","outputs":1,"noerr":0,"x":1330,"y":300,"wires":[["ce5a9cfc.2b176"]]},{"id":"aa81c32a.36236","type":"debug","z":"91fd570.30fa0a8","name":"print","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":1750,"y":300,"wires":[]},{"id":"e748f57b.dcc9d8","type":"inject","z":"91fd570.30fa0a8","name":"query","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":true,"onceDelay":"30","x":490,"y":340,"wires":[["f4dda377.599e3"]]},{"id":"13acdda0.2cac92","type":"MySQLdatabase","z":"","name":"temperature","host":"35.234.152.47","port":"3306","db":"Temperature","tz":""},{"id":"4bb7a134.bcfe2","type":"ui_group","z":"","name":"Monthly Inside Statistics","tab":"54bfa295.6612fc","order":1,"disp":true,"width":12,"collapse":false},{"id":"54bfa295.6612fc","type":"ui_tab","z":"","name":"Monthly Data","icon":"dashboard","order":4,"disabled":false,"hidden":false}]

could you add a debug node after your json node, set it to show the full message and then post a screenshot of the output here?

I cannot really see why it should not work, especially since my example worked

sure, this is the output of json node:
image

ahh, now, finally :slight_smile:

your payload is a string, not a javascript object. Just remove the json node and connect the output directly

1 Like

I got it. I deleted the json node and connected the function directly to the database, and it is working!

yup, that was it!! Thank you so much!

you're welcome :slight_smile:

One last thing. If I wanted a line chart, would it be the same?

I think yes, just try :slight_smile:

1 Like

i just try to implement that for my chart idea. For one chart line, it works fine, but what if I have an extra column with the room in my table, e.g. temperatures for living room and kitchen? Is it possible to adjust the function, so that I can display two (or dynamically the number of rooms in the msg.payload) charts simultaneosly without doubling the queries / functions?

Thanks in Advance!

(i hope it is ok that I revive that topic)