CODEWITHSUNDEEP
androidsqlitedate
Gold
Gold

Reputation: 62464

Unexpected results running SQLite on Android

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

Upvotes: 0

Views: 122

Answers (1)

GreyBeardedGeek
GreyBeardedGeek

Reputation: 30088

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.

Upvotes: 3

Related Questions