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?

score 10 · Answer 1 · edited May 23 '17 at 12:33

10

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.

edited May 23 '17 at 12:33

Community

1
1

answered Apr 14 '12 at 13:11

Boris Strandjev

46,145
15
108
135

I even know why I use float and why Google use everywhere in the API. Because it is 32 bits in a 32 bit CPU and because the precision is enough. – Tectona Apr 14 '12 at 18:06
@vkaci then just do not worry about the precision. However the speed difference is really neglectable I think (I never found any significant difference). You can also refer to this thread: http://stackoverflow.com/q/1074474/1108032 – Boris Strandjev Apr 14 '12 at 18:10
Sorry. The case here is that the number is 22.2 and storing as 22.2000007629395. This is not a precision problem! The float is 22.2, after the ContentValues.put the number still seems 22.2 (getAsString, getASFloat), but during the insert something happens. I assume that this is an API bug. – Tectona Apr 14 '12 at 18:11
@vkaci I have edited my answer. If you want to look into what I say. If you want to believe me. – Boris Strandjev Apr 14 '12 at 18:15

Android development - SQLite storing float

1 Answers1

Linked