# Charts in node-red with data from mysql database

**URL:** <https://discourse.nodered.org/t/charts-in-node-red-with-data-from-mysql-database/27539>\
**Category:** Dashboard\
**Created:** [30 May 2020 20:34 UTC](https://discourse.nodered.org/t/charts-in-node-red-with-data-from-mysql-database/27539 "2020-05-30T20:34:59Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![node96](https://avatars.discourse-cdn.com/v4/letter/n/e480ec/32.png) [@node96](https://discourse.nodered.org/u/node96)\
**Post date:** [30 May 2020 20:35 UTC](https://discourse.nodered.org/t/charts-in-node-red-with-data-from-mysql-database/27539/1 "2020-05-30T20:35:00Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/c/1c8160bfef9ea0b72c0e72b53545c11812906e04.jpeg)

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/e/feff1d76b1477dfd25cb2f62734c3a21c1661172.png)

---

<div class="post-metadata">

**Author:** ![cinhcet](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/cinhcet/32/438_2.png) [@cinhcet](https://discourse.nodered.org/u/cinhcet)\
**Post date:** [30 May 2020 22:27 UTC](https://discourse.nodered.org/t/charts-in-node-red-with-data-from-mysql-database/27539/2 "2020-05-30T22:27:11Z")

</div>

```auto
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?

---

<div class="post-metadata">

**Author:** ![node96](https://avatars.discourse-cdn.com/v4/letter/n/e480ec/32.png) [@node96](https://discourse.nodered.org/u/node96)\
**Post date:** [30 May 2020 22:29 UTC](https://discourse.nodered.org/t/charts-in-node-red-with-data-from-mysql-database/27539/3 "2020-05-30T22:29:31Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/7/d7300f6006c20b321dfbf5a2f02f2989787e66f7.png)

the data node as this code:

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

```

---

<div class="post-metadata">

**Author:** ![cinhcet](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/cinhcet/32/438_2.png) [@cinhcet](https://discourse.nodered.org/u/cinhcet)\
**Post date:** [30 May 2020 22:30 UTC](https://discourse.nodered.org/t/charts-in-node-red-with-data-from-mysql-database/27539/4 "2020-05-30T22:30:59Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![cinhcet](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/cinhcet/32/438_2.png) [@cinhcet](https://discourse.nodered.org/u/cinhcet)\
**Post date:** [30 May 2020 22:35 UTC](https://discourse.nodered.org/t/charts-in-node-red-with-data-from-mysql-database/27539/5 "2020-05-30T22:35:43Z")

</div>

I am guessing now  
try this in your function node

```auto
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;

```

---

<div class="post-metadata">

**Author:** ![node96](https://avatars.discourse-cdn.com/v4/letter/n/e480ec/32.png) [@node96](https://discourse.nodered.org/u/node96)\
**Post date:** [30 May 2020 22:38 UTC](https://discourse.nodered.org/t/charts-in-node-red-with-data-from-mysql-database/27539/6 "2020-05-30T22:38:51Z")

</div>

It should be the data that comes from json node.

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/5/35f7ebe11a8947ac7940f32f2f7702ac712c517a.png)

---

<div class="post-metadata">

**Author:** ![node96](https://avatars.discourse-cdn.com/v4/letter/n/e480ec/32.png) [@node96](https://discourse.nodered.org/u/node96)\
**Post date:** [30 May 2020 22:40 UTC](https://discourse.nodered.org/t/charts-in-node-red-with-data-from-mysql-database/27539/7 "2020-05-30T22:40:30Z")

</div>

> [@cinhcet](#):
>
> ```auto
> 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`

---

<div class="post-metadata">

**Author:** ![cinhcet](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/cinhcet/32/438_2.png) [@cinhcet](https://discourse.nodered.org/u/cinhcet)\
**Post date:** [30 May 2020 22:46 UTC](https://discourse.nodered.org/t/charts-in-node-red-with-data-from-mysql-database/27539/8 "2020-05-30T22:46:44Z")

</div>

then your msg.payload must look different.

Does that work for you?

```auto
[{"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?

---

<div class="post-metadata">

**Author:** ![node96](https://avatars.discourse-cdn.com/v4/letter/n/e480ec/32.png) [@node96](https://discourse.nodered.org/u/node96)\
**Post date:** [30 May 2020 22:51 UTC](https://discourse.nodered.org/t/charts-in-node-red-with-data-from-mysql-database/27539/9 "2020-05-30T22:51:00Z")

</div>

> [@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:

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

```

---

<div class="post-metadata">

**Author:** ![cinhcet](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/cinhcet/32/438_2.png) [@cinhcet](https://discourse.nodered.org/u/cinhcet)\
**Post date:** [30 May 2020 22:57 UTC](https://discourse.nodered.org/t/charts-in-node-red-with-data-from-mysql-database/27539/10 "2020-05-30T22:57:13Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![node96](https://avatars.discourse-cdn.com/v4/letter/n/e480ec/32.png) [@node96](https://discourse.nodered.org/u/node96)\
**Post date:** [30 May 2020 22:59 UTC](https://discourse.nodered.org/t/charts-in-node-red-with-data-from-mysql-database/27539/11 "2020-05-30T22:59:11Z")

</div>

sure, this is the output of json node:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/7/271a0119014c8603072eeb1c03a824d3cab8252a.png)

---

<div class="post-metadata">

**Author:** ![cinhcet](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/cinhcet/32/438_2.png) [@cinhcet](https://discourse.nodered.org/u/cinhcet)\
**Post date:** [30 May 2020 23:01 UTC](https://discourse.nodered.org/t/charts-in-node-red-with-data-from-mysql-database/27539/12 "2020-05-30T23:01:01Z")

</div>

ahh, now, finally 🙂

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

---

<div class="post-metadata">

**Author:** ![node96](https://avatars.discourse-cdn.com/v4/letter/n/e480ec/32.png) [@node96](https://discourse.nodered.org/u/node96)\
**Post date:** [30 May 2020 23:01 UTC](https://discourse.nodered.org/t/charts-in-node-red-with-data-from-mysql-database/27539/13 "2020-05-30T23:01:26Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![node96](https://avatars.discourse-cdn.com/v4/letter/n/e480ec/32.png) [@node96](https://discourse.nodered.org/u/node96)\
**Post date:** [30 May 2020 23:02 UTC](https://discourse.nodered.org/t/charts-in-node-red-with-data-from-mysql-database/27539/14 "2020-05-30T23:02:11Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![cinhcet](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/cinhcet/32/438_2.png) [@cinhcet](https://discourse.nodered.org/u/cinhcet)\
**Post date:** [30 May 2020 23:02 UTC](https://discourse.nodered.org/t/charts-in-node-red-with-data-from-mysql-database/27539/15 "2020-05-30T23:02:32Z")

</div>

you're welcome 🙂

---

<div class="post-metadata">

**Author:** ![node96](https://avatars.discourse-cdn.com/v4/letter/n/e480ec/32.png) [@node96](https://discourse.nodered.org/u/node96)\
**Post date:** [30 May 2020 23:04 UTC](https://discourse.nodered.org/t/charts-in-node-red-with-data-from-mysql-database/27539/16 "2020-05-30T23:04:35Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![cinhcet](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/cinhcet/32/438_2.png) [@cinhcet](https://discourse.nodered.org/u/cinhcet)\
**Post date:** [30 May 2020 23:31 UTC](https://discourse.nodered.org/t/charts-in-node-red-with-data-from-mysql-database/27539/17 "2020-05-30T23:31:35Z")

</div>

I think yes, just try 🙂

---

<div class="post-metadata">

**Author:** ![nodeschorsch](https://avatars.discourse-cdn.com/v4/letter/n/6bbea6/32.png) [@nodeschorsch](https://discourse.nodered.org/u/nodeschorsch)\
**Post date:** [4 February 2021 08:45 UTC](https://discourse.nodered.org/t/charts-in-node-red-with-data-from-mysql-database/27539/19 "2021-02-04T08:45:15Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![Vishal\_M](https://avatars.discourse-cdn.com/v4/letter/v/ba8739/32.png) [@Vishal\_M](https://discourse.nodered.org/u/Vishal_M)\
**Post date:** [10 March 2022 10:32 UTC](https://discourse.nodered.org/t/charts-in-node-red-with-data-from-mysql-database/27539/20 "2022-03-10T10:32:14Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [11 March 2022 05:15 UTC](https://discourse.nodered.org/t/charts-in-node-red-with-data-from-mysql-database/27539/21 "2022-03-11T05:15:21Z")

</div>

> <https://github.com/node-red/node-red-dashboard/blob/master/Charts.md#stored-data>

the above link may help.