0

I am using real type to save price value in SQLite database but it does not return correctly value. If I save a few values like 1.01 it will return 4.029999999999999. I have read that best way to save prices in SQLite is to use integer type

But I don't know how to use it. How to store and retrieve prices in this case? Here code how I retrieve price now:

    Cursor cursor = null;
    double totalPrice = 0;
    try {
        cursor = mDB.rawQuery("select " + Expense.PRICE + " from " + Expense.TABLE_NAME + " where " + " (strftime("
                + dateType + ", " + Expense.DATE + "))" + "= ? ", new String[] { currentDate });

    } catch (Exception e) {
        Log.e(TAG, "error" + e.toString());
        e.printStackTrace();
    }
    for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
        totalPrice += cursor.getDouble(cursor.getColumnIndex(Expense.PRICE));
    }
Volodymyr
  • 6,393
  • 4
  • 53
  • 84
  • 2
    Firstly, stop building your SQL like that. Use parameterized SQL instead. Next, don't use `double` for price information: store it as an integer if you want, but then *retrieve* it as an integer, and perhaps convert it to BigDecimal. – Jon Skeet Apr 26 '13 at 11:51
  • 1
    Obviously, you'd have to multiply the stored value by 100, or even more, if you need more than 1 cent precision. – stivlo Apr 26 '13 at 11:52
  • 1
    Thank for advice about SQL requests. – Volodymyr Apr 26 '13 at 11:53
  • Don't use integer if this is for large transactions or for stat or aggregation. Use long (in the java code) – Adam Gent Apr 26 '13 at 12:00

2 Answers2

9

It is actually quite simple when you see it.

I don't know what currency you are in, but assume US $.

You want to store $1.01. This is the same as 101 cents. You just multiply by 100.

So before storing the number, multiply by 100. When displaying the number, divide by 100.

Just to add to that answer: I also think you should store the numbers in memory as cents/integers too. So for all your workings, calculations etc, use that integer format.

If a user enters a $ amount, convert it straight away into integer (multiply by 100). And only change back to $ when you need to display it.

In other words, not just when going to and from the database.

Richard Le Mesurier
  • 29,432
  • 22
  • 140
  • 255
  • 1
    Thank, it is quite simple, I thought that is much harder to do! – Volodymyr Apr 26 '13 at 11:56
  • Glad I could help. Its a good lesson to learn early on in your programming life, and there are many blog posts about this, and what the "best" way to do it is... Good luck. – Richard Le Mesurier Apr 26 '13 at 12:00
  • I like this solution, but unfortunately it doesn't account for countries that have a different number of decimal places in their currency. See this Stack Overflow question (ignore the fact that is pertaining to iPhone development): (https://stackoverflow.com/questions/2701301/various-countrys-currency-decimal-places-width-in-the-iphone-sdk) – shagberg Sep 04 '18 at 18:35
  • 1
    Thanks @shagberg that is the generalised version - with useful information on where to find out about the number of decimal points. The basic idea is the same: "do not use float to store currency, rather use integer values". – Richard Le Mesurier Sep 05 '18 at 10:03
1

Effective Java book describes, in Item 48, that one should not use float or double if exact answers is required.

I think you should use long value to store the price value, but store the values in "cents" (or equivalent as per currency of choice) instead of "dollars". This way your math around prices should work just fine with no loss due to floating-point arithmetic.

Wand Maker
  • 18,476
  • 8
  • 53
  • 87