How to select data between two date range in android SQLite

Question

I have table which contains data along with created on date. I want to select data from table according to two given date ranges, or of one particular month.

I am not sure how to do that, as created on column is of type text and is stored as dd-MM-yyyy HH:mm:ss

I am using rawQuery() to fetch data.

Tyler Treat · Accepted Answer · 2012-03-10 20:12:19Z

16

You can do something like this:

 mDb.query(MY_TABLE, null, DATE_COL + " BETWEEN ? AND ?", new String[] {
                minDate + " 00:00:00", maxDate + " 23:59:59" }, null, null, null, null);

minDate and maxDate, which are date strings, make up your range. This query will get all rows from MY_TABLE which fall between this range.

edited Mar 10, 2012 at 20:12

answered Mar 10, 2012 at 20:06

Tyler Treat

15k16 gold badges84 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tyler Treat Over a year ago

mDb.rawQuery("select * from " + MY_TABLE + " where " + DATE_COL + " BETWEEN " + minDate + " 00:00:00 AND " + maxDate + " 23:59:59");

Adil Bhatty Over a year ago

I have date from 11th March to 14th March, but when I am using query, "select * from category where createdon BETWEEN '11-03-2012 00:00:00' AND '12-05-2012 23:59:59'", it should show me data of all dates, as my month is set to May, but its only showing me 11th and 12th March data :(

Adil Bhatty Over a year ago

I have done like this "select * from category where date(createdon) between date('2012-03-11') AND date('2013-01-01');" it seems like working :)

abidinberkay Over a year ago

what is DATE_COL and what is the format of minDate and maxDate ??

CoronaPintu Over a year ago

@TylerTreat u had store date like 2013-01-01 in this formate but in my case 2013/01/01 this formate so it's not working

Samir · Accepted Answer · 2017-06-11 16:45:24Z

Here is some usefull

 //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;
}

Collectives™ on Stack Overflow

How to select data between two date range in android SQLite

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related