CODEWITHSUNDEEP
sqlitedatetimeandroid-activityandroid-sqlite
user3314337
user3314337

Reputation: 341

How to filter data from database based on YYYY-MM using Selection and SelectionArgs[] parameter in Android SQLite Query

I am storing YYYY-MM-DD and HH:MM:SS values in two separate columns in all my SQLite tables.

I have been using the following code to filter data by supplier id and date from my SQLite database.

    public double addPurchaseTotal(String supplierID, String date) {
    SQLiteDatabase db = helper.getReadableDatabase();
    double result = 0;
    String selection = VivzHelper.COLUMN_ADD_PURCHASE_SUPPLIER_ID + " =? "
            + " AND " + VivzHelper.COLUMN_ADD_PURCHASE_DATE + " =? ";
    String[] selectionArgs = {supplierID, date};
    Cursor c = db.query(VivzHelper.ADD_PURCHASE_TABLE,
            new String[]{"sum(" + VivzHelper.COLUMN_ADD_PURCHASE_ITEM_COST_PRICE + ")"},
            selection,
            selectionArgs,
            null,
            null,
            null);
    if (c.moveToFirst()) {
        result = c.getDouble(0);
    }
    c.close();
    return result;
}

The value for date parameter is obtained from a date picker. As mentioned earlier, date value under the VivzHelper.COLUMN_ADD_PURCHASE_DATE is stored in YYYY-MM-DD format. I would like to filter my data based on YYYY-MM (year and month alone). How can this be done?

Upvotes: 0

Views: 440

Answers (2)

CL.
CL.

Reputation: 180030

Instead of comparing the entire string with =, check for the prefix with LIKE or GLOB:

... WHERE Supplier = 'xxx' AND Date LIKE '2015-04-%'
... WHERE Supplier = 'xxx' AND Date GLOB '2015-04-*'

(GLOB works better together with indexing.)

In Java:

String selection = ...
        + " AND " + VivzHelper.COLUMN_ADD_PURCHASE_DATE + " GLOB ?";
String[] selectionArgs = { ..., date.substring(0, 8) + "*" };

Upvotes: 1

Abhi
Abhi

Reputation: 334

I guess you are looking for this: SQLite Date and Time Functions

Upvotes: 0

Related Questions