1. Code
  2. Android SDK

Android Fundamentals: Database Dates and Sorting

Scroll to top
Read Time: 9 mins
This post is part of a series called Android Fundamentals.
Android Fundamentals: Status Bar Notifications
Android Fundamentals: IntentService Basics

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.

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

Update the schema to incorporate the new column.

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.

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:

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

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.

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:

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:

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

Android SDK Database Dates Figure 2Android SDK Database Dates Figure 2Android SDK Database Dates Figure 2

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:

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:

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

Android SDK Dates Figure 1Android SDK Dates Figure 1Android SDK Dates Figure 1


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, via their blog at, and on Twitter @androidwireless.

Need More Help Writing Android Apps? Check out our Latest Books and Resources!

Buy Android Wireless Application Development, 2nd Edition  Buy Sam's Teach Yourself Android Application Development in 24 Hours  Mamlambo code at Code Canyon

Did you find this post useful?
Want a weekly email summary?
Subscribe below and we’ll send you a weekly email summary of all new Code tutorials. Never miss out on learning about the next big thing.
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.