Friday, June 03, 2005

sqlite time and date, garbage collection

Phew, I _finally_ managed to figure out how to garbage collect old values in a sqlite database.

There wasn't much documentation on the date/time functions in sqlite, so I had to do quite a bit of reading on that, strftime() as well as locale times (because that heavily influences the strftime () values.)

Anyway, here is the magic code I used:
DELETE FROM queries WHERE (strftime('%s', time_created) + keepalive*60 - strftime('%s','now', 'localtime') < 0)

where my table 'queries' has columns 'query', 'time_created' and 'keepalive' (in minutes).

The formula is obvious:
if (time_created + keepalive - current_time < 0) {
delete the query;
}

and that's what that SQL does.

No comments: