Android development - SQLite storing float

Question

When I store float value widt SQLiteDatabase.insert the stored value will be different than the original, see below:

I have a database width:

db.execSQL("CREATE TABLE IF NOT EXISTS info_values ("
    + BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
    + "date DATE UNIQUE NOT NULL, "
    + "value REAL NOT NULL)");

When I insert eg 33.3 width:

private class inputdlg_ok implements input_dlg.ReadyListener {
    public void ready(float newvalue) {
      Date d = new Date();
      SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
      ContentValues values = new ContentValues();
      values.put("date", sdf.format(d));
      values.put("value", newvalue);

      database.insert("info_values", null, values);

I have got these:

sqlite> select * from info_values;
83|2012-04-04 09:06:22|33.2999992370605
84|2012-04-02 09:05:57|22.2000007629395

I have tested width exec:

 String sql = "INSERT INTO info_values (date, value) " + 
              "VALUES ('" + sdf.format(d) + "'," + Float.toString(newvalue) + ")";
database.execSQL(sql);

and that form works good.

any idea?

Answer 1

Never thought this thread will help anybody else, but allas here it goes.

Basically your problem is that you use float for the type in the java code. Float is with very low precision. Read the thread I link to, together with all comments and the linked chat. If you still have any problems write back.

You probably know that the double values are stored only upto certain precision in the computers. Even though the values look weird in the database, this is the best approximation of your values you can get when using float. With double you can increase the precision, but you will never get to perfect state. Maybe if you insist on getting precise values limiting the real size in the database might be a way to go.

EDIT As I could not make you believe it IS a precision problem I include the following program:

float f = 22.2;
printf("The number is: %.9f\n", f);

The output is:

22.200000763

I suggest you try it. As you can see this is exactly the number you point out.