android - How to count the records in a Sqlite table that match a date restriction? -

i'm using cursorloader return record count content provider, , need count records 7 or fewer days old. attempts make work have far yielded nothing frustration, count always zero. have limited experience sqlite , cursorloaders, , suspect i'm making simple error . here code:

string strcriteria = "select julianday('now') - julianday('" + session.date_string             + "','gregorian') <8";  return new cursorloader(getsherlockactivity(), session.content_uri,             new string[] {"count(" + session.table_name + "." + session._id +") count " },            strcriteria, null,null);   

can see going wrong?

i should add date_string column stores date string in yyyy-mm-dd format. have column storing date long, haven't figured out how sqlite date queries using numeric values either.

update question: if replace session.date_string part of strcriteria definition text date, works perfectly. example, if use following code :

string strcriteria = "select julianday('now') - julianday('" + "2013-05-08"             + "','gregorian') <8"; 

...instead of i've got above, works perfectly. leads me believe code not putting value of session.date_string column string, rather else, actual string name of column. not know how around this, assuming query fill in proper data value @ runtime. suggestions appreciated!

edit solution: i've figured out how make work properly. it's quite simple - had remove ' around session.date_string ensure value of column inserted, rather column name. so, how strcriteria needed defined:

string strcriteria = "select julianday('now') - julianday(" + session.date_string             + ",'gregorian') <8"; 

i'm not sure if work intend, have cache cleaner in app clears record database have been stored more x days ago.

step one: when saving each record, save date millis. keep in mind, save date string, since use labeling , other stuff too.

ste two: create statement:

string = dbopenhelper.saved_on + " <= " + (system.currenttimemillis() - msbackintime); 

where msbackintime amount of days in millis want delete. instance, if want go 3 days , erase, msbackintime be: 259200000. way, query return items less (and therefore mean stored before) date supplied result.

quick example:

right is: 1368020762363 in millis. 3 days in millis is: 259200000 3 days ago is: right (1368020762363) minus 3 days (259200000).

ergo, saved_on value lower result of difference i'll in query.

hope helps.