Android get items from sqlite db with date of today or previous
<p>I'm struggling with the implementation of dates in SQLite3 (for android). According to the documentation (<a href="https://www.sqlite.org/datatype3.html" rel="nofollow">https://www.sqlite.org/datatype3.html</a> and <a href="http://www.sqlite.org/lang_datefunc.html" rel="nofollow">http://www.sqlite.org/lang_datefunc.html</a>), SQLite doesn't have datatypes for date and time specifically and therefore it can be either stored as a TEXT or as an INTEGER. I've tried both, but they give the same erroneous results. After reading a <em>lot</em> on the internet and trying everything I can think of, I come here as a last resort.</p>

<p>So, now for the problem. The idea is actually very simple. I have a table containing items, with a date column. Now I want to select all items from this database that have a date of today, or before (i.e. today or in the past). In my current implementation I store dates as integers, since most people seem to agree that that is the way to go.</p>

<p>Below is the (simplified) code for inserting an item.</p>

<pre><code>Calendar calendar = GregorianCalendar.getInstance();
calendar.set(Calendar.HOUR_OF_DAY, 0); // Might not be 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 as integer/long
long id = db.insert(TABLE_ITEMS, null, values); // Add to db
</code></pre>

<p>And next I want to select the items where the date is equal to or lower than today:</p>

<pre><code>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 + "&lt;= ?", 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);
</code></pre>

<p>Now all rows are selected, also the rows with a date in the future. Could anyone tell me what I need to do differently?</p>

<p>Thanks in advance!</p>

<p><strong>Edit</strong></p>

<p>I found out that sqlite stores date integers in seconds (is that correct?). So that would mean that value I put in the database should be <code>Math.round(calendar.getTimeInMillis()/1000</code>, to get it in seconds right? But then it makes even less sence, since a date in milliseconds should always be larger than a date in seconds. Anyhow, I tried that, but it doesn't work either.</p>