title: Android get items from sqlite db with date of today or previous
body: <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>