I've started contributing to the Android Cookbook, a brand new project of O'Reilly which is being run by my friend, Ian Darwin. Since you retain ownership of the material that you submit and simply license it to O'Reilly under the Creative Commons cc-by license, you have every reason to contribute something you may have already written–and no excuse not to!

Here is the first recipe I submitted:

Problem

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.

The Code

Without further ado, here's how to do it.

First, create a table that defines a column of type timestamp.

CREATE TABLE current_list (
    item_id INTEGER NOT NULL,
    added_on TIMESTAMP NOT NULL DEFAULT current_timestamp,
    added_by VARCHAR(50) NOT NULL,
    quantity INTEGER NOT NULL,
    units VARCHAR(50) NOT NULL,
    CONSTRAINT current_list_pk PRIMARY KEY (item_id)
);

Note the default value for the added_on column. Whenever you insert a row into this table, SQLite will automatically fill in the current time (accurate to the second) for the new record.

sqlite> insert into current_list (item_id, added_by, quantity, units)
   ...> values (1, 'fuerth', 1, 'EA');
sqlite> select * from current_list where item_id = 1;
1|2010-05-14 23:10:26|fuerth|1|EA
sqlite>

See how the current date was inserted automatically? This is one of the advantages you get from working with SQLite timestamps.

How about the other advantages?

Select just the date part, forcing the time back to midnight:

sqlite> select item_id, date(added_on,'start of day')
     ...> from current_list where item_id = 1;
1|2010-05-14
sqlite>

Or adjust the date to the Monday of the following week:

sqlite> select item_id, date(added_on,'weekday 1')
     ...> from current_list where item_id = 1;
1|2010-05-17
sqlite>

Or the Monday before:

sqlite> select item_id, date(added_on,'weekday 1','-7 days')
   ...> from current_list where item_id = 1;
1|2010-05-10
sqlite>

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:

 Cursor cursor = database.rawQuery(
  "SELECT item_id AS _id," +
  " (strftime('%s', added_on) * 1000) AS added_on," +
  " added_by, quantity, units" +
  " FROM current_list", new String[0]);
 long millis = cursor.getLong(cursor.getColumnIndexOrThrow("added_on"));
 Date addedOn = new Date(millis);

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.

See Also

SQLite's documentation for its date and time functions
This recipe in the Android Cookbook