Using SQLite Data Base for temporary data

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...

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:

1 Like

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;
1 Like

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.

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