Tag Archives: java

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;

	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);
				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) {

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);

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!

Implementing bulkInsert for a custom Android ContentProvider

One of our apps implements a custom ContentProvider for managing the data it uses. The insert method gets a writable instance of the database, matches against the Uri, makes sure the appropriate values are included, inserts the entry and returns the Uri of the new entry. This is fine for when the app finds a new entry or two to add, but this seemed a little wasteful for the first update (when a user first subscribes, all of the current existing entries are pulled and added at once). With a little searching I found a related StackOverflow post, where user jcwenger explains it nicely:

However, since each Operation in the Batch can have a different URI and so on, there’s a huge amount of overhead. “Oh, a new operation! I wonder what table it goes in… Here, I’ll insert a single row… Oh, a new operation! I wonder what table it goes in…” ad infinitium. Since most of the work of turning URIs into tables involves lots of string comparisons, it’s obviously very slow.

By contrast, bulkInsert applies a whole pile of values to the same table. It goes, “Bulk insert… find the table, okay, insert! insert! insert! insert! insert!” Much faster.

It will, of course, require your ContentResolver to implement bulkInsert efficiently. Most do, unless you wrote it yourself, in which case it will take a bit of coding.

But wait, requires your ContentResolver to implement bulkInsert efficiently? How does one do that? Sure enough, checking the source code and comments of the abstract ContentProvider class for the bulkInsert method verifies that we have to implement bulkInsert ourselves.

By default, SQLite creates a journal file for each transaction (which is deleted once the transaction completes). For the bulk insert, we should specify a single transaction. A single Uri is passed in, so we should also only check that once. Here is what my bulkInsert logic looks like:

public int bulkInsert(Uri uri, ContentValues[] allValues) {
	SQLiteDatabase db = mHelper.getWritableDatabase();
	switch (URI_MATCHER.match(uri)) {
		return insertEvents(db, allValues);
		return insertNewsPosts(db, allValues);
	// ...
		throw new IllegalArgumentException("Unknown URI " + uri);

private int insertEvents(SQLiteDatabase db, ContentValues[] allValues) {
	int rowsAdded = 0;
	int rowId;
	ContentValues values;
	try {
		for (ContentValues initialValues : allValues) {
			values = initialValues == null ? new ContentValues() : new ContentValues(initialValues);
			rowId = insertEvent(db, values);
			if (rowId > 0)
	} catch (SQLException ex) {
		Log.e(LOG_TAG, "There was a problem with the bulk insert: " + StringUtils.exceptionToString(ex));
	} finally {
	return rowsAdded;

private int insertEvent(SQLiteDatabase db, ContentValues values) {
	if (!values.containsKey(TeamCaptainData.EventColumns.ORGANIZER))
		throw new IllegalArgumentException("Missing event column '" + TeamCaptainData.EventColumns.ORGANIZER + "'");
	// ...do some processing (check for more missing fields, set default values, etc.)
	return db.insert(TABLE_EVENTS, null, values);

Note that in this particular application, most bulkInsert calls will be outside of the SQLiteOpenHelper callback methods (onCreate, onUpgrade and onOpen); SQLite creates a transaction before invoking these methods, so when working within them it’s unnecessary to add explicit transaction control. Also, only one of the data columns can be empty (and empty is not the norm). A large batch of insertions is also usually only necessary when the user first subscribes to a particular organizer, which is not expected to happen often. If you’re looking at a different scenario or are just curious about possibly optimizing your bulkInsert implementation further, you might want to check out the DatabaseUtils.InsertHelper class. Out Of What Box? has a blog post, Android: Using DatabaseUtils.InsertHelper for faster insertions into SQLite database, that covers this in more detail.