我经历了无数帖子,但似乎仍然无法实现所需的结果。
我的日期/时间戳存储在SQLite数据库中,其中一个看起来像:Sun Apr 16 00:26:33 GMT 01:00 2017
我要实现的目标是从最近的48小时(现在直到2天前(中仅检索数据库中存储的项目。我已经提出的以下代码继续返回超过48小时/2天的结果。
public Cursor searchInboxEmails(String search, String twoDaysAgo, String today) throws SQLException {
Cursor cursor = null;
try {
if (search != null && !search.trim().isEmpty()) {
String received = "Received";
search = "%" + search + "%";
String query = "SELECT * FROM " + TABLE_NAME_1
+ " WHERE " + MESSAGE_TYPE + " IN (?)"
+ " AND " + FROM_EMAIL + " LIKE (?) OR " + CC_EMAIL + " LIKE (?) OR "
+ SUBJECT + " LIKE (?) OR " + MESSAGE + " LIKE (?)"
+ " AND " + "DATETIME(" + DATE_RECEIVED + ") BETWEEN DATETIME(?) AND DATETIME(?)"
+ " ORDER BY " + _ID + " DESC";
String[] params = {received, search, search, search, search, twoDaysAgo, today};
cursor = database.rawQuery(query, params);
if (cursor != null) {
cursor.moveToFirst();
}
}
} catch (Exception e) {
e.printStackTrace();
}
return cursor;
}
值" twodaysago"one_answers"今天"的值也是格式的字符串,看起来与存储的字符串相同(date_received'(。获得所需功能的任何帮助将是一个很好的帮助。
编辑:我添加了一个额外的列" Timestamp",该列保存了时期值(例如1492337028(,以下功能很棒(只显示时间戳后的项目(:
String received = "Received";
String query = "SELECT * FROM " + TABLE_NAME_1
+ " WHERE " + MESSAGE_TYPE + " IN (?)"
+ " AND DATETIME(" + TIMESTAMP + ") >= DATETIME(?)"
+ " ORDER BY " + TIMESTAMP + " DESC";
String[] params = {received, twoDaysAgo};
cursor = database.rawQuery(query, params);
但是,当试图在时间戳之后搜索项目时,它也将项目返回时间戳之前,因此如何修改以下功能以仅在时间戳之后搜索项目?
之后String received = "Received";
search = "%" + search + "%";
String query = "SELECT * FROM " + TABLE_NAME_1
+ " WHERE " + MESSAGE_TYPE + " IN (?)"
+ " AND " + FROM_EMAIL + " LIKE (?) OR " + CC_EMAIL + " LIKE (?) OR "
+ SUBJECT + " LIKE (?) OR " + MESSAGE + " LIKE (?)"
+ " AND DATETIME(" + TIMESTAMP + ") >= DATETIME(?)"
+ " ORDER BY " + TIMESTAMP + " DESC";
String[] params = {received, search, search, search, search, twoDaysAgo};
cursor = database.rawQuery(query, params);
您有两个选择要做您要求的。
第一个选项:更改date_receed的数据类型为Integer,然后在MILLIS中将其初始化。对于Twodaysago和今天的参数,请做同样的事情;将它们转换为毫里的时间。现在您的查询应该起作用。
第二个选项:检查此问题。您可能需要为此更改时间戳格式,但是我不确定。