Date and Times in SQLite
SQLite, the embedded database library in the Android platform, does not have a native date and time storage type (see SQLite docs), and instead suggests the use of TEXT, REAL or INTEGER data types. The library also has functions for working with dates and times, but I prefer to do my date and time calculations in Java, so these offerings were not of much use to me and did not figure into my decision about how to store this type of data.Text or Numbers?
So, what data type to use? Text strings or number values? Text strings are convenient for display, but they are a pain when it comes time to do calculations (is this date more recent than that date?). In addition, if I ever want to do localized date display (European Day, Month Year format versus US Month / Day / Year), having dates preformatted for one locale means quite a bit of work to transform it into other locales. So, text strings get tossed out as a storage type!That leaves storing dates and times as numbers. The big question there, is how precise do these dates and times need be? The SQLite INTEGER type can handle values up to 8 bytes (64 bits) in size. Using Java’s long value for time in milliseconds from January 1, 1970 might be convenient. However, I tossed out this idea because of concerns about the size of the numbers generated by this method and the fact that they are completely unreadable as dates without programming assistance. My application only needed precision down to seconds and I wanted the numbers to be as human-readable as possible without actually being formatted strings.
What’s Your Number?
With all this in mind, I decided to define a date and time number format that was closely related to printed dates and times, but that could be easily sorted into more recent and less recent. The format I defined starts with a numbered year, then month, then day of month, hours in 24 hour-format, minutes and then seconds.Example Date/Time: 10/29/2007, 12:06am, 21 seconds
Date/Time Number: 20071029000621
Date/Time Number: 20071029000621
Dates and times stored in this format can be interpreted by humans with a little instruction and the values will sort nicely, with higher values being the most recent.
Note: If you need to do length calculations in a specific unit (how many days between date X and date Y) this format isn’t much help, but unless you save your date and time values in that specific unit (e.g., days) doing those types of calculations is always going to be a bit of a pain.
The Implementation
With this design in mind, I created the following functions to help me convert between the Calendar objects and the long values I was going to retrieve out of my SQLite database: public static final String DATE_FORMAT = "yyyyMMddHHmmss";
private static final SimpleDateFormat dateFormat = new
SimpleDateFormat(DATE_FORMAT);
public static long formatDateAsLong(Calendar cal){
return Long.parseLong(dateFormat.format(cal.getTime()));
}
public static Calendar getCalendarFromFormattedLong(long l){
try {
Calendar c = Calendar.getInstance();
c.setTime(dateFormat.parse(String.valueOf(l)));
return c;
} catch (ParseException e) {
return null;
}
}
private static final SimpleDateFormat dateFormat = new
SimpleDateFormat(DATE_FORMAT);
public static long formatDateAsLong(Calendar cal){
return Long.parseLong(dateFormat.format(cal.getTime()));
}
public static Calendar getCalendarFromFormattedLong(long l){
try {
Calendar c = Calendar.getInstance();
c.setTime(dateFormat.parse(String.valueOf(l)));
return c;
} catch (ParseException e) {
return null;
}
}
This implementation uses Java’s SimpleDateFormat object to convert Calendar objects formatted long dates and formatted long dates back to Calendar objects.
Note: For those of you who might be wondering why I’m using a Calendar object as the primary Java data type for my date/times (rather than the Date object), it is because the implementation of Android’s DatePickerDialog is more closely aligned with the Calendar object.
In the database adapter for my application, the create and insert functions that include date and time information take a Calendar object and convert to a long just prior to inserting it into the SQLite database:
public long updateDateRecord(long id, Calendar calendar){
ContentValues values = new ContentValues();
// Here is where we apply the Data DateFormat:
values.put(COL_DATE, DateDataFormat
.formatDateAsLong(calendar));
return mDb.update(TABLE_DATES, values, COL_ID + "=" + id, null);
}
ContentValues values = new ContentValues();
// Here is where we apply the Data DateFormat:
values.put(COL_DATE, DateDataFormat
.formatDateAsLong(calendar));
return mDb.update(TABLE_DATES, values, COL_ID + "=" + id, null);
}
On the user interface side, I retrieve the date and time value as a long, convert it to a Calendar object and then format the date into a string for display:
private void populateDate(){
String date = null;
if (mListCursor != null && mListCursor.getCount() > 0){
dateId = mListCursor.getLong(
mListCursor.getColumnIndex(DbAdapter.COL_ID));
long d = mListCursor.getLong(
mListCursor.getColumnIndex(DbAdapter.COL_DATE));
if (d > 0){
mDate = DateDataFormat
.getCalendarFromFormattedLong(d);
date = DateDisplayFormat.getFormattedDate(mDate);
}
}
if (date != null){
mDateText.setText(date);
}
}
String date = null;
if (mListCursor != null && mListCursor.getCount() > 0){
dateId = mListCursor.getLong(
mListCursor.getColumnIndex(DbAdapter.COL_ID));
long d = mListCursor.getLong(
mListCursor.getColumnIndex(DbAdapter.COL_DATE));
if (d > 0){
mDate = DateDataFormat
.getCalendarFromFormattedLong(d);
date = DateDisplayFormat.getFormattedDate(mDate);
}
}
if (date != null){
mDateText.setText(date);
}
}
Code Download
To review a complete, example implementation of this approach in an Android project, please download and import the following Android Eclipse project:DateData_CodeProject.zip
You must have Eclipse and the Android SDK including platform version 1.5 (3) installed to compile and run this project.
Roads not Taken
Here are some things that were not considered in this approach, but might be relevant to your application:- Time Zones – All the date and times I was recording for my application were in relation to one user in a single time zone, so they did not figure into my implementation. Folks working with dates and times that need to be coordinated across time zones would need to enhance my approach using an implementation of Coordinated Universal Time (UTC).
- High-Precision Times – All the dates and times for my application would differ by at least a second or more, so there was no reason for me to include millisecond precision.
Conclusion
This example implementation is not meant to be the ultimate solution for saving and retrieving date and time data in Android. As noted above, your application may have different needs which are not considered by this methodology, which you should seriously consider before adopting this approach.I hope this article helps you more easily navigate the decisions of date and time storage on the Android platform and gets you another step further in completing your app. Happy programming!
