Unexpected results running SQLite on Android

Question

I have this table in SQLite on Android device:

CREATE TABLE IF NOT EXISTS MEN(_id INTEGER PRIMARY KEY AUTOINCREMENT,
                               Tdate date, Weight float, Total float, BMI float);

I insert data in this date format (31/05/2011 22:34)

String Tdate = android.text.format.DateFormat.format("dd/MM/yyyy k:mm", new java.util.Date()); 

   SQL = "insert into MEN (Tdate,Weight,Total,BMI) values ('";
   SQL += Tdate + "','" + ThisWeight + "','" + Total + "','" + BMI+ "')";

I run a query like this:

SELECT _id,Tdate,Weight,Total,BMI FROM MEN ORDER BY Tdate desc

But I see the data is not sorted:

31/05/2013 22:20
31/05/2013 19:06
29/04/2013 17:33
02/06/2013 19:25
02/06/2013 19:24

SQLite doesn't have any `Date` datatype!!!!! Am I mistaken? [Datatypes In SQLite](http://www.sqlite.org/datatype3.html) — Sam R., Jun 02 '13 at 17:01
@samrad is right sqlite doesn't support date data type [link](http://www.sqlite.org/datatype3.html). — Saeid Farivar, Jun 02 '13 at 17:03
[Best way to work with dates in Android SQLite](http://stackoverflow.com/q/7363112/1693859) — Sam R., Jun 02 '13 at 17:05
Because SQLite does not have a date type, the type of the `date` column is `TEXT`, so the results are sorted alphabetically. The most simple solution would be to change your date format to something like "YYYY-MM-DD HH:MM:SS.SSS", if possible. — Tamás Szincsák, Jun 02 '13 at 17:06

score 3 · Accepted Answer · answered Jun 02 '13 at 17:48

The comments so far are partially correct - SQLite does not really support Date types.

While it is common for people to recommend storing your dates as text, e.g. "YYYY-MM-DD HH:MM:SS.SSS", this does not address time zone, unless you include that in your string format as well. If you don't specify a time zone, SQLite functions will use UTC, which is probably not what you'd be expecting.

I believe that the best solution (unless you need millisecond accuracy) is to store an integer containing the Unix Epoch time (number of seconds since midnight Jan 01, 1970 UTC). The SQLite date functions support this directly.

If you need milliseconds, I'd consider storing the date as Java Epoch time (number of milliseconds since midnight Jan 01, 1970 UTC) - Unix epoch is approximately Java Epoch / 1000. However, in this case, if you're using SQLite functions, you'd have to divide by 1000 when using them, and you'd lose the millisecond precision.

See http://www.sqlite.org/datatype3.html and http://www.sqlite.org/lang_datefunc.html

P.S. Be really careful about SQL Injection Vulnerability - you should be binding your parameters instead of concatenating them into a sql statement.

Unexpected results running SQLite on Android

1 Answers1