deleting android SQLite rows older than x days

Question

I want to delete all rows in table MYTABLE which are older than x days. Column SAVE_DATE Long is the time when the row was inserted in table.

I tried this but apparently it deletes all my rows:

long daysInMiliSec = new Date().getTime() - X
            * (24L * 60L * 60L * 1000L);
return db.delete(MYTABLE , SAVE_DATE
            " <= ?", new String[] { "" + daysInMiliSec }

What is wrong?

Answer 1

Below query will delete data older than 2 days:

String sql = "DELETE FROM myTable WHERE Save_Date <= date('now','-2 day')"; 
db.execSQL(sql);

Answer 2

Since it's the first hit on google some more explanation for beginners:
You do not need the time/date functions from the main program you use to access the sqlite DB but use the sqlite date functions directly.

You create the table with the row entry for the age with for example:

CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, text TEXT, age INTEGER)

You write to it with

INSERT INTO test (text, age) VALUES ("bla", datetime('now'))

Here I used 'datetime' because this also will let you later search for hours/minutes/seconds. If you don't need that 'date('now')' is enough.

Here is an explanation for the date function: https://www.sqlite.org/lang_datefunc.html

To select everything older than for example 5 minutes:

SELECT * FROM test WHERE age <= datetime('now', '-5 minutes')

You can see more of those possibilities on the website above under the paragraph 'Modifiers'.

Answer 3

Delete data older than 2 days when the timestamp or date field is stored in milliseconds or an epoch integer.

DELETE FROM update_log WHERE timestamp <= strftime('%s', datetime('now', '-2 day'));