SQLite - Problem with DateTime format when query any row comparing DateTime into WHERE clause

问题: 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 = Calen...

问题:

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??


回答1:

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.

  • 发表于 2019-04-01 20:59
  • 阅读 ( 119 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除