12
Li that post on the lack of precision of double
, and virtually every link in the post.
Then I realized that this was a vulnerability of my software in production (since I have rounds
to every corner before making any kind of monetary value-related calculation). So before having bigger problems with customers' pockets. I decided to refactor my system using BigDecimal
for all attributes related to monetary values.
When arriving at the database access layer I came across a problem/question of how to reliably and accurately store these values in the Sqlite Database?
Researching found some possibilities:
- Save to a column
INTEGER
, in the form of pennies, for example:BigDecimal valor = new BigDecimal("1.67")
, at the base would:long valueInCents = valor.multiply(new BigDecimal(100)).longValue()
, which would result in 167 in the database column, requiring the reverse process when retrieving the value from the database again. (This is the one I found more effective, practical and interesting so far); - Save to two columns
INTEGER
one for the whole part of the value and one for the decimal part. (This is more comprehensive as it would give freedom to use as many decimal places as are needed up to the limit ofINTEGER
sqlite think which is 18, but for monetary values it may be an unnecessary effort if you work only with the 2 pennies); - Save to type column
TEXT
and always converts fromBigDecimal
forString
and ofString
forBigDecimal
. (This would be the most practical solution, since theBigDecimal
has a constructor for the value inString
, but despite this is the solution that I like least since it would not have the numerical value in the database, so any calculation with this column should be done via application).
Doubts
- The column of the type
REAL
, or any other decimal variant, would have the precision to store that valueBigDecimal
maintaining accuracy without major problems? - Which of the 3 possibilities mentioned above (or some extra solution) would be more suitable and efficient to store monetary values (with only 2 cents. Ex:
1.67
)?
https://www.sqlite.org/datatype3.html
– Jorge B.