Sunday, July 5, 2009

Date, Time, SQLite and Android

This post summarizes my recent experience (problem, finally solved) with storing and then reading and displaying the date and time in Android application.

Storing the current time/date in SQLite
The most common way is to do:
database.execSQL("update TABLE_NAME set COLUMN_NAME = datetime('now') where ...");
As a result, SQLite stores a string representing the current time in UTC (GMT), using the ISO8601 date/time format. This format (YYYY-MM-DD HH:MM:SS), is by the way suitable for date/time comparisons. The fact that the value stored is in UTC and not in a local time zone, is actually nice. More about it below.

Retrieving a time/date and displaying it
To retrieve the value, follow the recommended Android practice:
Cursor row = databaseHelper.query(true, TABLE_NAME, new String[] {
COLUMN_INDEX}, ID_COLUMN_INDEX + "=" + rowId,
null, null, null, null, null);
String dateTime = row.getString(row.getColumnIndexOrThrow(COLUMN_INDEX));
This, returns a string, parse it and reformat to your local format and time zone:
DateFormat iso8601Format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try {
date = iso8601Format.parse(dateTime);
} catch (ParseException e) {
Log.e(TAG, "Parsing ISO8601 datetime failed", e);
}

long when = date.getTime();
int flags = 0;
flags |= android.text.format.DateUtils.FORMAT_SHOW_TIME;
flags |= android.text.format.DateUtils.FORMAT_SHOW_DATE;
flags |= android.text.format.DateUtils.FORMAT_ABBREV_MONTH;
flags |= android.text.format.DateUtils.FORMAT_SHOW_YEAR;

String finalDateTime = android.text.format.DateUtils.formatDateTime(context,
when + TimeZone.getDefault().getOffset(when), flags);
Note the when + TimeZone.getDefault().getOffset(when) above. This does the trick to convert the UTC time/date to local time. The DateUtils.formatDateTime() seem to be supposed to do this, but it doesn't, or I wasn't able to find a way to achieve it.

And that's it.

11 comments:

Amazing... said...

hey. i am working on xmlparsing. inthat i have a date value as wed, 3 Feb 2010, 01:22:19 -0500. how can i covert this text value to date and store it in sqlite table.

Amazing... said...

hey. i am working on xmlparsing. inthat i have a date value as wed, 3 Feb 2010, 01:22:19 -0500. how can i covert this text value to date and store it in sqlite table.

stanb said...

Amazing,
The simplest way would be to use SimpleDateFormat("..."). Try finding the right format description in javadoc for this method (http://java.sun.com/j2se/1.5.0/docs/api/java/text/SimpleDateFormat.html). For example, for the "wed", you would use 'E', then ',' and so on.
droidguy

manandbytes said...

What about SQLite's function datetime('your-utc-time', 'localtime')?

Vaishali Sharma said...

Amazing you can either use simple date format to get date and time, or you can first store the value of date tag from your xml in a string and then extract it's substring, this is also a very simple wat

Vaishali Sharma said...

hey amazing, you can either use simpledateformat or simply you can first save the value of your date tag in a string and then extract substring from it,

15MinBlogger said...

Why cant you declare the column as a INTEGER and the datetime will be stored as number of seconds passed after 1970. You can skip the step of parsing it back to date object later.

Akshay Medakkar said...

Hello, i have list of database which is added dynamically from one activity now i want to display date and time on same activity means when i am adding data it should store data along with its time and date in sqlite data base ...How to do that please help me...

Akshay Medakkar said...

Hello, i have list of database which is added dynamically from one activity now i want to display date and time on same activity means when i am adding data it should store data along with its time and date in sqlite data base ...How to do that please help me...

saloni shah said...

how do i save date taken from datepicker in textview and store that in database..

Martial SERON said...

Thanks!
Exactly what I was looking for.