Way to group sqlite cursor results by formated date column

Question

Is there a simpler way to group and present data from an SQLite database than what I'm about to propose? If so, how would you improve it?

In an app I'm doing for a gym, I need to be able to record the history for 5 types of exercise classes, by date, in descending order (newest on top). In the spirit of sharing code to better understand, here is my table creation:

private static final String CREATE_TABLE_BJJ = "CREATE TABLE IF NOT EXISTS "
    // TODO finish this
    + TABLE_BJJ + "(" + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
    + BJJ_HISTORY_MOVE + " TEXT NOT NULL, " + BJJ_HISTORY_MOVECOUNT + " INTEGER NOT NULL, "
    + BJJ_HISTORY_PERFORMEDBY + " TEXT NOT NULL, " + BJJ_HISTORY_PERFORMEDTO
    + " TEXT NOT NULL, " + DATE + " datetime NOT NULL, " + UPLOADED
    + " INTEGER NOT NULL DEFAULT 0);";

this results in something like this once data gets recorded: tablerow

for the history page though, I need to somehow group all moves performed by date into one row with a sum of the number of moves performed, as in the screenshot below:

sample screen

What I was thinking might work would be to populate an array with the results of a SELECT DISTINCT STATEMENT then filling an array with the results like so:

String query ="SELECT DISTINCT Date FROM BJJHistory";
ArrayList<String> dateList = new ArrayList<String>();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Cursor  cursor = database.rawQuery(query,null);
if (cursor != null) {
    cursor.moveToFirst();
    if (!cursor.isAfterLast())
    {
         do
         {
              String datestr = sdf.format(new Date(cursor.getString(0))); 
              dateList.add(datestr);
         }
         while (cursor.moveToNext());
    }

}
return cursor;

I have a custom class to hold the results:

Public class History {
public String numMoves;
public String date;

public History() {
    super();
}

public History(final String numMoves, final String date) {
    super();
    this.numMoves = numMoves;
    this.date = date;
    }
}

Afterward, using the array to then create cursors to pull data back as such:

final ArrayList<History> BJJHistory = new ArrayList<History>();
for (int i = 0; dateList.size(); i++) {
    final Cursor cursor = db.rawQuery("SELECT COUNT(*) FROM BJJHistory WHERE Date LIKE '" + dateList(i).toString() + "%'") > 0, null); 
    if (cursor != null) {
        cursor.moveToFirst();

        BJJHistory.add(new History(cursor.getString(0).toString(),dateList[i].toString())) 
    }

lot of typing

Finally, using an array adapter to fill the listview with the resulting arraylist of history items. I would still need to use a way to implement an onclick listener that would query the database for all the items for each date, so the user could see what moves they did that date, but the main listview is more important right now.

As always, Thanks in advance!

score 1 · Answer 1 · answered Oct 09 '12 at 05:27

1

The GROUP BY clause does what you want; the date function also helps:

SELECT COUNT(*), date("Date") FROM BJHistory GROUP BY date("Date")

answered Oct 09 '12 at 05:27

CL.

173,858
17
217
259

Thanks, But that doesn't end up grouping my results into an array to feed into a listview. – Evan R. Oct 09 '12 at 18:51

score 0 · Answer 2 · edited Mar 17 '13 at 04:50

I ended up getting it to work with a slightly modified version of what I posted above. For anyone interested, here's my code (which could probably be improved upon):

First, I get all the unique dates from the table:

public String[] getUniqueDates(final String tablename) {
    final String query = "SELECT DISTINCT Date FROM " + tablename
            + " ORDER BY Date DESC";

    final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    final SimpleDateFormat sourceFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
    final Cursor cursor = database.rawQuery(query, null);
    String datestr = null;

    final Set<String> set = new LinkedHashSet<String>();
    if (cursor != null) {
        cursor.moveToFirst();
        if (!cursor.isAfterLast()) {
            do {
                // Convert the date to YYYY-MM-DD
                try {
                    final Date dt = sourceFormat.parse(cursor.getString(0));
                    datestr = sdf.format(dt);
                } catch (final ParseException e) {
                    e.printStackTrace();
                }
                set.add(datestr);
            } while (cursor.moveToNext());
        }
        cursor.close();
    }

    return set.toArray(new String[0]);
}

Then I have a class in my Database Access class that selects the number of dates matching the date:

public String selectCount(final String date) {
    String count = "";
    final Cursor cursor = database.rawQuery("SELECT COUNT(*) FROM "
            + SQLiteHelper.TABLE_BJJ + " WHERE Date LIKE '" + date
            + "%'", null);
    if (cursor != null) {
        cursor.moveToFirst();
        count = cursor.getString(0);
        cursor.close();
    }

    return count;
}

The meat and potatoes that puts the data into the ListView:

String[] DateArray = datasource.getUniqueDates(SQLiteHelper.TABLE_BJJ);
final History BJJHistoryArray[] = new History[DateArray.length];
for (int i = 0; i < DateArray.length; i++) {
    String suffix;
    if (datasource.selectCount(DateArray[i]).equals("1"))
        suffix = " Move";
    else
        suffix = " Moves";
    BJJHistoryArray[i] = new History(datasource.selectCount(DateArray[i]) + suffix,
            DateArray[i]);
}

final HistoryAdapter adapter = new HistoryAdapter(this, R.layout.history_row,
        BJJHistoryArray);
BJJHistory.setAdapter(adapter);

This results in the following:

screenshot

When a new entry is logged with the log entry button, it refreshes the query, thus changing the number up by 1 each time.

Things to improve:

Add a parameter for the selectCount() method, so that it can be used on any table.
Try to optimize cursors, if possible.
Optimize code, if possible.

Way to group sqlite cursor results by formated date column

2 Answers2