# Create Chart from mySQL Data

**URL:** <https://discourse.nodered.org/t/create-chart-from-mysql-data/3296>\
**Category:** General\
**Created:** [20 September 2018 08:16 UTC](https://discourse.nodered.org/t/create-chart-from-mysql-data/3296 "2018-09-20T08:16:09Z")\
**Posts on this page:** 17\
**Page:** 2

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [18 June 2020 08:45 UTC](https://discourse.nodered.org/t/create-chart-from-mysql-data/3296/22 "2020-06-18T08:45:45Z")

</div>

the live data should just be the number so

```auto
msg.topic = "temperature";
msg.payload = Number(msg.payload);
return msg;

```

---

<div class="post-metadata">

**Author:** ![gatuso](https://avatars.discourse-cdn.com/v4/letter/g/b5ac83/32.png) [@gatuso](https://discourse.nodered.org/u/gatuso)\
**Post date:** [18 June 2020 08:55 UTC](https://discourse.nodered.org/t/create-chart-from-mysql-data/3296/23 "2020-06-18T08:55:59Z")

</div>

The problem is that the live data starts a new line, instead of 'picking up' the loaded data:

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/3/43c34fee4cd5fc5139ff74247ec969e612d38ec2.png)

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [18 June 2020 09:01 UTC](https://discourse.nodered.org/t/create-chart-from-mysql-data/3296/24 "2020-06-18T09:01:45Z")

</div>

something must be slightly different then - and are you sure not other data point is sent as payload ?  
When sending the setup you just need to send the array - no need for topic there.

---

<div class="post-metadata">

**Author:** ![gatuso](https://avatars.discourse-cdn.com/v4/letter/g/b5ac83/32.png) [@gatuso](https://discourse.nodered.org/u/gatuso)\
**Post date:** [18 June 2020 09:22 UTC](https://discourse.nodered.org/t/create-chart-from-mysql-data/3296/25 "2020-06-18T09:22:39Z")

</div>

Do you have any example that I could follow or test? To compare with mine.  
Thanks

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [18 June 2020 09:38 UTC](https://discourse.nodered.org/t/create-chart-from-mysql-data/3296/26 "2020-06-18T09:38:09Z")

</div>

[https://flows.nodered.org/flow/92920b4fd450bf92a3035e061947daa0](https://flows.nodered.org/flow/92920b4fd450bf92a3035e061947daa0)

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [18 June 2020 10:07 UTC](https://discourse.nodered.org/t/create-chart-from-mysql-data/3296/27 "2020-06-18T10:07:09Z")

</div>

Can you post a debug showing the message you send to populate the chart please? Make sure all the objects are expanded.

---

<div class="post-metadata">

**Author:** ![gatuso](https://avatars.discourse-cdn.com/v4/letter/g/b5ac83/32.png) [@gatuso](https://discourse.nodered.org/u/gatuso)\
**Post date:** [18 June 2020 10:23 UTC](https://discourse.nodered.org/t/create-chart-from-mysql-data/3296/28 "2020-06-18T10:23:29Z")

</div>

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/0/f0c5cc7ccf3d364fef9c86e7754ae180b1c9b04d.png)

```auto
function newValue(){
 msg.topic = "esp32/temperature";
 msg.payload = Number(msg.payload);
 return msg;
}

function valuesToArr(){
 var series =["esp32/temperature"];
 var labels = ["esp32/temperature"];
 var data = [];

 var i, len, string;

 for (i = 0, len = msg.payload.length, string = ""; i < len; i++) {
  data.push({"x":Date.parse(msg.payload[i].created_at), "y":Number(msg.payload[i].temp)});
 }

 data = [data];

 msg.payload = [{series, data, labels}];
 msg.topic = "esp32/temperature";

 return msg;
}

```

Output (when restart the nodered server):  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/d/cde9bd52dd37b7130406ac318c65d274f87862cc.png)

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [18 June 2020 10:36 UTC](https://discourse.nodered.org/t/create-chart-from-mysql-data/3296/29 "2020-06-18T10:36:37Z")

</div>

As an experiment try putting a 10 second delay node in series with the live data (I know that is not a valid final solution, just an experiement). I suspect what may be happening is that the first live value is getting added in before the historical data has been added, though not certain.  
If that doesn't fix it then please do as I previously asked, which is to show us the debug output of the historical data actually being sent to the chart.

---

<div class="post-metadata">

**Author:** ![gatuso](https://avatars.discourse-cdn.com/v4/letter/g/b5ac83/32.png) [@gatuso](https://discourse.nodered.org/u/gatuso)\
**Post date:** [18 June 2020 10:43 UTC](https://discourse.nodered.org/t/create-chart-from-mysql-data/3296/30 "2020-06-18T10:43:23Z")

</div>

Delay does not work.  
Also historic data is load on startup (0.1sec). Live data is coming each 5sec.  
The debug from historical data.  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/c/cc6bc710e8c1d633550af553d4fb2d1e747327f3.png)

Looking at the chart it seems that the first Live data point is 'connected' to the first point of historical data. But it should be connected to last point.

---

<div class="post-metadata">

**Author:** ![gatuso](https://avatars.discourse-cdn.com/v4/letter/g/b5ac83/32.png) [@gatuso](https://discourse.nodered.org/u/gatuso)\
**Post date:** [18 June 2020 10:56 UTC](https://discourse.nodered.org/t/create-chart-from-mysql-data/3296/31 "2020-06-18T10:56:52Z")

</div>

Ok I found the problem.  
In the query it was "ORDER BY created\_at DESC".  
Even the plotting of the chart was correct, the first live data point was connecting with the actual date last point, that in this case was the first point.  
Interesting that the plotting is done correctly but the connection between points gets messed up.

Seems to be a common ChartJS problem/topic.

Thanks for the support 😄

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [18 June 2020 11:08 UTC](https://discourse.nodered.org/t/create-chart-from-mysql-data/3296/32 "2020-06-18T11:08:10Z")

</div>

> [@gatuso](#):
>
> In the query it was "ORDER BY created\_at DESC".

That was one of the reasons why I asked for a fully expanded debug output, in order to check the ordering of the timestamps. The first live sample draws a line from the last received data point to the new value. Since the last received data was from the earliest timestamp that is where it draws the line from. The behaviour you are seeing is as expected.

---

<div class="post-metadata">

**Author:** ![chuckf201](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/chuckf201/32/91382_2.png) [@chuckf201](https://discourse.nodered.org/u/chuckf201)\
**Post date:** [20 June 2020 18:13 UTC](https://discourse.nodered.org/t/create-chart-from-mysql-data/3296/33 "2020-06-20T18:13:07Z")

</div>

If you're working with a large database you earliest date may be out of the range you want shown. Reverse the data using a decremented loop to create an array to use in msg.payload.newLoop

---

<div class="post-metadata">

**Author:** ![rpr](https://avatars.discourse-cdn.com/v4/letter/r/b5ac83/32.png) [@rpr](https://discourse.nodered.org/u/rpr)\
**Post date:** [15 December 2020 09:50 UTC](https://discourse.nodered.org/t/create-chart-from-mysql-data/3296/34 "2020-12-15T09:50:43Z")

</div>

Can you kindly suggest any guid to import data from mysql to Node-Red.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [15 December 2020 11:13 UTC](https://discourse.nodered.org/t/create-chart-from-mysql-data/3296/35 "2020-12-15T11:13:41Z")

</div>

@rpr - Welcome to the forum. Based on your question, I would suggest you spen an hour and watch the [Node-RED Essentials](https://www.youtube.com/watch?v=ksGeUD26Mw0&list=PLyNBB9VCLmo1hyO-4fIZ08gqFcXBkHy-6) videos to get a feel for node-red.

You will have to create a flow to do this. You will need to use the mysql node (node-red-node-mysql) and send it a mySQL query to retreive the data and then decide what to do wth it.

---

<div class="post-metadata">

**Author:** ![rpr](https://avatars.discourse-cdn.com/v4/letter/r/b5ac83/32.png) [@rpr](https://discourse.nodered.org/u/rpr)\
**Post date:** [16 December 2020 05:31 UTC](https://discourse.nodered.org/t/create-chart-from-mysql-data/3296/36 "2020-12-16T05:31:15Z")

</div>

Thank you Sir, Till now I have used node red to send data to kudal and now I am trying to retrieve it... Thanks for your help

---

<div class="post-metadata">

**Author:** ![rpr](https://avatars.discourse-cdn.com/v4/letter/r/b5ac83/32.png) [@rpr](https://discourse.nodered.org/u/rpr)\
**Post date:** [28 December 2020 07:40 UTC](https://discourse.nodered.org/t/create-chart-from-mysql-data/3296/37 "2020-12-28T07:40:48Z")

</div>

Thank you Sir, I did what you said and it help me to retrieve data.  
Unfortunately later i found i didn't need but still it will be useful in future so Thank you.

---

<div class="post-metadata">

**Author:** ![khoat0](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/khoat0/32/46207_2.png) [@khoat0](https://discourse.nodered.org/u/khoat0)\
**Post date:** [9 August 2021 19:07 UTC](https://discourse.nodered.org/t/create-chart-from-mysql-data/3296/38 "2021-08-09T19:07:57Z")

</div>

Your code is a great suggestion, before that, I couldn't figure out what to do to extract the data from the sql data string sent back and then how to format it to give the chart.

Thank you!

[Previous page](https://discourse.nodered.org/t/create-chart-from-mysql-data/3296.md?page=1)