mercredi 24 juin 2015

Android select from SQLite database where date (stored as int) = today?

I have dates stored in a SQLite table in int format (i.e. milliseconds - derived from System.currentTimeMillis()). I now want to query all rows from the table where the date is equal to today's date, but the query below always returns zero, even though file_uploaded_date in the upload_history table is set to today's date in at least 20 rows. Can anyone tell me what's wrong with my query?

String today = new SimpleDateFormat("d-MMMM-yyyy").format(new Date());
String sql = "SELECT COUNT(*) as uploaded_today from upload_history "
    + "WHERE strftime('%-d-%b-%Y',file_uploaded_date) = strftime('%-d-%b-%Y','" + today + "')";
Cursor cursor = db.rawQuery(sql, null);
if(cursor != null && cursor.moveToFirst()){
   int uploadedToday = cursor.getInt(cursor.getColumnIndex("uploaded_today"));
}

Aucun commentaire:

Enregistrer un commentaire