# Dashboard - line graph - show datapoints from database

**URL:** <https://discourse.nodered.org/t/dashboard-line-graph-show-datapoints-from-database/67537>\
**Category:** Dashboard\
**Tags:** node-red-dashboard, database\
**Created:** [9 September 2022 22:25 UTC](https://discourse.nodered.org/t/dashboard-line-graph-show-datapoints-from-database/67537 "2022-09-09T22:25:36Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![RMB](https://avatars.discourse-cdn.com/v4/letter/r/b19c9b/32.png) [@RMB](https://discourse.nodered.org/u/RMB)\
**Post date:** [9 September 2022 22:25 UTC](https://discourse.nodered.org/t/dashboard-line-graph-show-datapoints-from-database/67537/1 "2022-09-09T22:25:36Z")

</div>

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 😢 😞 ... maybe someone here can help me and is willing to do so 🙂

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 😲  
 ![grafik](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/e/de2b4cfddc6772a0ccb7d04d65f10ddaa88c2acb.png)  
--\> Please also notice, that the time on the x-Axis is the wrong way round 🙈 🧐

Here a picture how the line graphs are configured:  
 ![grafik](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/8/b84faa79f3b2903524367fbc3239627dbf6e8d0f.png)  
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:

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

```auto

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:

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

```

--\> works perfectly fine

for the line graphs I use following function node:

```auto
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... ☹

What am I doing wrong?  
Why is the x-axis on the line graph the wrong way round? (latest time is left 😱)  
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 ☹ )

Many thanks in advance for any constructive support.

RMB

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [10 September 2022 05:59 UTC](https://discourse.nodered.org/t/dashboard-line-graph-show-datapoints-from-database/67537/2 "2022-09-10T05:59:40Z")

</div>

The chart node only accepts a specific [input format](https://github.com/node-red/node-red-dashboard/blob/master/Charts.md):

> 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.

---

<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:** [10 September 2022 07:10 UTC](https://discourse.nodered.org/t/dashboard-line-graph-show-datapoints-from-database/67537/3 "2022-09-10T07:10:26Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![RMB](https://avatars.discourse-cdn.com/v4/letter/r/b19c9b/32.png) [@RMB](https://discourse.nodered.org/u/RMB)\
**Post date:** [10 September 2022 07:59 UTC](https://discourse.nodered.org/t/dashboard-line-graph-show-datapoints-from-database/67537/4 "2022-09-10T07:59:01Z")

</div>

Hi bekman2,

I thought I did this with following function node:

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

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [10 September 2022 08:01 UTC](https://discourse.nodered.org/t/dashboard-line-graph-show-datapoints-from-database/67537/5 "2022-09-10T08:01:57Z")

</div>

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

---

<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:** [10 September 2022 08:10 UTC](https://discourse.nodered.org/t/dashboard-line-graph-show-datapoints-from-database/67537/6 "2022-09-10T08:10:05Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![RMB](https://avatars.discourse-cdn.com/v4/letter/r/b19c9b/32.png) [@RMB](https://discourse.nodered.org/u/RMB)\
**Post date:** [10 September 2022 08:10 UTC](https://discourse.nodered.org/t/dashboard-line-graph-show-datapoints-from-database/67537/7 "2022-09-10T08:10:13Z")

</div>

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

This is how the data looks after modifying the timestamp:

 ![grafik](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/e/8e58c6314d45b6557150d37dd99974b60664494e.png)

to be continued in next post ...

---

<div class="post-metadata">

**Author:** ![RMB](https://avatars.discourse-cdn.com/v4/letter/r/b19c9b/32.png) [@RMB](https://discourse.nodered.org/u/RMB)\
**Post date:** [10 September 2022 08:10 UTC](https://discourse.nodered.org/t/dashboard-line-graph-show-datapoints-from-database/67537/8 "2022-09-10T08:10:51Z")

</div>

...

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

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

 ![grafik](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/9/c918fa30fa7ca5bebcadf45f84e642a4f9762bde.png)

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

Kind regards  
RMB

---

<div class="post-metadata">

**Author:** ![RMB](https://avatars.discourse-cdn.com/v4/letter/r/b19c9b/32.png) [@RMB](https://discourse.nodered.org/u/RMB)\
**Post date:** [10 September 2022 08:12 UTC](https://discourse.nodered.org/t/dashboard-line-graph-show-datapoints-from-database/67537/9 "2022-09-10T08:12:38Z")

</div>

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

 ![grafik](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/9/c9145d82ee1677c0def3f701c5fb390aff18264c.png)

Kind regards  
RMB

---

<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:** [10 September 2022 08:12 UTC](https://discourse.nodered.org/t/dashboard-line-graph-show-datapoints-from-database/67537/10 "2022-09-10T08:12:49Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![RMB](https://avatars.discourse-cdn.com/v4/letter/r/b19c9b/32.png) [@RMB](https://discourse.nodered.org/u/RMB)\
**Post date:** [10 September 2022 08:17 UTC](https://discourse.nodered.org/t/dashboard-line-graph-show-datapoints-from-database/67537/11 "2022-09-10T08:17:12Z")

</div>

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

---

<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:** [10 September 2022 08:20 UTC](https://discourse.nodered.org/t/dashboard-line-graph-show-datapoints-from-database/67537/12 "2022-09-10T08:20:47Z")

</div>

> [@RMB](#):
>
> Or how would the select statement then look like instead?

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.

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [10 September 2022 08:26 UTC](https://discourse.nodered.org/t/dashboard-line-graph-show-datapoints-from-database/67537/13 "2022-09-10T08:26:22Z")

</div>

Note this comment as [pointed out in the documentation](https://github.com/node-red/node-red-dashboard/blob/master/Charts.md):

> [@dceejay](#):
>
> (one thing is that series property should also be an array)

> 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.

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [10 September 2022 09:32 UTC](https://discourse.nodered.org/t/dashboard-line-graph-show-datapoints-from-database/67537/14 "2022-09-10T09:32:35Z")

</div>

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.

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

```

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [10 September 2022 12:48 UTC](https://discourse.nodered.org/t/dashboard-line-graph-show-datapoints-from-database/67537/15 "2022-09-10T12:48:12Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![RMB](https://avatars.discourse-cdn.com/v4/letter/r/b19c9b/32.png) [@RMB](https://discourse.nodered.org/u/RMB)\
**Post date:** [11 September 2022 23:55 UTC](https://discourse.nodered.org/t/dashboard-line-graph-show-datapoints-from-database/67537/16 "2022-09-11T23:55:36Z")

</div>

Hi E1cid,

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

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

Kind regards  
RMB

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [11 October 2022 23:56 UTC](https://discourse.nodered.org/t/dashboard-line-graph-show-datapoints-from-database/67537/17 "2022-10-11T23:56:29Z")

</div>

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