Charts in node-red with data from mysql database

node96 · 30 May 2020 20:35

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.

cinhcet · 30 May 2020 22:27

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?

node96 · 30 May 2020 22:29

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]};

cinhcet · 30 May 2020 22:30

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

cinhcet · 30 May 2020 22:35

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;

node96 · 30 May 2020 22:38

It should be the data that comes from json node.

node96 · 30 May 2020 22:40

cinhcet:

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;

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

cinhcet · 30 May 2020 22:46

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?

node96 · 30 May 2020 22:51

cinhcet:

[{"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}]

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}]

cinhcet · 30 May 2020 22:57

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

node96 · 30 May 2020 22:59

sure, this is the output of json node:

cinhcet · 30 May 2020 23:01

ahh, now, finally

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

node96 · 30 May 2020 23:01

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

node96 · 30 May 2020 23:02

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

cinhcet · 30 May 2020 23:02

you're welcome

node96 · 30 May 2020 23:04

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

cinhcet · 30 May 2020 23:31

I think yes, just try

nodeschorsch · 4 February 2021 08:45

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)

Vishal_M · 10 March 2022 10:32

I am trying to create a chart which shows time along x-axis and data along y-axis for the past one hour's data from the SQL database

My SQL command is - "select A1_avg , Timing from Ok_Avg where Timing > now() - interval 1 hour"

It returns the data as follows

A1_Avg Timing
2 2022-03-10 15:58:06
3 2022-03-10 15:58:06
|2| 2022-03-10 15:13:27|
|48| 2022-03-10 15:13:49|
|12| 2022-03-10 15:13:52|
|19| 2022-03-10 15:13:55|
|47| 2022-03-10 15:13:58|
|0| 2022-03-10 15:14:02|
|35| 2022-03-10 15:18:11|
|26| 2022-03-10 15:18:14|
|6| 2022-03-10 15:18:1|

how do i plot these in a chart with time along x-axis and data along y-axis?

smanjunath211 · 11 March 2022 05:15

github.com

node-red/node-red-dashboard/blob/master/Charts.md#stored-data

# Node-RED-Dashboard Charts

## Live data

To display live data just wire up an input with a `msg.payload` that contains a number.


### Line charts

To display two or more lines on the same chart then each `msg` must also contain `topic`
property that identifies which data series it belongs to - for example:

    {topic:"temperature", payload:22}
    {topic:"humidity", payload:66}

Each series will be represented by a different colour.
Alternatively you can use the property `series` instead of `topic` if you prefer.

It is possible to create "gaps" in line charts by sending either a null or boolean false as the payload.

This file has been truncated. show original

the above link may help.

Topic		Replies	Views
Build charts with data from mysql database General	5	897	28 July 2020
How could I display data from a mysql database within a chart? Dashboard node-red-dashboard	25	2797	19 July 2022
MySQL Array or Arrays to line chart General	18	4927	18 February 2020
Doubt with data from Mysql A NodeRed and graph it General node-red-dashboard , database , chart , mysql	8	184	14 July 2024
Need help with line chart Dashboard	30	7625	16 July 2019

Charts in node-red with data from mysql database

Related topics