Create Chart from mySQL Data

General
1

Hello Pro's,

need to create a chart with the following data from mySQL table:

db name: sensors
table name: temperature

| 1_OWD | 2493 | 2018-09-20 09:17:32 |
| 1_OWD | 2493 | 2018-09-20 09:19:02 |
| 1_OWD | 2500 | 2018-09-20 09:20:32 |
| 1_OWD | 2500 | 2018-09-20 09:22:02 |
| 1_OWD | 2500 | 2018-09-20 09:23:32 |
| 1_OWD | 2500 | 2018-09-20 09:25:02 |
| 1_OWD | 2506 | 2018-09-20 09:26:32 |
| 1_OWD | 2506 | 2018-09-20 09:28:02 |
| 1_OWD | 2506 | 2018-09-20 09:29:32 |
| 1_OWD | 2506 | 2018-09-20 09:31:02 |
| 1_OWD | 2506 | 2018-09-20 09:32:32 |
| 1_OWD | 2512 | 2018-09-20 09:34:02 |
| 1_OWD | 2512 | 2018-09-20 09:35:32 |

This is Sensor name | Temp | timestamp.

Please help me to put the data array into the right chart json format. I don't know how to put every meassured data into the right place.

ps: sorry for my poor english!

2

If the data is in a mySQL table you first need to get the data into Node-RED

You will need to use one of the SQL nodes, if you google "SQL node-red" you should find a video and other guides to show you how to do this. Add a debug to the end of your flow so you can see the results you get.

3

The mySQL connection ist ready and working. I have following data in nodered:

select * from temperature where sensor = '1_OWD'; : msg.payload : array[896]

array[896]

[0 … 9]

0: object

sensor: "1_OWD"

value: "2493"

timestamp: "2018-09-19T09:10:36.000Z"

1: object

sensor: "1_OWD"

value: "2493"

timestamp: "2018-09-19T09:12:06.000Z"

but this is not the right format for a graph.

4

Question is, what kind of chart you want to use. The structure of data is not same for all of them. Do you going to use ui_chart from dashboard or some external stuff?

5

I will use ui_chart.

6

Next step is to choose the chart type. Line chart, Bar chart ...

7

:slight_smile: Line chart please

8

And now it is time for a bit of reading the documents :slight_smile:
https://github.com/node-red/node-red-dashboard/blob/master/Charts.md#stored-data

1 Like
9

I did it. Thanks for the Link and not for a quick JS help which i needed.

10

So I hope you already tried something and you have something to show, flow or function where you try to rearrange your data into proper format to be feed to chart? If so, please share. Then we see where you stack and we can give you relevant help.

11

(just a gentle reminder that this forum is not a free coding resource, and that all contributors do so of their own free will and time. The general approach we prefer is one of education, where rather than provide canned answers, we help identify possible solutions and approaches based on the user's existing efforts, such that the whole community can help, contribute, and learn from others.)

8 Likes
12

Given the data you have provided you have two possible approaches. 1) rearrange the whole array into a chart format as per that doc. or 2) split the array and then resend each datapoint to the chart as if it was replayed - again as per that doc.
The second approach is possibly easier to start with - a split node will spit the array into individual objects - then a change node could move the various properties to match the required format for a line chart - then a function node with some code to convert the timestamp into epoch format.

13

I've noticed in the past that the chartjs library is smart enough to recognize and parse dates that come from database queries (or at least when they are in ISO-8601 format). So, I would try to send the data without converting the date/time strings first...

1 Like
14

Hello again, here is my function:

//var data = [[{"x":1537348236000,"y":2493},{"x":1537348326000,"y":2493}]];
//
var series =["Temperatur in °C", "x"];
var labels = ["Labels"];
var data = [];

var i, len, string;

for (i = 1, len = msg.payload.length, string = ""; i < len; i++) {
data.push({"x":Date.parse(msg.payload[i].timestamp) + 2*60*60, "y":Number(msg.payload[i].value)/100});
}

data = [data];

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

And here is the mySQL Query:

SELECT * FROM temperature ORDER BY timestamp DESC LIMIT 1440

Feel free to ask (or tell me what i could have done better).

You are right, will share my solutions in future.

16

Hi !
I have the same problem as you, did you find the solution to this one ?
If so, can you share your solution?

Thank you !

GK.

17

Signed up just to say thank you for sharing this. Really useful, coming from languages other than JS.
Needed .length, .push and data = [data];
With those, anything is possible. Great question, good answer (in the end).
If I have time I'll see if I can make a more generic solution.

18

Great topic - solved my sql-to-chart problem! Kudo's to dceejay.
Thanks

19

How to mix historical data from MySql with live that from MQTT feeding into the chart?

Use case: Rpi, restarted. Chart data is lost. Get data from DB and fill up the chart, but keep using it with new live values.

20

Just do it. As long as the topic is the same then you can just add more data via msg.payload in real time.

21

On startup chart is feed with:

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

Live data is feed via:

var data = [{"x":Date.parse(Date()), "y":Number(msg.payload)}];
msg.topic = "temperature";
msg.payload = {data};
return msg;

Still chart does not update with live data.