Editing cursor data before ListAdapter displays it in the ListView

Question

I first retrieve information from the SQLite database into a cursor.

The cursor contains a timestamp column in the format: yyyy-MM-dd HH:mm:ss.SSS, for example, 2010-08-27 21:25:30.575

Once retrieved, I set aSimpleCursorAdapter like so (code simplified):

SimpleCursorAdapter adapter = 
            new SimpleCursorAdapter(this, R.layout.row_layout, cursor, new String{KEY_TITLE, KEY_TIMESTAMP}, new int[]{ R.id.title, R.id.timestamp } );
    setListAdapter(adapter);

The code successfully displays the time and date in the format described above, but I would like to display just the date in DD/MM/YYYY format (or perhaps the user's format based on locale if possible).

How can I intervene with the SimpleCursorAdapter to change the date to my preferred format?

N.B. I would be willing to change the way information is passed to the adapter if it simplifies matters. I also wouldn't mind changing the SQLite query for the cursor if SQLite has a built-in formatter.

Answer 1

The query way:

SELECT strftime('%d/%m/%Y',TimeStampField) FROM MyTable...

Because SQLite will return strings for all fields, what you would need to do for your SimpleCursorAdaptor is parse the date and then transform it. I'm tempted to say it's simpler and cleaner to have it done by SQLite in the first place.

Answer 2

This was my final solution.

Please note that this is a hack that gets SQLite to reformat the date on retrieval. A better solution would be to store the date and retrieve it on its own.

Have fields for retrieval keys:

public static final String KEY_TIMESTAMP = "timestamp";
public static final String KEY_DATESTAMP = 
              "strftime('%d/%m/%Y'," + KEY_TIMESTAMP +")";

Store the timestamp like normal in the database entry:

String timeStamp = new Timestamp( 
                Calendar.getInstance().getTimeInMillis() ).toString();

ContentValues initialValues = new ContentValues();
...
initialValues.put(KEY_TIMESTAMP, timeStamp);

return mDb.insert(DATABASE_TABLE, null, initialValues);

Where mDb is an SQLiteDatabase object.

When retrieving the timestamp in yyyy-MM-dd HH:mm:ss.SSS format, use the regular KEY_TIMESTAMP.

The following method retrieves a row with both items in there. Use the appropriate key for retrieval.

public Cursor fetchItem(long rowId) throws SQLException {
        Cursor cursor =
                mDb.query(true, DATABASE_TABLE, 
                        new String[] {KEY_ITEMID,KEY_TIMESTAMP,KEY_DATESTAMP},
                        KEY_ITEMID + "=" + rowId, 
                        null, null, null, null, null
                );
        return cursor;
}

Use the appropriate key for retrieval

mTimestamp = quote.getString(
                quote.getColumnIndex(QuotesDbAdapter.KEY_TIMESTAMP)));
mDatestamp = quote.getString(
                    quote.getColumnIndex(QuotesDbAdapter.KEY_DATESTAMP)));