I am using SQLite and need to store prices. SQLite's REAL
data-type says it uses floating-point
which is unacceptable storage for prices. Is there a data-type besides TEXT that I can use to store prices numerically so they sort correctly?
2 Answers
Use an integer & store the prices as the lowest common unit. So, for dollars and cents you'd store it in cents. EG: $1.00 would be stored as 100.
In my experience it's standard practice.
-
3It's extra work but this is the way to go - you also need a currency type field to store with the value so you know how to convert back. You need to pay attention, too - most currencies use 2 decimal places, but some use three. Check out ISO 4127 for the full list.– lonstarOct 1, 2013 at 17:51
-
1CLF Chilean Unidad de Fomento uses 4 decimal places. Bitcoin even more :-) Jan 14, 2018 at 17:58
Certainly, use integer
You will have to decide how many decimal places you are assuming and be consistent at the application level.