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	829	28 July 2020
How could I display data from a mysql database within a chart? Dashboard node-red-dashboard	25	2290	19 July 2022
MySQL Array or Arrays to line chart General	18	4778	18 February 2020
Need help with line chart Dashboard	30	7008	16 July 2019
Walkthrough for SQL queries to basic guages and multi series/line charting? Does it exist? General	7	492	2 October 2020

Charts in node-red with data from mysql database

Related Topics