Problem inserting android.text.format.Time.toMillis value into SQLite DB on droid

Question

I'm writing an app for Android OS, and I need to store some time values in the SQLite DB. I have been using android.text.format.Time to store the time values in the app, and then inserting the values as millis into the DB as REAL values. On the SDK emulator, everything works perfectly. On the sole phone I've had the opportunity to test my app (so far), my duration code doesn't work as expected. Some relevant code:

private static final String DATABASE_CREATE =
    "create table " + DATABASE_TABLE + " ("
     + KEY_ROWID + " integer primary key autoincrement, "
     + KEY_START + " REAL, "
     + KEY_STOP + " REAL, "
     + KEY_DUR + " REAL );";

...

private SQLiteDatabase mDb;
ContentValues timerValues = new ContentValues();

...

timerValues.put(KEY_START, stime.toMillis(false));
timerValues.put(KEY_STOP, etime.toMillis(false));
timerValues.put(KEY_DURATION, stime.toMillis(false)-etime.toMillis(false));
int result = mDb.insert(DATABASE_TABLE, null, timerValues);

I pull this data from two separate functions with slightly different bits of code, both using Time.set(long millis), both giving incorrect results: The start and stop values come back correct, but the duration comes out 17 hours too large. Am I missing something about calculating durations or does this just seem like there's something "special" about this particular droid? I'll have another droid to test on Monday, but any ideas are appreciated.

Answer 1

Besides Mr. Currie's note about column names, there's the question of why you're wasting space storing KEY_DURATION in the first place. It can be calculated from KEY_START and KEY_STOP:

SELECT start, stop, dur=stop-start FROM whatever

Also, android.text.format.Time only has second precision, so if you need milliseconds, you should not be using that class.

If none of those help or are deemed suitable, try logging the value you are putting in KEY_DURATION, to see if the problem is in your calculation or in how it is being stored.

Answer 2

The issue actually turned out to be caused by the time zone settings on the phone. It appears that Time.toMillis will export the time as milliseconds from epoch, UTC. When setting time using Time.set(long millis), Time assumes that millis is milliseconds from Epoch, UTC. When you then request Time.format("%T"), Time returns a HH:MM:SS string adjusted to the local TZ. This makes sense unless you are using Time to measure a duration, in which case some effort should probably be used to account for the TZ adjustment. So, what I expected to be an elapsed time of say, 20 minutes, turned out to be represented by Time as 17:20:00 on the day before Epoch (MST or GMT-7). Nice little undocumented behavior.

In my application, I opted to write a simple utility class to handle converting to milliseconds to an appropriate string. Hopefully this post will save someone else some headache, as my google-fu revealed nothing related.

Answer 3

Hi, How can I measure the start,stop,duration and pause time of different video which is streaming from website?