Search code examples
iossqlitefmdbnsdecimalnumber

Rounding issus insert NSDecimalNumber into Sqlite

the NSDeciamlNumber 3.33 becomes 0.32999999999999996 when it insert into database The the sql is correct, but the data become 0.32999999999999996 when sql execute. I'm using FMDB, I tried column type REAL, DECIMAL, they have same issue. If there a way to set scale to DECIMAL column. I tried DECIMAL(8,2), it doesn't help.

Solution

  • As far as I know, SQLite does not offer an actual decimal type, and all floating-point-like numbers are converted to doubles.

    http://www.sqlite.org/datatype3.html

    For DECIMAL(10,5):

    For conversions between TEXT and REAL storage classes, SQLite considers the conversion to be lossless and reversible if the first 15 significant decimal digits of the number are preserved. If the lossless conversion of TEXT to INTEGER or REAL is not possible then the value is stored using the TEXT storage class.

    REAL (8-byte "double") is the usual type for floating point data, and you will experience conversion issues if you want to store an actual value of 3.33.

    Your options:

    • Store a string. Slow, memory inefficient, annoying to maintain, but guaranteed to be correct.
    • Store an int if you know how many decimal points you need (for example, currency values).
    • Store a BLOB, and do the conversion to binary yourself, in whatever format you like.
    • Store a real (double), and live the small differences. If you don't store currencies, this is often okay.

    If you want to store currencies, do not use floating point math, but use an int to store the number of cents (or similar denomination).