Using Dates with Android Lists and SQLite

Our TeamCaptain app checks the web for changes to the schedule and news for recreational sports leagues, providing update notifications and local (cached) viewing on mobile devices. On Android, this data is stored in a SQLite database. As the SQLite page on Datatypes In SQLite Version 3 states, there isn’t a specific storage class for these:

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

  • TEXT as ISO8601 strings (“YYYY-MM-DD HH:MM:SS.SSS”).
  • REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
  • INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

I chose to store date values as an integer, but when Binding to Data with AdapterView using an Android SimpleCursorAdapter, the result isn’t so useful to the user:

TeamCaptain original Date view

A common solution for this seems to be to create a custom CursorAdapter like the example in this answer found on StackOverflow. This works, but I decided to choose a different that I think is more reusable: creating a custom ViewBinder for binding date fields to an Android TextView.

public class DateViewBinder implements ViewBinder {
	private SimpleDateFormat mFormat;
	private int mTargetViewId;

	public DateViewBinder(SimpleDateFormat format, int targetViewId) {
		mFormat = format;
		mTargetViewId = targetViewId;
	}

	public void setBinding(int from) {
		mTargetViewId = from;
	}

	public void setFormat(SimpleDateFormat format) {
		mFormat = format;
	}

	@Override
	public boolean setViewValue(View view, Cursor cursor, int columnIndex) {
		final int id = view.getId();
		if (id == mTargetViewId) {
			final String value = getLongFieldAsString(cursor, columnIndex);
			if (view instanceof TextView)
				setViewText((TextView)view, value);
			else
				throw new IllegalStateException(view.getClass().getName() + " is not a view that can be bound by this view binder (" +
						DateViewBinder.class.getSimpleName() + ")");
			return true;
		}

		return false;
	}

	private String getLongFieldAsString(Cursor cursor, int columnIndex) {
		final long data = cursor.getLong(columnIndex);
		if (data == 0)
			return "";
		else {
			final Date date = new Date(data);
			return mFormat.format(date);
		}
	}

	private void setViewText(TextView view, String value) {
		view.setText(value);
	}
}

And to use it in the code:

SimpleCursorAdapter adapter = new SimpleCursorAdapter(this, R.layout.schedule_entry, cursor, dataColumns, viewIDs);
/** A date format.  Example: 1:30 pm on Wednesday, Jan. 2 **/
final SimpleDateFormat format = new SimpleDateFormat("hh:mm a 'on' EEEE, MMM'.' d", Locale.ENGLISH);
final DateViewBinder binder = new DateViewBinder(format, R.id.dateTime);
adapter.setViewBinder(binder);

Now we’ve got something much more understandable for our users:

Date view in TeamCaptain using the custom ViewBinder

This can of course be further expanded to take in an array/list of view IDs for when more than one date and/or time field is going to be shown using a single adapter. Hope this helps!

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>