CODEWITHSUNDEEP
androidsqlsqliteandroid-sqlite
Faheem Kalsekar
Faheem Kalsekar

Reputation: 1420

SQLite Query: SQLite Query to delete data, such that only current date data exits in db

database schema

I have a database with following columns(screenshot attached)

_id = INTEGER PRIMARY KEY

guid = TEXT(unique id of the image)

pubDate= LONG(time since epoch)

imgsrc = BLOB(image byte)

The requirement is such that i want to keep only images which are downloaded today. I want to delete stale data...i.e yesterdays data.

For ex consider id = 1 with guid = 471c9744666b6f51

This image was downloaded on Wed Dec 26 2012 17:11:05, so database should contain data only for Dec 26 and not Dec 25. Its not necessary that the first pubdate will be have today's date. So, probably first we need to sort the db by pubdate in desc-ding order and then use the first pubdate as an input to delete older data.

Any help as to how to form a SQL statement will be highly appreciated.

Answer:Ended up doing something like this..

long maxDate = 0;

Cursor cursor = getApplicationContext().getContentResolver().query(IMAGES_URI, null, null, null, PUB_DATE + " DESC ");

if (cursor != null && cursor.moveToNext()) {
maxDate = cursor.getLong(cursor.getColumnIndexOrThrow(DatabaseHelper.PUB_DATE));
cursor.close();
}
Calendar cal = Calendar.getInstance();
cal.setTimeInMillis(maxDate);
cal.add(Calendar.HOUR, -12);

int delcount = getApplicationContext().getContentResolver().delete(IMAGES_URI, PUB_DATE + " <= " + cal.getTimeInMillis(), null);

Upvotes: 0

Views: 3365

Answers (1)

Yaqub Ahmad
Yaqub Ahmad

Reputation: 27659

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

EDIT:

String sql = "DELETE FROM myTable WHERE pubDate NOT IN (SELECT MAX(pubDate) FROm myTable)"; 
db.execSQL(sql);

EDIT: Final Version

DELETE FROM myTable WHERE pubDate NOT IN (SELECT pubdate FROM mytable 
WHERE pubDate < datetime((SELECT MAX(pubDate) FROM myTable),'-1 days'))

Upvotes: 3

Related Questions