On 2018-12-15 2:15 AM, Frank Millman wrote:
On 2018-12-14 11:24 AM, Darren Duncan wrote:
If yours is a financial application then you should be using exact numeric types
only, such as integers that represent multiples of whatever quantum you are
using, such as cents; fractional numbers are a display or user input format
only, and in those cases they are character strings.
Thanks, Darren. In principle I agree with you, but I am experimenting with a
different approach.
My application supports SQL Server and PostgreSQL as well as sqlite3, and those
databases do have exact numeric types for monetary use, and I am trying to
stick to one code base as much as possible.
The python sqlite3 module allows you to convert sqlite3 data to a python
object, so my approach is to store decimal data as text in sqlite3, and convert
it to a python Decimal object when reading it in. I find that this works ok. I
do have a bit of trouble when using sqlite3 to ‘sum’ a column, as it then
switches to floating point and can lose precision, but provided I convert the
result back to a Decimal object with appropriate rounding it also works.
Having said that, I am still testing, and I may decide that I have to bite the
bullet and store everything as integers, in which case I will use the same
approach for the other databases as well.
Simon Slavin says ‘Currency amounts should be stored as integers’. Does this
apply to sqlite3 specifically, or is that your recommendation for all databases?
I think that can reasonably apply to all DBMSs, even ones supporting exact
fractional numeric types. Keeping the money as integers everywhere for storage
or where you do math, and character strings only for display to users, is a
reasonable consistent strategy, that also scales more easily to handling
multiple currencies. Its not just about the DBMSs. Some programming languages
don't support exact fractional numbers either, and Javascript doesn't on the web
client side if you do that. -- Darren Duncan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
