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

Question

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);

Yaqub Ahmad · Accepted Answer · 2012-12-27 09:22:16Z

3

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'))

edited Dec 27, 2012 at 9:22

answered Dec 26, 2012 at 12:34

Yaqub Ahmad

27.7k24 gold badges105 silver badges151 bronze badges

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

3 Comments

Faheem Kalsekar Over a year ago

thanks. But i think instead of using 'now'(which is system time), i would prefer to use highest time stored in db. How to go about it?

dschulz Over a year ago

@Yaqub I think there's an error in your last query. The subquery after NOT IN will select exactly one record every time. So, if there's more than one record in a given day, those will be deleted too. In fact, this DELETE query will preserve exactly one record (the last inserted) if I'm correct.

dschulz Over a year ago

Here's my version:

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

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related