我有一个包含创建日期的数据表。我想根据给定的两个日期范围或一个特定月份选择表中的数据。
由于“创建日期”列的类型为文本,并且存储为dd-MM-yyyy HH:mm:ss,因此我不确定如何做到这一点。
我正在使用rawQuery()来获取数据。
mDb.query(MY_TABLE, null, DATE_COL + " BETWEEN ? AND ?", new String[] {
minDate + " 00:00:00", maxDate + " 23:59:59" }, null, null, null, null);
minDate
和 maxDate
是日期字符串,它们组成了你的日期范围。这个查询将获取所有在此范围内的行从MY_TABLE
中。
这里有一些有用的内容
//Get Trips Between dates
public List<ModelGps> gelAllTripsBetweenGivenDates(String dateOne, String dateTwo) {
List<ModelGps> gpses = new ArrayList<>();
SQLiteDatabase database = dbHelper.getReadableDatabase();
final String columNames[] = {DBHelper.COLUMN_ID, DBHelper.COLUMN_NAME, DBHelper.COLUMN_LATITUTE, DBHelper.COLUMN_LONGITUDE, DBHelper.COLUMN_ALTITUDE, DBHelper.COLUMN_DATE, DBHelper.COLUMN_TYPE, DBHelper.COLUMN_TRAVEL, DBHelper.COLUMN_SPEED};
String whereClause = DBHelper.COLUMN_TYPE + " = ? AND " + DBHelper.COLUMN_DATE + " BETWEEN " + dateOne + " AND " + dateTwo;
String[] whereArgs = {"Trip"};
Cursor cursor = database.query(DBHelper.TABLE_NAME_GPS, columNames, whereClause, whereArgs, null, null, DBHelper.COLUMN_NAME + " ASC");
while (cursor.moveToNext()) {
ModelGps gps = new ModelGps();
gps.setId(cursor.getLong(cursor.getColumnIndex(DBHelper.COLUMN_ID)));
gps.setName(cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_NAME)));
gps.setLatitude(cursor.getDouble(cursor.getColumnIndex(DBHelper.COLUMN_LATITUTE)));
gps.setLongitude(cursor.getDouble(cursor.getColumnIndex(DBHelper.COLUMN_LONGITUDE)));
gps.setAltitude(cursor.getDouble(cursor.getColumnIndex(DBHelper.COLUMN_ALTITUDE)));
gps.setDate(cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_DATE)));
gps.setType(cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_TYPE)));
gps.setTravel(cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_TRAVEL)));
gps.setSpeed(cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_SPEED)));
gpses.add(gps);
}
database.close();
cursor.close();
return gpses;
}
mDb.rawQuery("select * from " + MY_TABLE + " where " + DATE_COL + " BETWEEN " + minDate + " 00:00:00 AND " + maxDate + " 23:59:59");
- Tyler Treat