Get current date data from Content Provider

Question

I have to get data from ContentProvider based on current month. For getting the data, I used following query:

SELECT * FROM Festivals
WHERE strftime('%Y-%m-%d',fest_date) >= date('now','-6 days') AND
strftime('%Y-%m-%d',fest_date)<=date('now') order by fest_date

I tried to write the same in the CursorLoader as follows:

new CursorLoader(this, FestivalContract.Festivals.CONTENT_URI, 
            PROJECTION, "strftime('%Y-%m-%d',fest_date)>=? AND strftime('%Y-%m-%d',fest_date)<=?", new String[] { "date('now','-6 days')", "date('now')" },
            FestivalContract.Festivals.FestivalColumns.FEST_NAME + " asc");

But, it returned all records. I am unable to find what is wrong in my code.

Answer 1

Probebly you query method in content provider is something like this:

SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
        // check if the caller has requested a column which does not exists
        checkColumns(projection);
        // Set the table
        queryBuilder.setTables(MessageTable.TABLE_NAME);
        int uriType = uriMatcher.match(uri);
        switch (uriType) {
            case MESSAGES:
                break;

            case MESSAGE_ID:
                // adding the ID to the original query
                queryBuilder.appendWhere(MessageTable.COLUMN_ID + "=" + uri.getLastPathSegment());
                break;

            case BROADCAST_MESSAGES:
                groupBy = MessageTable.COLUMN_MESSAGE_ID;
                break;

            default:
                throw new IllegalArgumentException("Unknow URI: " + uri);

And if you are using queryBuilder cannot achieve your goal. You have two workarounds:

1- Store you date in database in a long format. for example your festival date is unix formated date which means you should store each date as long number in your database. then write you query in cursorloader like this:

new CursorLoader(this, FestivalContract.Festivals.CONTENT_URI, 
        PROJECTION, "fest_date>=? AND fest_date<=?", new String[] { System.currentTimeMillis(), System.currentTimeMillis() - 6 * 24 * 3600 * 1000)", "" },
        FestivalContract.Festivals.FestivalColumns.FEST_NAME + " asc");

2- The second solution is to customize your ContentProvider and add a different Content_Uri for this special query that you want to make, and then in query method of your provider check if you are querying this special uri, then instead of using QueryBuilder which does not let you run sql method, just execute your query string on database object that you have in content provider, something like this:

return database.rawQuery(your_query_string_with_sql_methods, new String['your_params_or_whatever]);