如何在android中使用SQLiteDatabase类查询方法的sqlite DATE函数？

I am using query function in the SQLiteDatabase class in android for fetching contents from the sqlite database. It works fine. But when I am trying to use date function of sqlite in my query as follows, it does not return any rows.

我在android的SQLiteDatabase类中使用查询函数从sqlite数据库中获取内容。它工作正常。但是当我尝试在我的查询中使用sqlite的日期函数时,如下所示,它不会返回任何行。

SQLiteDatabase mDb = mDbHelper.getWritableDatabase();
String [] selectionArgs = new  String[] {"date('2016-02-10 00:00:00')"};
String selection = "date(column_name) = ?";
String limit  = 4;

String[] ALL_COLUMNS = new String[] { "id", "column_name","column_name2"};

cursor = mDb.query("table_name", ALL_COLUMNS,selection , selectionArgs, "column_name" + " DESC", limit);

if (cursor != null) {
    cursor.moveToFirst();
    while (!cursor.isAfterLast()) {
        //code to read each row
    }
    cursor.close();
}

Can anyone tell me what is wrong with the above code?

谁能告诉我上面的代码有什么问题?

Also, is there a way to compare the date (and not the time) from the sqlite database using SQLiteDatabase class query function?

另外,有没有办法使用SQLiteDatabase类查询函数比较sqlite数据库的日期(而不是时间)?

1 个解决方案

#1

In SQLite dates are strings. Reference: https://www.sqlite.org/lang_datefunc.html
Therefore, simply remove the date() function from the date value and from the column name:

在SQLite中,日期是字符串。参考:https://www.sqlite.org/lang_datefunc.html因此,只需从日期值和列名中删除date()函数:

i.e.: change this

即:改变这一点

String [] selectionArgs = new  String[] {"date('2016-02-10 00:00:00')"};
String selection = "date(column_name) = ?";

to this

String [] selectionArgs = new  String[] {"2016-02-10"};
String selection = "column_name = ?";

If you need the time, too:

如果你也需要时间:

String [] selectionArgs = new  String[] {"2016-02-10 00:00:00"};
String selection = "column_name = ?";

See the SQLite supported datetime strings in the link above

请参阅上面链接中的SQLite支持的datetime字符串

#1