How to archive a SQLite database

I have an SQLite databese in which i save temperature and humidity data with timestamps (epoch). I want to archive the databse by months. So that when november ends my data gets saved to another database for december etc. How can i achieve that?

Have you considered using something like Influxdb rather than sqlite? Then you can always have previous months instantly accessible.

Alternatively you can use one of the timer nodes, node-red-contrib-cron-plus is my favourite, to give you an event at the start of each month which you can use to do the database archive.

Is that defined as a datetime field in the DB? If so you can do a simple query on date to extract the data to a different DB. Then a delete query using the same date/time spec.

But as Colin says, InfluxDB is designed from the ground up to handle this. You can specify a retention policy to continuously trim the data along with a "continuous query" to copy the data to another db. You can also summarise the data to the 2nd db. So I keep sensor data at a per-minute level for a week and summarise to hourly data which I keep for a few years.

Well I’m a total newbie in database systems so I have no idea about how InfluxDB works. Guess I’ll have to find out. Thanks

1 Like

This might help a little :slight_smile:

How to effectively use InfluxDB - FAQs - Node-RED Forum (

1 Like

But influxDB free plan allows to store data only for 30 days, am I right?

No, you want InfluxDB server. Self-hosted. No limits.

InfluxDB website literally says “server less”.
I think I’m missing something

Can you give us a link to that please?

Nevermind, I had to be on InfluxDB Cloud website.
Anyway, i downloaded InfluxDB and hope to sort it out soon.

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