How to delete old rows from sqlite database on android?

Question

I have a parameter, where user can choose for which period of time data should be stored. I read it in days variable. Date and time, when record was added to the database is stored in KEY_DATE_ADDED, it is created like:

SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  // sql date format
Date date = new Date();
initialValues.put(KEY_DATE_ADDED, dateFormat.format(date));

Then I do the following:

mDb.delete(DATABASE_TABLE, 
           "date(now) > date(" + KEY_DATE_ADDED + ",+" + days + " days)",
           null);

and it doesn't work. Actual query (where part) looks like:

WHERE date(now) > date(date_added,+10 days)

What is incorrect here? Looks like instead of date_added something else should be here. I wanted to use SQL data formatting to avoid re-formatting dates several times.

Is there any GUI for Android database to test SQL queries on the actual data?

ok, I should use date('now') > date('date_added','+10 days') - it works now. So, the open question is only about GUI. — LA_
– LA_, Commented Mar 26, 2011 at 15:50
you can use adb pull /data/data/<namespace>/databases/<dbName> to pull the db from the emulator. I use the Firefox add-on "SQLite Manager" to view or edit the database. — Rajath
– Rajath, Commented Mar 26, 2011 at 15:57
What I did to test queries and whatnot was play around with SQLiteBrowser: sourceforge.net/projects/sqlitebrowser It's pretty easy to work with, you just have to extract your Database from your device (emulator or actual phone) and then go wild. I'm not really at my work station right now, but extracting the DB is fairly simple through the interface. Emulator/device instances store SQLite3 databases in the folder /data/data/<package_name>/databases/. See this question too: stackoverflow.com/questions/3292818/… — Codemonkey
– Codemonkey, Commented Mar 26, 2011 at 15:58

LA_ · Accepted Answer · 2011-03-28 20:05:07Z

3

ok, I should use date('now') > date('date_added','+10 days')

answered Mar 28, 2011 at 20:05

LA_

20.5k61 gold badges181 silver badges321 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tacone Over a year ago

I guess it should be date('now') > date(date_added,'+10 days')

Paul Lefebvre · Accepted Answer · 2011-03-29 18:59:17Z

1

Keep in mind that may also need to periodically use the VACUUM command to actually reduce the size of the database.

http://www.sqlite.org/lang_vacuum.html

answered Mar 29, 2011 at 18:59

Paul Lefebvre

6,4364 gold badges31 silver badges37 bronze badges

Collectives™ on Stack Overflow

How to delete old rows from sqlite database on android?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related