Advice please! | store data to file or database

[Magnus_P]

I have 3 energy meters (Shelly 3EM) showing graphs in the dashboard of the latest 24 hours data. The data is received by MQTT topics

Now I want to store, search and preset the data in various graphs and histograms over longer time periods than only latest 24 hour. Example is to present a power consumption histogram over a selected period of months or year, or present the highest current for phase 1 for a specific period etc.

My question
What is the most preferred solution to store and extract data from? Tips of nodes to use is also welcome.

• Save to one file and continuously append data
• Save to multiple files, one per month
• Save data to database e.g. SQL, SQLite

[Colin]

Have a look at storing it in influxdb and displaying using Grafana. That is a very common setup for storing and charting time series data. Influxdb is very easy to interface with node-red.
If you are looking at influx I suggest starting with V1.8 rather than V2, as there are large changes in V2 and it is only just starting to be used, so getting help with V2 may be tricky. There may be an argument for going with V2 for your production setup once you have played with V1.8 and got the hang of what it is all about though, as V2 is the future.

[TotallyInformation]

Agree with Colin that InfluxDB is your best bet. Watch out for the not quite SQL syntax though, it does tend to catch people out.

Have a look at the following answer I gave to a question which should help you with the terminology and structures that you probably want.

Need more detailed information on influxdb - General - Node-RED Forum

You should also look at Grafana which is a great way to produce excellent dashboards and charts from InfluxDB data.

[Magnus_P]

@Colin

influxdb

Thanks, I will check out the influxdb.

But still a question is how to put the data to the database which are received via MQTT, are then node-red the alternative or is this supported by influxdb itself?

[Colin]

Since you are already using node-red then probably easiest to pick up from MQTT in node-red and save to influxdb. I believe there are other alternatives (telegraf?) but node-red will do it perfectly well.

[TotallyInformation]

Because you can write to InfluxDB using a REST API call (a URL), almost anything that can call a URL can write to it.

For example, you could easily get an ESP8266 microcontroller to write directly to it.

However, I agree with Collin that in most cases it will be easier to simply use Node-RED and that's what I do.

The exception is that I also use Telegraf to capture system data directly into InfluxDB. It is a great way to keep track of system and network performance over time. Because Grafana can also generate alerts, that's also useful for spotting system or networking issues early. You could do that in Node-RED as well of course, but the setup may be easier for some things in Grafana.

Here are some Grafana examples. They all show data from InfluxDB: