vendredi 8 mai 2015

Selecting dates 3 days before now on Android with SQLite

I have a table with events, and want to select all events which happen from three days ago. (The events do belong to categories which is a field in the Article table which has to description etc., but that works).

This is my code:

    public List<Event> findByCategory(long catId, int start, int count) throws Exception {
        String limit = "";
        if (count > 0) limit = start + "," + count;

        SQLiteQueryBuilder _QB = new SQLiteQueryBuilder();
        _QB.setTables("Event e INNER JOIN Article a ON e.articleId=a.id");

        String[] rows = _fields.keySet().toArray(new String[0]);
        for (int i = 0; i < rows.length; i++) {
            rows[i] = "e." + rows[i];
        }
        Cursor cursor = _QB.query(_db, rows, "date(e.startTime) > date('now','-3 days') AND EXISTS(SELECT * FROM CategoryArticleLink WHERE CategoryArticleLink.articleId = a.id AND EXISTS (SELECT * FROM Category WHERE Category.id = CategoryArticleLink.categoryId AND Category.id = '" + catId + "'))", null, null, null, "e.startTime ASC", limit);

        List<Event> list = new ArrayList<>(cursor.getCount());
        if (cursor.moveToFirst()) {
            do {
                Event item = getObject(cursor);
                list.add(item);
            } while (cursor.moveToNext());
        }
        cursor.close();
        return list;
    }

The resulting Query is:

SQLiteQuery: SELECT e.articleId, e.keynote, e.locationId, e.id, e.finishTime, e.startTime, e.languageCode, e.flag, e.lastUpdate FROM Event e INNER JOIN Article a ON e.articleId=a.id WHERE (date(e.startTime) > date('now','-3 days') AND EXISTS(SELECT * FROM CategoryArticleLink WHERE CategoryArticleLink.articleId = a.id AND EXISTS (SELECT * FROM Category WHERE Category.id = CategoryArticleLink.categoryId AND Category.id = '9'))) ORDER BY e.startTime ASC LIMIT 0,8

This returns an empty result set. However, if I take the date selection (date(e.startTime) > date('now','-3 days')) out of the query it returns all records as expected. So I must be doing something small wrong, but I just don't see what.

The field type on database generation is 'DATE', so that should be ok I expect.

Aucun commentaire:

Enregistrer un commentaire