Android Fundamentals: Database Dates and Sorting

Android uses SQLite technology for its local database. This works quite well. However, occasional quirks exist when compared to a fully featured relational database. One such quirk is that SQLite doesn't support any sort of date type. Luckily, it does support date functions and is capable of storing dates in numerous formats. This tutorial will provide you with a method for working with dates in the context of adding dates to the "TutList" application database and (finally) showing a list of tutorials, sorted by date.

This tutorial builds upon previous tutorials, including SQLite Crash Course for Android Developers and the continued series on our TutList activity with the most recent tutorial, Android Fundamentals: Working With Content Providers. If you have trouble keeping up, feel free to post questions in the comment section -- many folks read and respond, including ourselves. Also, don't forget about the Android SDK reference.
The final sample code that accompanies this tutorial is available for download as open-source from the Google code hosting.

Step 0: Getting Started

This tutorial assumes you will start where the previous tutorial in the series, Android Compatibility: List Indicators on Honeycomb, left off. You can download that code and work from there or you can download the code for this tutorial and follow along. Either way, get ready by downloading one or the other project and importing it into Eclipse.

Step 1: Updating the Database

In order to store and retrieve dates through the content provider, we'll need to update the application database to store dates associated with each content record. A change to the database means that you will need to update the database version, add the new column name as a constant, provide a new initial schema, and code an upgrade to the existing database. All of these changes affect the TutListDatabase class.

Begin by updating the database version to 3.

1
2	private static final int DB_VERSION = 3;

Next, add the new column for the date, defining that column name in Java.

1
2	public static final String COL_DATE = "tut_date";

Update the schema to incorporate the new column.


private static final String CREATE_TABLE_TUTORIALS = "CREATE TABLE "
        + TABLE_TUTORIALS + " (" + ID
        + " INTEGER PRIMARY KEY AUTOINCREMENT, " + COL_TITLE
        + " TEXT NOT NULL, " + COL_URL + " text UNIQUE NOT NULL, "
        + COL_DATE + " INTEGER NOT NULL DEFAULT (strftime('%s','now'))"
        + ");";

We've chosen to use an INTEGER type to store the date. We could have chosen REAL, to store Julian dates, or TEXT to store dates as strings. We use a SQLite expression, "strftime('%s', 'now'))" that inserts the current time, in the form of an integer, into the database. The value is in seconds and is defined as Unix time, seconds since the UTC start of 1970.

Finally, upgrade older schemas inside the onUpgrade() method.


@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion == 2 && newVersion == 3) {
this value is mid february 2011
        db.execSQL("alter table "+ TABLE_TUTORIALS + " add column " + COL_DATE + " INTEGER NOT NULL DEFAULT '1297728000' ");
    
    } else {
        Log.w(DEBUG_TAG, "Upgrading database. Existing contents will be lost. ["
                + oldVersion + "]->[" + newVersion + "]");
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_TUTORIALS);
        onCreate(db);
    }
}

Notice here that the altered schema varies from the original schema. SQLite does not allow expressions or current date values to be used as default values when altering a table. Using a constant value (we chose mid-February of this year) is the only option short of creating a new table and copying data across -- an expensive operation when there is a large amount of data. See the SQLite documentation on ALTER TABLE for more information about the restrictions with the ADD COLUMN operation. Keep these restrictions in mind when designing your application database schema and planning for changes or upgrades at a later time.

This completes the database changes necessary for supporting the article date data. This will allow updating older apps without losing any data already downloaded. Although this may not seem useful on this app, doing this correctly and getting used to doing it will save trouble later. Plus, your users will be happier.

Step 2: Updating the Content Provider

When updating the application database, you'll want to review your content provider to see what changes should be made to bring the two into parity. In this case, there are no changes necessary. We've simply added a new column for the date, which need not be reflected in any URI types or other query modifications at the content provider.

Easy step, huh? It's not always so easy, as you'll see next time we go through this exercise.

Step 3: Updating the Parser

Within the TutListDownloaderService class, we use a Pull Parser to grab data out of the XML feed and insert it into the database. We need to get the date from the feed, transform it to the appropriate format for the database, and then add it as part of each database record.

Within the xmlParse() method, add the following check in the chain of checks for the START_TAG event:


if (tutorials.getName().equals("pubDate")) {
    tutorials.next();
    DateFormat parser = new SimpleDateFormat("E, dd MMM yyyy");
    try {
        Date date = parser.parse(tutorials.getText());
        tutorialData.put(TutListDatabase.COL_DATE, 
                date.getTime() / 1000);
    } catch (ParseException e) {
        Log.e(DEBUG_TAG, "Error parsing date: " 
                + tutorials.getText());
    }
}

Here's an example of what the date looks like in XML:

1
2	<pubDate>Fri, 20 May 2011 11:30:23 +0000</pubDate>

Since we are only interested in the date, we parse this using the SimpleDateFormat class. Once the parse() method has been called, we have a standard Date object. Since a Date object holds values as milliseconds rather than seconds, we then divide by 1000 before storing the result into the database.

Step 4: Modifying the ListView Layout

The date needs to be displayed in the ListView along with the title of each article. Currently, the list_item.xml layout resource contains a single TextView. Now, modify this layout to use a LinearLayout (vertically oriented) to place the title above the date. Here's a listing of the updated list_item.xml file.


<?xml version="1.0" encoding="utf-8"?>
<LinearLayout
    xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:orientation="vertical">
    <TextView
        android:id="@+id/title"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:textSize="24dp"
        android:padding="6dp" />
    <TextView
        android:id="@+id/date"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:textSize="18dp"
        android:padding="4dp"
        android:gravity="right" />
</LinearLayout>

Don’t forget to make this change to the alternative list_item.xml found in the /layout-v11 directory as well.

Step 5: Updating the ListView Fragment

Within the TutListFragment class, several changes must be made to add sorting support to the ListView control. The Cursor projection must be updated to include the date column. The binding values for the adapter must be updated to add the date data as well, and the new TextView object that will display the date must be configured. Finally, a custom ViewBinder class must be added, otherwise displayed dates won't look right.

Begin by updating the Cursor projection and add the sorting clause, within the onCreateLoader() method, like this:


@Override
public Loader<Cursor> onCreateLoader(int id, Bundle args) {
    String[] projection = { TutListDatabase.ID, TutListDatabase.COL_TITLE,
            TutListDatabase.COL_DATE };

    Uri content = TutListProvider.CONTENT_URI;
    CursorLoader cursorLoader = new CursorLoader(getActivity(),
           content , projection, null, null, TutListDatabase.COL_DATE+" desc");
    return cursorLoader;
}

A descending date order means the newest items will appear at the top.

Next, modify the binding variables (now found at the class level) that are used by the SimpleCursorAdapter class, like this:

1
2	private static final String[] UI_BINDING_FROM = { TutListDatabase.COL_TITLE,
3	TutListDatabase.COL_DATE };
4	private static final int[] UI_BINDING_TO = { R.id.title, R.id.date };

At this point, the code should work. However, the result will look like this:

The raw date information, although sorted correctly, isn't very useful to most users. The solution is to transform the raw value back to a displayed date using the user's locale. To do this, we can implement a ViewBinder object.

Step 6: Implementing a ViewBinder

Since we're using a SimpleCursorAdapter object, we'll implement a custom SimpleCursorAdapter.ViewBinder class. The ViewBinder class allows for custom mapping of a column to its View object as identified through the supplied binder arrays. This means we can change what happens during the binding of the date column to the TextView object without interfering with any of the other bindings. Here is a sample implementation of a custom ViewBinder that performs this:


private class TutorialViewBinder implements SimpleCursorAdapter.ViewBinder {

    @Override
    public boolean setViewValue(View view, Cursor cursor, int index) {
        if (index == cursor.getColumnIndex(TutListDatabase.COL_DATE)) {
            // get a locale based string for the date
            DateFormat formatter = android.text.format.DateFormat
                    .getDateFormat(getActivity().getApplicationContext());
            long date = cursor.getLong(index);
            Date dateObj = new Date(date * 1000);
            ((TextView) view).setText(formatter.format(dateObj));
            return true;
        } else {
            return false;
        }
    }
}

The incoming parameters to the single method we need to implement, setViewValue, are the View object from the UI_BINDING_TO array that we provided, the Cursor object the adapter is working with, and the index of the data found within the Cursor.

We use the index and the Cursor object to determine if the value being bound is the date. If not, false is returned to indicate that the default binding should take place. If so, we proceed with reformatting the date. Using the android.text.format.DateFormat class allows us to retrieve a locale-specific DateFormat object. Since the value stored in the database is in seconds, we must convert that to milliseconds. Then we can call setText() on the view to write the resulting string.

Finally, this ViewBinder class must be assigned to the adapter. Just after the adapter is initialized, in the onCreate() method, call the setViewBinder() method:

1
2	adapter.setViewBinder(new TutorialViewBinder());

Now when you run the application, the date shows up in the format specific to the user’s locale (or the date format you set in the Settings).

Conclusion

In this tutorial, you have learned how to work with dates in Android when reading them from XML feeds, and reading and writing them to SQLite databases. In addition, you learned how to make custom changes to individual values displayed within a ListView. In the context of TutList, you've modified the application to store the dates as parsed from the XML feed of tutorials and use those dates to show the most recent tutorials to the user in order of publication.

As always, we look forward to your feedback!

About the Authors

Mobile developers Lauren Darcey and Shane Conder have coauthored several books on Android development: an in-depth programming book entitled Android Wireless Application Development and Sams Teach Yourself Android Application Development in 24 Hours. When not writing, they spend their time developing mobile software at their company and providing consulting services. They can be reached at via email to androidwirelessdev+mt@gmail.com, via their blog at androidbook.blogspot.com, and on Twitter @androidwireless.