Using SQLite Data Base for temporary data

General
1

I am starting to use SQLite for storing some values from a continuous flow of input data.
My purpose is that the data storage is temporary, and I don't want de DB file to grow forever.

Is there any SQL command (or any thing that I can do) to delete the oldest data so that the number of rows becomes steady? I mean some kind of FIFO...

I guess that I could use the DELETE sql command conditioned to a number given by the command COUNT, but I wonder I is there a more direct command...

2

SQL data isn't necessarily like that so you won't find trimming commands in a SQL db, you need to use a timeseries db like InfluxDB if you want to do that.

However, a delete can use a select statement as a filter (I'm fairly sure, my SQL is a bit rusty). So create a WHERE that selects the date/time values embedded in the data that you want to remove.

WHERE mydatefield <= 2020-01-01

for example (that may be invalid SQL though, as I say, I'm rather rusty.

Here are some alternatives:

3

I'm using this script in a function node to delete all the data from table consoles in my sqlite dbase that's older then 60 days.

var p_60d  = 1000*60*60*24*60 ; //60 Days
var p_30d  = 1000*60*60*24*30 ; //30 Days
var p_7d  = 1000*60*60*24*7 ; //7 Days
var p_1d   =  1000*60*60*24 ; // 1 Day
var p_1u = 1000*60*60; //1 Hour

var d = new Date();
var current = d.getTime();
var fromdate = current - p_60d;

msg.topic ="DELETE FROM consoles WHERE  timestamp < " + fromdate + "" ;

return msg;
4

When you say temporary do you mean seconds, minutes, hours, days, weeks or what?

You might find that using persistent context storage might be much simpler that using sqlite. https://nodered.org/docs/user-guide/context