Android's embedded SQLite3 database supports date and time data directly, including some useful date and time arithmetic. However, getting these dates out of the database is troublesome: there is no Cursor.getDate() in the Android API.

Solution

Use SQLite's strftime() function to convert between SQLite timestamp format and the Java API's "milliseconds since the epoch" representation.

Discussion

This recipe demonstrates the advantages of using SQLite timestamps over storing raw milliseconds values in your database, and shows how to retrieve those timestamps from your database as java.util.Date objects.

Background

The usual representation for an absolute timestamp in Unix is time_t, which historically was just an alias for a 32-bit integer. This integer represented the date as the number of seconds elapsed since UTC 00:00 on January 1, 1970 (the Unix time epoch.) On systems where time_t is still a 32-bit integer, the clock will roll over partway through the year 2038.

Java adopted a similar convention, but with a few twists. The epoch remains the same, but the count is always stored in a 64-bit signed integer (the native Java long type) and the units are milliseconds rather than seconds. This method of timekeeping will not roll over for another 292 million years.

Android example code that deals with persisting dates and times tends to simply store and retrieve the raw milliseconds since the epoch values in the database. However, by doing this, it misses out on some useful features built in to SQLite.

The Advantages

There are several advantages to storing proper SQLite timestamps in your data: you can default timestamp columns to the current time using no Java code at all; you can perform calendar-sensitive arithmetic such as selecting the first day of a week or month, or adding a week to the value stored in the database; and you can extract just the date or time components and return those from your data provider.

All of these code-saving advantages come with two added bonuses: first, your data provider's API can stick to the Android convention of passing timestamps around as long values; second, all of this date manipulation is done in the natively-compiled SQLite code, so the manipulations don't incur the garbage collection overhead of creating multiple java.util.Date or java.util.Calendar objects.

These examples are just the tip of the iceberg. You can do a lot of useful things with your timestamps once SQLite recognizes them as such.

Last, but not least, you must be wondering how to get these dates back into your Java code. The trick is to press another of SQLite's date functions into service—this time strftime(). Here is a Java method that fetches a row from the current_list table we've been working with:

That's it: using strftime's %s format, you can select timestamps directly into your Cursor as Java milliseconds since the epoch values. Client code will be none the wiser, except that your content provider will be able to do date manipulations for free that would take significant amounts of Java code and extra object allocations.