CODEWITHSUNDEEP
javasql
Roger
Roger

Reputation: 4259

Large numbers losing precision in SQLiteDB?

I'm storing 0.00 decimal values as text in an SQLiteDB. I'm retrieving them by

"SELECT SUM(SUBSTR(amount, 1)) FROM record WHERE date BETWEEN '" +
    date1 + "' AND '" + date2 + "'";

Problem is when the numbers start getting up to 10000.00 there seems to be issues with precision.

Upvotes: 1

Views: 279

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

The value has been turned into a REAL (has floating point issues). One option is just to round it

"SELECT ROUND(SUM(SUBSTR(amount, 1)),2) FROM record WHERE date BETWEEN '"+date+"' AND '"+date2+"'";

Or use INTEGER maths, shifting the position by 2 decimals

"SELECT SUM(CAST(SUBSTR(amount, 1)*100 as INT))/100.0 FROM record WHERE date BETWEEN '"+date+"' AND '"+date2+"'";

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

Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes: NULL, INTEGER, REAL, TEXT, BLOB

What you need to know about Floating Point maths

Upvotes: 1

Related Questions