Let’s say i’m having a database which includes a table like this:
CREATE TABLE tbl_EX (_id TEXT, TIME TEXT);
And then I insert a value like this:
Date currentTime = Calendar.getInstance(Locale.getDefault()).getTime();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.getDefault());
String time = dateFormat.format(currentTime);
ContentValues contentValues = new ContentValues();
contentValues.put("_id", "SomeID");
contentValues.put("TIME", time);
database.insert("tbl_EX", null, contentValues);
After that, I try to query. Without WHERE clause:
database.query("tbl_EX", new String[]{"_id", "TIME"}, null, null, null, null, "TIME");
It retrieved me all records as expected, which are shown in 2 TextView like this:
_id = SomeID | Time = 2019-03-30 15:00:00
BUT, when I make query with this WHERE clause:
database.query("tbl_EX", new String[]{"_id", "TIME"}, "date(TIME) = ?", new String[]{"date('now')"}, null, null, "TIME");
No data found! I even try replacing the part new String[]{"date('now')"} to
new String[]{"date('2019-03-30')"}or
new String[]{"strftime('%Y-%m-%d', 'now')"} or even new String[]{"'2019-03-30'"}, still no go.
So, did I store the DateTime data in the SQLite database in the right way? And querying it in the right way too??
Advertisement
Answer
When you pass
new String[]{"date('now')"}
as an argument, this is translated to this query:
select _id, TIME from tbl_EX where date(TIME) = 'date('now')'
Can you see the problem?
date('now') is treated as a string argument for the WHERE clause, so your query searches for literals date('now') in the column TIME.
What you should do instead is this:
database.query("tbl_EX", new String[]{"_id", "TIME"}, "date(TIME) = date(?)", new String[]{"now"}, null, null, "TIME");
This way the parameter now will be passed and your query will be:
select _id, TIME from tbl_EX where date(TIME) = date('now')
Similarly when you want to filter for a specific date like 2019-03-30 you must do:
database.query("tbl_EX", new String[]{"_id", "TIME"}, "date(TIME) = ?", new String[]{"2019-03-30"}, null, null, "TIME");
So you pass 2019-03-30 without the single quotes.
Everything you include in the selectionArgs parameter is treated as a String literal and will be actually surrounded by single quotes in the statement that will be executed.
You can read more here.