Logo Icon Logo
A Crowd-sourced Cookbook on Writing Great Android® Apps
GitHub logo Twitter logo OReilly Book Cover Art

Working with Dates in SQLite

Author: Jonathan Fuerth
Published? true
FormatLanguage: WikiFormat

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 (We show this using the command-line SQLite program running on a desktop, but the previous recipes have shown how to create a database under Android)

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

eusebia 2016-05-16 08:19:20.067 My business partners were needing CA SI-100 recently and learned about a web service with a ton of fillable forms . If you are searching for CA SI-100 too , here's <a href="http://pdf.ac/2pEhTM" >http://pdf.ac/2pEhTM</a>
AndyD 2011-11-07 09:57:58.604 there's a critical omission in the code above! For this to work, you must include the 'unixepoch' modifier with strftime(). I've been trying to use this recipe with no success, and beating my head against the wall for a while, until discovering the solution. Thus, the rawQuery sample code near the end of the recipe should be: Cursor cursor = database.rawQuery( "SELECT item_id AS _id," + " (strftime('%s', added_on, 'unixepoch') * 1000) AS added_on," + " added_by, quantity, units" + " FROM current_list", new String[0]);