Write a monthly temperatures File

General
1

Hi, I have several temperature sensors sending data to gauges on a dashboard, the data is put into a variable in a function node, what i want to do is write a file automatically at the right time of the month with all the temperatures on it, would this be easy to do ? any help appreciated.

Thanks
Stuart

2

It sounds easy enough, but exactly why do you want to do this?

Have you considered a simple database, eg SQLite?
You could save each temperature reading to the database as it arrives in Node-red (I think you are using MQTT?).
Then if you need a monthly summary for emailing or similar you can create it with an SQL query, outputting to a file.

I run Node-red, Mosquitto and SQLite together quite comfortably on a Raspberry Pi Zero 2 so certainly no problem on a beefier Pi such as the Pi 5.
A Pi is perfect for an mqtt broker and db server too since it can easily be left running 24/7

1 Like
3

Hi Jbudd,

I have about 20 gauges and they are updated every 30 seconds so I would end up with a massive database really, a simple file once a month as a recording would suit me better I think, I am just not sure how to do it, I already have some nodes that send an email if the temperatures become out of range so maybe I could modify this ?

Thanks
Stuart

4

It is far easier and more resource efficient to use InfluxDB (or a similar Time Series DB) for this. That's what most of us do.

InfluxDB has the tools built right in to do all the work for you. Here is what I do:

  • Write the detailed environment data into a table. Each of my sensors outputs roughly once per minute.
  • I tell InfluxDB to only keep the details for one week.
  • I also tell InfluxDB to summarise the per-minute data to per hour data - turning each aggregated measurement into a max, min, and avg measure per hour.
  • I finally tell InfluxDB to keep the hourly data for 5 years.

I track a LOT of data in InfluxDB since it isn't only environmental data but also system performance data. I can dig out the DB sizes if you like but I used to run all of this and a lot more on a single Pi3b.

1 Like
5

If you really don't want a database, you probably need to retain the readings in a persistent context variable until you write them to a file, whether you write the file once a month or write a new line every 30 seconds.
The context variable might be json format. I'm not really sure since as I said, I prefer to use a database.

There is a Write-file node which can be set to overwrite it's target file or to append to it if it exists.

1 Like
6

Hi Totally,

Think I might give this a try, Is InfluxDB a free download, not heard of it before

Cheers
Stuart

7

Yes, it is free, at least for non-commercial support. Do yourself a favour and stick to the v1.8 style queries and don't use the v2 queries. :slight_smile:

There is an article I wrote here on the forum (in FAQ's?) some years ago about the terminology and configuration that works well with Node-RED. It takes a little getting used to, especially if you know SQL. But once set up and running, you'll never need to touch it.

The other advantage is that - with the v1.8 queries - you can get full point-and-click query building and graphical output by also using InfluxDB with Grafana.

1 Like
8

Thanks
I will have a read in a bit

Stuart

9

Hmm it may well be more efficient but I'd argue with the far easier bit.
While SQL databases are hardly straightforward, at least the INSERT SELECT & DELETE queries are logical.
I find Influx distinctly odd with it's measurements and buckets.

1 Like
10

Hi Jbudd,
I am just looking at options really, I work for a charity and so I am classed as commercial I suppose so not looking to buy any licenses, Is the SQL database free to use ?

Thanks
Stuart

11

There is a fully Open Source version of InfluxDB so you will be fine if you want to use that.

1 Like
12

Thanks
I will have a look at all this later when I get home

Stuart

13

It takes a few minutes to work out. :slight_smile:

That's why I wrote the article. Done once and forgotten about until needed - which is pretty much never.

The important part is that you don't have to worry about getting aggregations right, a very simple query gives you aggregations over time periods. And if you use Grafana, it is even easier.

1 Like
14
2 Likes
15

Thanks for the info, I might set it up on my own computer at home first and see if I can get my head round it lol

Stuart

1 Like
16

I see you are leaning towards Influx and it's a perfectly sound choice.

A quote from sqlite.org:
The core SQLite database software is in the public domain. No license is necessary to use SQLite or to build SQLite into your products.

2 Likes
17

Hi,
They both look a little over the top for what i need, I think I need to get my head around variables first and find out how they work in node-red, I have variables in my function flows and was told if I go to context data in the menu that the current values of the variables would be displayed but it shows nothing so I will have to post about that.

Thanks
Stuart

18

Hey, don't be put off by using a simple file to store your data. I ran this method together with a database to record environmental data for years.

When I wanted to take a look at the data (do some stats on it) it was always the comma delimited file that I grabbed, not the database.

Good luck.

Bob

1 Like
19

Hi Bobo,

Thanks, good to know, I know it should be a simple thing to do but still learning

Stuart

20

My two tips would be:

  1. To avoid the risk of losing data, consider regular file saves (every few minutes), and
  2. To avoid the risk of losing the file in 1. make sure you do a backup to different media also on a regular basis.
  3. Bonus tip, regularly check the integrity of your file by examining the last few records (readings).

NR can do all of the above very easily.

1 Like