Android get items from sqlite db with date of today or previous -

i'm struggling implementation of dates in sqlite3 (for android). according documentation (https://www.sqlite.org/datatype3.html , http://www.sqlite.org/lang_datefunc.html), sqlite doesn't have datatypes date , time , therefore can either stored text or integer. i've tried both, give same erroneous results. after reading lot on internet , trying can think of, come here last resort.

so, problem. idea simple. have table containing items, date column. want select items database have date of today, or before (i.e. today or in past). in current implementation store dates integers, since people seem agree that way go.

below (simplified) code inserting item.

calendar calendar = gregoriancalendar.getinstance(); calendar.set(calendar.hour_of_day, 0); // might not necessary calendar.add(calendar.day_of_year, 4); // today plus 4 days  sqlitedatabase db = dbopenhelper.getwritabledatabase(); contentvalues values = new contentvalues(); values.put(column_date, calendar.gettimeinmillis()); // today integer/long long id = db.insert(table_items, null, values); // add db 

and next want select items date equal or lower today:

sqlitedatabase db = dbopenhelper.getreadabledatabase(); string[] columns = new string[]{column_id, column_text, column_date}; string[] selectarg = new string[]{"date('now')"}; cursor citems = db.query(table_items, columns, column_date + "<= ?", selectarg, null, null, null); //cursor citems = db.query(table_items, columns, "strftime('%y-%m-%d'," + column_date + ")" + select, selectarg, null, null, null); //cursor citems = db.query(table_items, columns, "date(" + column_date + ")" + select, selectarg, null, null, null); 

now rows selected, rows date in future. tell me need differently?

thanks in advance!

edit

i found out sqlite stores date integers in seconds (is correct?). mean value put in database should math.round(calendar.gettimeinmillis()/1000, in seconds right? makes less sence, since date in milliseconds should larger date in seconds. anyhow, tried that, doesn't work either.

thanks insights cl. gave me, fixed trivial errors made.

looking @ sqlite documentation, figured there 2 ways of storing date without time. julian day number or date string yyyy-mm-dd. there no readily available function julian day number in java/android, therefore chose go string format again.

a second, mistake figured out selectargs considered text , therefore, putting date('now', 'localtime') in there won't work.

below final scripts work.

calendar calendar = gregoriancalendar.getinstance(); calendar.set(calendar.hour_of_day, 0); // might not necessary calendar.add(calendar.day_of_year, 4); // today plus 4 days  simpledateformat dateformat = new simpledateformat("yyyy-mm-dd"); contentvalues values = new contentvalues(); values.put(column_date, dateformat.format(calendar.gettime())); // date string   sqlitedatabase db = dbopenhelper.getwritabledatabase(); long id = db.insert(table_items, null, values); // add db 

and data back:

sqlitedatabase db = dbopenhelper.getreadabledatabase(); string[] columns = new string[]{column_id, column_text, column_date}; string[] selectarg = new string[]{}; cursor citems = db.query(table_items, columns, column_date + "<= date('now', 'localtime')", selectarg, null, null, null); 

cl., many help! in end simple...