Data range filter not working in SQLite used in Android
I am running SQLite to select data between two ranges for the generated execution. To select data from two dates, I use the following statement
public ArrayList<LoopExecution> getDateRangeLoop(Date fromDate, Date toDate, int limit) {
String query = "SELECT * FROM"
+ " loopexe "
+ " WHERE "
+ " created_on "
+ " BETWEEN " + fromDate.getTime()
+ " AND " +toDate.getTime();
Cursor cursor = database.rawQuery(query, null);
ArrayList<LoopExecution> loopExecutions = new ArrayList<LoopExecution>();
if(cursor.moveToNext()) {
do {
LoopExecution loopExecution = new LoopExecution();
loopExecution.setId(cursor.getString(0));
Date createdOn = new Date(cursor.getLong(1));
loopExecution.setCreatedOn(createdOn);
loopExecution.setCreatedBy(cursor.getString(2));
loopExecution.setLoopId(cursor.getString(3));
loopExecution.setLoopStatus(LoopExecution.LoopStatus.valueOf(cursor.getString(4)));
loopExecution.setImage(cursor.getString(5));
loopExecution.setResultJson(cursor.getString(6));
loopExecution.setBgImage(cursor.getString(7));
loopExecution.setTrendImage(cursor.getString(8));
loopExecutions.add(loopExecution);
} while (cursor.moveToNext());
}
cursor.close();
return loopExecutions;
}
create query in SQLite database
public static final String CREATE_LOOP_EXC_TABLE = CREATE_TABLE + LOOP_EXC_TABLE_NAME
+ "(" + ID + " INTEGER PRIMARY KEY AUTOINCREMENT , "
+ CREATED_ON + "TIMESTAMP , " + CREATED_BY + "VARCHAR , "
+ LOOP_ID + "VARCHAR , " + LOOP_STATUS + "INTEGER NOT NULL, "
+ LOOP_IMAGE + "VARCHAR NOT NULL, " + RESULT_JSON + "VARCHAR , "
+ LOOP_BG_IMAGE + "VARCHAR , " + LOOP_TREND_LINE + "VARCHAR , "
+ "FOREIGN KEY(created_by) REFERENCES user(id) , "
+ "FOREIGN KEY(loop_id) REFERENCES loop(id) " + ")";
source to share
Option one (if it created_on
has a type INTEGER
):
Since you want to use the long timestamp (just a number) that it retrieves getTime()
, you must use the <
and operators >=
.
String query = "SELECT * FROM"
+ " loopexe "
+ " WHERE "
+ " created_on "
+ " >= " + fromDate.getTime()
+ " < " + toDate.getTime();
Option two (if it created_on
has a type TEXT
):
You can use an operator BETWEEN
, but then you need to specify its arguments in a different format.
String query = "SELECT * FROM"
+ " loopexe "
+ " WHERE "
+ " created_on "
+ " BETWEEN '2017-07-10'"
+ " AND '2017-07-14'";
This is just an example with hardcoded dates. To make it dynamic:
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
String date = formatter.format(today);
source to share