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"));
}
I'd say you have your format-strings incorrect.
I don't see a
%b
argument in the documentation. For month, you would want to use%m
. Also%-d
doesn't seem right. Use the following format string instead:%Y%m%d
.Further, you are then passing a poorly-formatted string into the query, rather than the int, and relying an sqlite to correct that. Instead, compare to a
SimpleDateFormat( "yyyyMMdd" )
without further conversion.Your code would then look like this:
(Note that if you return only one column, you don't have to name it, and can just access the first column for the result.)
Further, please be aware that this query will cause a table-scan every time it's executed, as sqlite needs to convert all the epochs to strings. You'd be better off adding a date column to the table, and update it once:
This will then allow you to do much quicker searches, and even search by year or month, using the
LIKE
operator. If your table is very large, you might want to put an index on that column as well.