# Realtime data - average and mysql insert
**URL:** https://discourse.nodered.org/t/realtime-data-average-and-mysql-insert/67141
**Category:** General
**Tags:** database
**Created:** [30 August 2022 17:23 UTC](https://discourse.nodered.org/t/realtime-data-average-and-mysql-insert/67141 "2022-08-30T17:23:02Z")
**Posts on this page:** 10
**Page:** 1
### Author:  [@mafo](https://discourse.nodered.org/u/mafo)
#### Post date: [30 August 2022 17:23 UTC](https://discourse.nodered.org/t/realtime-data-average-and-mysql-insert/67141/1 "2022-08-30T17:23:02Z")
Hi,
I am new in node-red. I have temp humidity and barometric pressure sensors (30 pcs) in different places. Every sensor send me a message to rs485 every 15-20 sec. It is a lot of data. I want to do this: with each message from each sensor do average and after a couple of time send it to MySQL database which is on server.
I try to do aggregate for every sensor, but for now, there are 30 sensors.
How to do this?
Message data looks like this.
payload: [{sensor id, humidity, temperature, pressure}]
Thanks
---
### Author:  [@zenofmud](https://discourse.nodered.org/u/zenofmud)
#### Post date: [30 August 2022 17:49 UTC](https://discourse.nodered.org/t/realtime-data-average-and-mysql-insert/67141/2 "2022-08-30T17:49:14Z")
1 - why are you taking the readings so often?
2 - have you looked at the influx db? You can store the data and create rules to consolidate it
---
### Author:  [@E1cid](https://discourse.nodered.org/u/E1cid)
#### Post date: [30 August 2022 18:47 UTC](https://discourse.nodered.org/t/realtime-data-average-and-mysql-insert/67141/3 "2022-08-30T18:47:14Z")
You should be able to use the split, smooth and join nodes to average nth number of readings, using sensor\_id and readings property names as unique topics.
e.g.
```auto
[{"id":"d2137b3d.ea539","type":"inject","z":"30af2d3e.d94ea2","name":"1","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"sensor_id\":1,\"humidity\":30,\"temperature\":10,\"pressure\":500}]","payloadType":"json","x":110,"y":1920,"wires":[["234d0a8f.1b7c2e"]]},{"id":"234d0a8f.1b7c2e","type":"change","z":"30af2d3e.d94ea2","name":"","rules":[{"t":"move","p":"payload[0]","pt":"msg","to":"payload","tot":"msg"},{"t":"move","p":"payload.sensor_id","pt":"msg","to":"id","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":270,"y":1980,"wires":[["8ec0f7a.56a1408"]]},{"id":"816ff415.94e3c8","type":"inject","z":"30af2d3e.d94ea2","name":"1","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"sensor_id\":1,\"humidity\":60,\"temperature\":20,\"pressure\":1000}]","payloadType":"json","x":120,"y":1980,"wires":[["234d0a8f.1b7c2e"]]},{"id":"7db6ef6f.82ca9","type":"inject","z":"30af2d3e.d94ea2","name":"2","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"sensor_id\":2,\"humidity\":60,\"temperature\":20,\"pressure\":1000}]","payloadType":"json","x":110,"y":2040,"wires":[["234d0a8f.1b7c2e"]]},{"id":"e07d32a9.9a289","type":"inject","z":"30af2d3e.d94ea2","name":"2","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"sensor_id\":2,\"humidity\":30,\"temperature\":10,\"pressure\":500}]","payloadType":"json","x":118.33332824707031,"y":2080.66650390625,"wires":[["234d0a8f.1b7c2e"]]},{"id":"8ec0f7a.56a1408","type":"split","z":"30af2d3e.d94ea2","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"topic","x":430,"y":1980,"wires":[["f0eeafc0.0788b"]]},{"id":"f0eeafc0.0788b","type":"change","z":"30af2d3e.d94ea2","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"$$.topic & $$.id","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":590,"y":1980,"wires":[["e0bfe459.990cd"]]},{"id":"e0bfe459.990cd","type":"smooth","z":"30af2d3e.d94ea2","name":"","property":"payload","action":"mean","count":"3","round":"2","mult":"multi","reduce":true,"x":760,"y":1980,"wires":[["608402e6.8c6294"]]},{"id":"608402e6.8c6294","type":"join","z":"30af2d3e.d94ea2","name":"","mode":"auto","build":"string","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":890,"y":1980,"wires":[["3c623628.42c482"]]},{"id":"3c623628.42c482","type":"change","z":"30af2d3e.d94ea2","name":"","rules":[{"t":"move","p":"id","pt":"msg","to":"payload.sensor_id","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":610,"y":2040,"wires":[["8043f003.11f8b8"]]},{"id":"8043f003.11f8b8","type":"debug","z":"30af2d3e.d94ea2","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":810,"y":2040,"wires":[]}]
```
Set in smooth node to average every 3 readings. You will need to install the smooth node from the pallette.
Hope it helps.
---
### Author:  [@mafo](https://discourse.nodered.org/u/mafo)
#### Post date: [31 August 2022 08:29 UTC](https://discourse.nodered.org/t/realtime-data-average-and-mysql-insert/67141/4 "2022-08-31T08:29:59Z")
It is an area monitor which needs accurate temp. And when the temperature is getting high, I must know it quickly.
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [31 August 2022 08:47 UTC](https://discourse.nodered.org/t/realtime-data-average-and-mysql-insert/67141/5 "2022-08-31T08:47:10Z")
Is it really worth doing the averaging? Is there a lot of noise in the signal so you need to average it? Otherwise sample at whatever rate you need for your real time monitoring and then just send one every 5 mins (or whatever is appropriate) to the database.
---
### Author:  [@mafo](https://discourse.nodered.org/u/mafo)
#### Post date: [31 August 2022 09:15 UTC](https://discourse.nodered.org/t/realtime-data-average-and-mysql-insert/67141/6 "2022-08-31T09:15:42Z")
Hi, thanks.
I don't really need that much information. But at first, these data were sent through rs485 devices where I could not change the sampling rate. I was thinking that average is the simplest way to slow down data transfers.
---
### Author:  [@mafo](https://discourse.nodered.org/u/mafo)
#### Post date: [31 August 2022 09:23 UTC](https://discourse.nodered.org/t/realtime-data-average-and-mysql-insert/67141/7 "2022-08-31T09:23:03Z")
And at all, raw data looks like this:
{\*072 4 411.8 20.28 8351 240.1 33.04 7905 46 10870
11} - {id status dcvoltage dccurrent dcpower acvoltage accurrent acpower temperature todayPower humidity} pressure don't need|
I use function to split data into something that I can analyze, then wait with data for at least 1 min and then send it as is to SQL server
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [31 August 2022 09:41 UTC](https://discourse.nodered.org/t/realtime-data-average-and-mysql-insert/67141/8 "2022-08-31T09:41:45Z")
> [@mafo](#):
>
> I was thinking that average is the simplest way to slow down data transfers.
To slow down the data transfers then use a Delay node in rate limit mode, with discard intermediate messages set.
---
### Author:  [@mafo](https://discourse.nodered.org/u/mafo)
#### Post date: [31 August 2022 17:36 UTC](https://discourse.nodered.org/t/realtime-data-average-and-mysql-insert/67141/9 "2022-08-31T17:36:52Z")
Thanks it works well
---
### Author:  [@system](https://discourse.nodered.org/u/system)
#### Post date: [14 September 2022 17:37 UTC](https://discourse.nodered.org/t/realtime-data-average-and-mysql-insert/67141/10 "2022-09-14T17:37:05Z")
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.