Date, Time, SQLite and Android

This post summarizes my recent experience (problem, finally solved) with storing and then reading and displaying the date and time in Android application.

Storing the current time/date in SQLite
The most common way is to do:

database.execSQL("update TABLE_NAME set COLUMN_NAME = datetime('now') where ...");

As a result, SQLite stores a string representing the current time in UTC (GMT), using the ISO8601 date/time format. This format (YYYY-MM-DD HH:MM:SS), is by the way suitable for date/time comparisons. The fact that the value stored is in UTC and not in a local time zone, is actually nice. More about it below.

Retrieving a time/date and displaying it
To retrieve the value, follow the recommended Android practice:

Cursor row = databaseHelper.query(true, TABLE_NAME, new String[] {
COLUMN_INDEX}, ID_COLUMN_INDEX + "=" + rowId,
null, null, null, null, null);
String dateTime = row.getString(row.getColumnIndexOrThrow(COLUMN_INDEX));

This, returns a string, parse it and reformat to your local format and time zone:

DateFormat iso8601Format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try {
date = iso8601Format.parse(dateTime);
} catch (ParseException e) {
Log.e(TAG, "Parsing ISO8601 datetime failed", e);
}

long when = date.getTime();
int flags = 0;
flags |= android.text.format.DateUtils.FORMAT_SHOW_TIME;
flags |= android.text.format.DateUtils.FORMAT_SHOW_DATE;
flags |= android.text.format.DateUtils.FORMAT_ABBREV_MONTH;
flags |= android.text.format.DateUtils.FORMAT_SHOW_YEAR;

String finalDateTime = android.text.format.DateUtils.formatDateTime(context,
when + TimeZone.getDefault().getOffset(when), flags);

Note the when + TimeZone.getDefault().getOffset(when) above. This does the trick to convert the UTC time/date to local time. The DateUtils.formatDateTime() seem to be supposed to do this, but it doesn't, or I wasn't able to find a way to achieve it.

And that's it.