# Chart: How to display the average data/second if my data is at 200Hz

**URL:** <https://discourse.nodered.org/t/chart-how-to-display-the-average-data-second-if-my-data-is-at-200hz/15423>\
**Category:** Dashboard\
**Created:** [11 September 2019 14:22 UTC](https://discourse.nodered.org/t/chart-how-to-display-the-average-data-second-if-my-data-is-at-200hz/15423 "2019-09-11T14:22:49Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![AK51](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ak51/32/8438_2.png) [@AK51](https://discourse.nodered.org/u/AK51)\
**Post date:** [11 September 2019 14:22 UTC](https://discourse.nodered.org/t/chart-how-to-display-the-average-data-second-if-my-data-is-at-200hz/15423/1 "2019-09-11T14:22:49Z")

</div>

Hi,

I have a Mysql with RealTime as primary key and when I send the query  
SELECT AcX, RealTime from certain\_table WHERE Realtime \>’2019-09-11 22:02:00.00‘ LIMIT 200;

I use the Change Node to modify the payload to feed into the Chart  
[  
{  
"series": ["AcX"],  
"labels": ["AcX\_label"],  
"data": [  
[  
payload.{  
"x": RealTime,  
"y": AcX  
}  
]  
]  
}  
]

And I got a nice chart with x-axis in milliseconds.

First question, I wonder if I feed 2000 data to the chart with 200 points setting, what will happen? Will it scale it for me? I tried to expand the chart to 2000 point but the response is slow.

My another idea is get the average data per minute and plot the chart, but I am not good at manipulate the javascript object, can I do it in the Change Node? Or can the chart node do anything about this function? Thx.

---

<div class="post-metadata">

**Author:** ![shrickus](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/shrickus/32/517_2.png) [@shrickus](https://discourse.nodered.org/u/shrickus)\
**Post date:** [11 September 2019 14:47 UTC](https://discourse.nodered.org/t/chart-how-to-display-the-average-data-second-if-my-data-is-at-200hz/15423/2 "2019-09-11T14:47:57Z")

</div>

This is where databases really shine -- retaining historical data, yet allowing queries to summarize and report trends. It's best if you don't try to pull all the raw data into your flow (or chart!) and then aggregate it into time slots.

Instead, you can use a SQL query to group your data into 1 minute time slots -- the actual syntax may change depending on which database you have, but something like this:

> SELECT AVG(AcX) as avg\_acx,  
> FLOOR(UNIX\_TIME(RealTime) / 60) \* 60 AS time\_slot  
> FROM certain\_table WHERE Realtime \>’2019-09-11 22:02:00.00‘  
> GROUP BY time\_slot;

BTW, if you have a choice of which database to use, I would recommend InfluxDB, which makes summaries by groups of time very trivial -- although its syntax is not quite the same as SQL.

---

<div class="post-metadata">

**Author:** ![AK51](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ak51/32/8438_2.png) [@AK51](https://discourse.nodered.org/u/AK51)\
**Post date:** [11 September 2019 16:16 UTC](https://discourse.nodered.org/t/chart-how-to-display-the-average-data-second-if-my-data-is-at-200hz/15423/3 "2019-09-11T16:16:50Z")

</div>

Thx. It is working except the starting time on the chart is not the same as in the millisecond chart...  
The millisecond chart range is 09-04 23:56:25.367 - 09-04 23:56:26.380  
But the average chart range is 10-29 08:30:00 - 08:46:00  
Note: RealTime is the primary key  
Is it the ORDER problem?Thx

millisecond query  
SELECT \* FROM certain\_table where RealTime \> '2019-09-00 01:01:01' LIMIT 200;

second query  
SELECT AVG(AcX) as avg\_acx, FLOOR((RealTime) / 60) \* 60 AS time\_slot FROM certain\_table where RealTime \> '2019-09-00 01:01:01' GROUP BY time\_slot LIMIT 200;  
 ![average_error](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/8/8e75c497f5423e148e08adfb4dd1c9a5b7c92233.jpeg)

---

<div class="post-metadata">

**Author:** ![AK51](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ak51/32/8438_2.png) [@AK51](https://discourse.nodered.org/u/AK51)\
**Post date:** [11 September 2019 16:28 UTC](https://discourse.nodered.org/t/chart-how-to-display-the-average-data-second-if-my-data-is-at-200hz/15423/4 "2019-09-11T16:28:48Z")

</div>

If I use this query, I only get one object... Sorry, I am new to SQL... Thx

SELECT AVG(AcX) as avg\_acx, FLOOR((RealTime) / 60) \* 60 AS time\_slot FROM lift\_database.liftcar\_table where RealTime \> '2019-09-00 01:01:01' ORDER BY RealTime ASC LIMIT 200; : msg.payload : array[1]

array[1]

0: object

avg\_acx: -3665.2787

time\_slot: 20190904235580

---

<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:** [11 September 2019 17:23 UTC](https://discourse.nodered.org/t/chart-how-to-display-the-average-data-second-if-my-data-is-at-200hz/15423/5 "2019-09-11T17:23:10Z")

</div>

`AVG` is an aggregate function, so it "groups" them, as @shrickus stated, use the `group by` to get 'grouped' output.

---

<div class="post-metadata">

**Author:** ![shrickus](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/shrickus/32/517_2.png) [@shrickus](https://discourse.nodered.org/u/shrickus)\
**Post date:** [11 September 2019 17:36 UTC](https://discourse.nodered.org/t/chart-how-to-display-the-average-data-second-if-my-data-is-at-200hz/15423/6 "2019-09-11T17:36:16Z")

</div>

Bakman2 is correct -- if you want to use the AVG() function you need the GROUP BY ...

Your difference in the times on the chart tics is because it expects JS millis (ms since the epoch), where the sql UNIX\_TIME() function converts it to Epoch seconds (sec since the epoch). You can do that sec -\> millis conversion right in your select statement, just by multiplying by 1000:

> [@AK51](#):
>
> FLOOR((RealTime) / 60) \* 60 \* 1000

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [11 September 2019 19:30 UTC](https://discourse.nodered.org/t/chart-how-to-display-the-average-data-second-if-my-data-is-at-200hz/15423/7 "2019-09-11T19:30:01Z")

</div>

Incidentally, using InfluxDB with Grafana is much more efficient for this kind of thing since InfluxDB has simple commands to aggregate data by time and Grafana can plot things much more intelligently.

---

<div class="post-metadata">

**Author:** ![AK51](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ak51/32/8438_2.png) [@AK51](https://discourse.nodered.org/u/AK51)\
**Post date:** [9 November 2019 17:54 UTC](https://discourse.nodered.org/t/chart-how-to-display-the-average-data-second-if-my-data-is-at-200hz/15423/8 "2019-11-09T17:54:13Z")

</div>

It seems like the new node-red version is more stable. The dashboard seldom crash. I can feed data to mySQL at 1Hz smoothly. And I can get 2000 rows from mySQL without any problem. :\> Thx for all people who helped. Cheers. And yes, I will try InfluxDB later. Thx