Dashboard - line graph - show datapoints from database

Dashboard
,
1

Hi there,

for several days I've now been trying, reading, trying again (repeat ...) to create a line graph on the dashboard, with data from a database.
But I just can't get it running, the way I'd like to have it.
So I finally figured I'd post my misery here :cry: :disappointed: ... maybe someone here can help me and is willing to do so :slightly_smiling_face:

Background:
I have several devices which send temperature (and some also humidity) via MQTT into my local network.
On a RaspberryPi the MQTT-Broker, Node-RED and MariaDB are running.
With NodeRED I save all values from the devices into the database (MariaDB).
-- so far everything works fine --
After that I want Node-RED to display these values in a line graph, each.
I want to display the saved values in 4 separate graphs:

  • gauge of the latest/current value
  • line graph of the values from the last hour
  • line graph of the values from the last day
  • line graph of the values from the last week

--> the gauge works fine
--> but I do have quite a struggle with the line graphs
The 3 line graphs do show a graph, but all 3 show exactly the same.
There is no difference between the HOUR and the WEEK graph :astonished:
grafik
--> Please also notice, that the time on the x-Axis is the wrong way round :see_no_evil: :face_with_monocle:

Here a picture how the line graphs are configured:
grafik
All three are identical, except that one is set to 1h, the other to 1 day and the third to 1 week

While searching the net I figured that I might have a bug in one of the function nodes which select the last 1000 values from the database and then "send" them to the dashboard.

Here an example how the SELECT (function node) looks like:

var newMsg = {
"topic" : "SELECT * FROM `temp_log`.`access` WHERE room = 'heatingroom' AND position = 'bufferTank' AND direction = 'span' ORDER BY no DESC LIMIT 1000;"
}
return newMsg;

after that, I want to modify the timestamp since it is in the format of: YYYY-MM-DDTHH:MM:SS:000Z
but I want to have it in the format YYYY-MM-DD HH:MM:SS:000

to do this I am using another function node with looks like this:


for(var i = 0; i < msg.payload.length; i++){
    var date = new Date(msg.payload[i].time);
    date = (date.getFullYear() + '-' + ('00' + (date.getMonth()+1)).slice(-2) + '-' + ('00' + date.getDate()).slice(-2) + ' ' + ('00' + date.getHours()).slice(-2) + ':' + ('00' + date.getMinutes()).slice(-2) + ':' + ('00' + date.getSeconds()).slice(-2));
    msg.payload[i].time = date;
}
return msg;

from there I modify the payload for the gauge like this:

msg.payload = msg.payload[0].temperature;
return msg;

--> works perfectly fine

for the line graphs I use following function node:

var data = [];
var time = [];
msg.payload.forEach(function(value) {
    data.push(value['span']);
    time.push(value['time']);
});

msg.payload = [{
    series: "",
    data: [data],
    labels: time
}];

return msg;

hmmm... :frowning:

What am I doing wrong?
Why is the x-axis on the line graph the wrong way round? (latest time is left :scream:)
Why do all three graphs show an identical line? (I had hoped, that the setting with 1h, 1d, 1w would then do the job for me --- obviously not :frowning: )

Many thanks in advance for any constructive support.

RMB

2

The chart node only accepts a specific input format:

You can also insert extra data points by specifying the timestamp property. This must be in either epoch time (in miliseconds, not seconds), or ISO8601 format.

3

When you have corrected the timestamp, if it still does not work then add a debug node showing what is going into the chart. This is the technique to use whenever a node is not doing what it should, look at what you are sending in to it. If it looks ok then adjust the query so that it just returns a few points and post the debug output here. Expand the debug output enough that we can see what is there.

A couple of points, I see that in the query you have sorted the data by no descending. I don't know what no is but you should sort the data by time ascending.
Also, when you give the chart a set of data it will display all of that data, if you just want to show a portion of the data in the database then you should select just that data from the database.
The formatting of the timestamp is done by the browser, using the PC locale settings.

4

Hi bekman2,

I thought I did this with following function node:

var data = [];
var time = [];
msg.payload.forEach(function(value) {
    data.push(value['span']);
    time.push(value['time']);
});

msg.payload = [{
    series: "",
    data: [data],
    labels: time
}];

return msg;

What's wrong here?

Kind regards

5

We cannot tell because we don't see the payload output here, only the function that creates some payload.

6

(one thing is that series property should also be an array)

7

Hi Colin,

the no refers to the row in the database the data is saved in
therefor if I order no or time I would have the same result.

Here's a debug node before modifying the timestamp:

grafik
grafik1672×698 198 KB

This is how the data looks after modifying the timestamp:

grafik
grafik1672×698 200 KB

to be continued in next post ...

8

...

This is how the data looks like, before going into the line chart:

grafik
grafik1672×261 77.1 KB

grafik
grafik310×963 43 KB

Does that help you to figure out, where the problem is?

Kind regards
RMB

9

This is the payload output going into the line chart node:

grafik
grafik310×963 44.8 KB

Kind regards
RMB

10

As I said, you are sorting descending so you get the last record first.

11

If I order by ASC then I would always have the first 1000 datapoints from the database ...
Am I wrong?
Or how would the select statement then look like instead?

Kind regards
RMB

12

Usually this would be done by using a WHERE clause to select just the time range you want. Alternatively I don't know whether you can add another ORDER BY on the end. If not then you can sort it in node red. Selecting just the time range you want would usually be better though.

13

Note this comment as pointed out in the documentation:

To display a complete chart in one go - for example from a set of points retrieved from a database, the data must be supplied in the form of an array, that holds an object that has series ,labels , and data arrays.

14

you would also set the db return to output the span and time in correct names for chart, and order by time. some thing like this
"SELECT span AS y, time As x FROM 'temp_log'.'access' WHERE room = 'heatingroom' AND position = 'bufferTank' AND direction = 'span' ORDER BY time LIMIT 1000;"

you can then feed that directly to a change or function or template node to format the chart array. The output of each chart can then feed the next chart, you can then limit each chart to week, day, hour.
e.g.

[{"id":"f5142b09.9b2248","type":"inject","z":"bf9e1e33.030598","name":"dat from db in correct format","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"x\":1662769858618,\"y\":5.41},{\"x\":1662770158623,\"y\":3.91},{\"x\":1662770458619,\"y\":0.91},{\"x\":1662770758624,\"y\":6.25},{\"x\":1662771058619,\"y\":2.62},{\"x\":1662771358624,\"y\":7.34},{\"x\":1662771658624,\"y\":7.61},{\"x\":1662771958620,\"y\":1.56},{\"x\":1662772258625,\"y\":7.83},{\"x\":1662772558626,\"y\":3.63},{\"x\":1662772858626,\"y\":3.49},{\"x\":1662773158626,\"y\":9.51},{\"x\":1662774658629,\"y\":3.56},{\"x\":1662774958630,\"y\":2.01},{\"x\":1662775258630,\"y\":9.12},{\"x\":1662775558631,\"y\":9.7},{\"x\":1662775858630,\"y\":7.89},{\"x\":1662776158631,\"y\":1.71},{\"x\":1662776458631,\"y\":7.91},{\"x\":1662776758632,\"y\":8.34},{\"x\":1662777058633,\"y\":7.83},{\"x\":1662777358634,\"y\":0.5},{\"x\":1662777658635,\"y\":8.98},{\"x\":1662777958637,\"y\":9.25},{\"x\":1662778258637,\"y\":3.94},{\"x\":1662778558639,\"y\":7.58},{\"x\":1662778858639,\"y\":4.59},{\"x\":1662779158640,\"y\":1.73},{\"x\":1662779458644,\"y\":5.95},{\"x\":1662779758645,\"y\":0.16},{\"x\":1662780058645,\"y\":5.65},{\"x\":1662780358645,\"y\":6.63},{\"x\":1662780658646,\"y\":0.77},{\"x\":1662780958647,\"y\":3.25},{\"x\":1662781258647,\"y\":0.16},{\"x\":1662781558648,\"y\":2.54},{\"x\":1662781858649,\"y\":6.07},{\"x\":1662782158650,\"y\":2.64},{\"x\":1662782458651,\"y\":0.07},{\"x\":1662782758652,\"y\":5.37},{\"x\":1662783058653,\"y\":8.98},{\"x\":1662783358654,\"y\":6.82},{\"x\":1662783658653,\"y\":8.27},{\"x\":1662783958654,\"y\":6.45},{\"x\":1662784258654,\"y\":6.87},{\"x\":1662784558655,\"y\":2.05},{\"x\":1662784858656,\"y\":6.49},{\"x\":1662785158656,\"y\":0.04},{\"x\":1662785458656,\"y\":1.71},{\"x\":1662785758656,\"y\":1.11},{\"x\":1662786058656,\"y\":6.54},{\"x\":1662786358657,\"y\":6.45},{\"x\":1662786658657,\"y\":6.79},{\"x\":1662786958658,\"y\":6.01},{\"x\":1662787258665,\"y\":4.66},{\"x\":1662787558663,\"y\":9.85},{\"x\":1662787858665,\"y\":4.75},{\"x\":1662788158664,\"y\":3.1},{\"x\":1662788458665,\"y\":2.36},{\"x\":1662788758665,\"y\":4.95},{\"x\":1662789058665,\"y\":4.54},{\"x\":1662789358666,\"y\":7.72},{\"x\":1662789658667,\"y\":2.24},{\"x\":1662789958668,\"y\":0},{\"x\":1662790258669,\"y\":1.2},{\"x\":1662790558670,\"y\":2.17},{\"x\":1662790858670,\"y\":2.97},{\"x\":1662791158671,\"y\":0.19},{\"x\":1662791458672,\"y\":4.66},{\"x\":1662791758673,\"y\":5.62},{\"x\":1662792058674,\"y\":8.16},{\"x\":1662792358674,\"y\":3.02},{\"x\":1662792658675,\"y\":3.68},{\"x\":1662792958675,\"y\":9.35},{\"x\":1662793258676,\"y\":0.5},{\"x\":1662793558677,\"y\":9.64},{\"x\":1662793858677,\"y\":0.34},{\"x\":1662794158678,\"y\":2.51},{\"x\":1662794458677,\"y\":0.71},{\"x\":1662794758678,\"y\":3.31},{\"x\":1662795058678,\"y\":7.86},{\"x\":1662795358678,\"y\":9.89},{\"x\":1662795658678,\"y\":6.04},{\"x\":1662795958679,\"y\":5.12},{\"x\":1662796258678,\"y\":2.84},{\"x\":1662796558686,\"y\":3.59},{\"x\":1662796858684,\"y\":5.74},{\"x\":1662797158685,\"y\":8.08},{\"x\":1662797458686,\"y\":9.92},{\"x\":1662797758687,\"y\":8.22},{\"x\":1662798058687,\"y\":3.84},{\"x\":1662798358688,\"y\":1.35},{\"x\":1662798658690,\"y\":2.87},{\"x\":1662798958689,\"y\":8.83},{\"x\":1662799258688,\"y\":6.5},{\"x\":1662799558689,\"y\":2.53},{\"x\":1662799858689,\"y\":2.89},{\"x\":1662800158690,\"y\":8.7}]","payloadType":"json","x":240,"y":3680,"wires":[["541ba449.a0034c"]]},{"id":"541ba449.a0034c","type":"change","z":"bf9e1e33.030598","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"[\t   {\t       \"series\":[\"temp\"],\t       \"data\":[ [$$.payload]],\t       \"labels\":[\"temp\"]\t   }\t]","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":520,"y":3680,"wires":[["efd2c82a.d9935"]]},{"id":"efd2c82a.d9935","type":"ui_chart","z":"bf9e1e33.030598","name":"","group":"2d4fe667.28f8ba","order":13,"width":0,"height":0,"label":"week","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"4","removeOlderPoints":"1000","removeOlderUnit":"86400","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":300,"y":3760,"wires":[["2acea977.40afce","445cb618.031938"]]},{"id":"2acea977.40afce","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":630,"y":3760,"wires":[]},{"id":"445cb618.031938","type":"ui_chart","z":"bf9e1e33.030598","name":"","group":"2d4fe667.28f8ba","order":14,"width":0,"height":0,"label":"day","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"60","removeOlderUnit":"86400","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":380,"y":3800,"wires":[["bd4a6478.817348","2acea977.40afce"]]},{"id":"bd4a6478.817348","type":"ui_chart","z":"bf9e1e33.030598","name":"","group":"2d4fe667.28f8ba","order":15,"width":0,"height":0,"label":"hour","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"12","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":480,"y":3840,"wires":[["2acea977.40afce"]]},{"id":"2d4fe667.28f8ba","type":"ui_group","name":"demo","tab":"1caa8458.b17814","order":1,"disp":true,"width":"12","collapse":false},{"id":"1caa8458.b17814","type":"ui_tab","name":"Demo","icon":"dashboard","order":1,"disabled":false,"hidden":false}]
15

You select the last 1000 records from the table like this:

SELECT * FROM temp_log.access WHERE room = 'heatingroom' AND position = 'bufferTank' AND direction = 'span' ORDER BY no DESC LIMIT 1000;

And you can put them in ascending order something like this

SELECT * FROM (SELECT * FROM temp_log.access a WHERE a.room = 'heatingroom' AND a,position = 'bufferTank' AND a.direction = 'span' ORDER BY a.no DESC LIMIT 1000) b ORDER BY b.no ASC;

Sorry the forum mangled the back quotes

16

Hi E1cid,

THANKS!!
This really helped me a lot and got me back on track! :wink:

And @ all other supporters --> MANY thanks to you too, for sharing your thoughts and ideas on how to solve my problem!

Kind regards
RMB

17

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.