Android Question Prevent automatic rounding in Sqlite

ernschd

Active Member
Licensed User
Longtime User
Hello,

I have a field of data type "Numeric" in a Sqlite database where floating point numbers are stored. Unfortunately, Sqlite seems to automatically round the decimal places when they exceed a certain length (at least on Android).
For example, 12345.678 becomes 12345.7
This is unacceptable as I need the high accuracy.
That's why I experimentally changed the data type to "Real", but unfortunately with the same result.

Of course I could store the value without decimal point as integer (12345678) and divide it by 1000, but shouldn't actually a modern database system be able to handle floating point values directly?

Does anyone have a suggestion for a better solution?

Many thanks in advance.

Regards.
 

Mahares

Expert
Licensed User
Longtime User
or example, 12345.678 becomes 12345.7
This is unacceptable as I need the high accuracy.
Use a data type TEXT for that. column SQLite will accept it and will not tamper with any rounding. It will keep it as is. You can still do any mathematical calculations as if it was stored numeric.
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
SQLite data that is numbers:

If you store column as TEXT data type:

cursor1.GetDouble("Model") or cursor1.GetString("Model") will display without any truncation. 12345.678

If you store column as NUMERIC or REAL data type:
cursor1.GetString("Model") will display truncated 12345.678 shows as 12345.7
cursor1. GetDouble ("Model") will display as is without truncation 12345.678

cursor1.getlong("Model") will truncate all decimals and displays: 12345 for all types
 
Upvote 0

MikeSW17

Active Member
Licensed User
Just remember 'Accuracy' can have different meanings. For scientific/mathematical calculations, you need to establish the degree of accuracy required/meaningful to the task.
If you're dealing with accounting figures, you must take care to avoid any rounding in calculations - generally by working with integers only for storage and calculation, then inserting a decimal point at display/print time. Works well for addition/subtraction, but additional checks and corrections often needed for multiplication/division.
 
Upvote 0
Top