6 ms·
My only grievance, and a blocker, is that SQLite does not support decimal numbers, making it less practical to work with finance related data.
by miohtama 7y ago
My only grievance, and a blocker, is that SQLite does not support decimal numbers, making it less practical to work with finance related data.
- webpaymentsguy 7y agoYou probably already heard of storing amount in cents as an integer, but it's a pattern worth mentioning if it fits your use case.
- marton78 7y agoI don't know how it's done in the US, but in Europe financial calculations are done with four digits after the dot.
- catmanjan 7y agoSo store the value as milli-cents? (haha)
- Loranubi 7y agoShouldn't that be centi-cents? ^^
- inferiorhuman 7y agoOr in MySQL: innocents.
- miohtama 7y agoIt is possible, but makes is somewhat impractical, because you need to convert numbers back and forth every time you touch them on an application level. Not worth the extra headache.
- luhn 7y agoSpeaking from experience, if you exclusively deal with integers internally and then apply the appropriate formatting when outputting to the user (which you need to deal with regardless of internal representation), it makes dealing with monetary data so much easier. Integers are generally better supported, faster, and more convenient for most languages, databases, and serialization formats.
- gen220 7y ago+1 adding my anecdata to this. If you don’t need irrational numbers (few financial applications do?), the tuple of (value, scale) can take you very far.
- utxaa 7y agoyou never really deal with irrationals ... do you mean floating point?
- gen220 7y agoNope, specifically meant irrationals. Floating point (up to a certain amount of absurdity) you can describe with integers (value, scale). Real numbers you can describe with integers (numerator, denominator, scale). Irrationals are more difficult. That's why we try not to use them :). Hopefully your company's commission tiers are not defined in terms of circles or euclidean distances.
- fetbaffe 7y agoDo you then use variable length for the precision depending on the currency?
- 7y ago
- BlueTemplar 7y agoWait, there are finance applications that don't forbid floating point numbers ??
- zimbu668 7y agoFixed point arithmetic: https://en.wikipedia.org/wiki/Fixed-point_arithmetic https://en.wikipedia.org/wiki/Fixed-point_arithmetic
- fowl2 7y agoI'd like you to meet my friend "Excel"...
- BlueTemplar 7y agoI mean for professional applications, not amateur stuff.
- Dylan16807 7y agoExcel is used for very very expensive professional tasks.
- Zarel 7y agoThey're talking about decimal numbers as a different thing from floating point numbers: https://en.wikipedia.org/wiki/Decimal#Decimal_computation https://en.wikipedia.org/wiki/Decimal#Decimal_computation
- massysett 7y agoIn addition to the other workarounds suggested, you can store the numbers in two columns - one for the coefficient and one for the power of ten, negated. For instance 2.50 becomes 250 and 2, meaning 250 x 10 ^ -2. You can then do math at the application level, or even in SQLite with user-defined functions or even with recursive CTEs if you’re a bit of a masochist.
- bob1029 7y agoYou know its strange - We work with an incredible number of decimal facts (financial data), but we find that this has zero impact on our SQLite usage because we have policy where extremely complex business entities that are prone to rapid change are serialized to JSON and stored as BLOB columns. Within this JSON serialized data is where virtually all of our decimal values reside. Obviously, its not a solution for any arbitrary case, but you can usually find workarounds in interesting places further up the stack. We use only 3 data types in SQLite - BINARY, TEXT and INTEGER.
- lukevp 7y agoHow do you do reporting on these blobs?
- zzo38computer 7y agoThere is no BINARY type in SQLite. (However, the declared type mostly does not restrict the data that column may store. All columns can store blobs and nulls (unless NOT NULL is specified). Columns declared CHAR, VARCHAR, CLOB, CHARACTER, or TEXT can store only text, blob, and null; trying to store a number will convert it to text. Columns declared REAL, FLOAT, or DOUBLE cannot store integers; they are converted into floating numbers; if you try to store text representing a number, it is converted to that number. Columns declared INT, INTEGER, NUMERIC, or an unrecognized type (such as BINARY) can store any value, but text representing a number is converted to a number, and floating numbers which can be losslessly represented as integers are converted to integers. Columns declared BLOB or with no type specified can store any value.)
- manigandham 7y agoUse a 64-bit integer and store the value as microdollars (or whatever main currency you're using). Simple, fast, correct, and compatible everywhere.
- Scarbutt 7y agohttps://floating-point-gui.de/formats/integer/ https://floating-point-gui.de/formats/integer/
- tommica 7y agoJust to give context, the link points out possible drawbacks with the approach mentioned above
- patrec 7y ago> Integers have complete precision, but very limited range You can store up to 900 trillion dollars with 0.01 cent resolution in an int64. I doubt many applications need more range than that. > when [integers] overflow, they usually “wrap around” silently If you want to write an application handling money, you really want to use a Money type of some description, regardless of whether the underlying storage format is an integer. So it's very easy to avoid silent overflows, since basically all languages that are prone to them also offer primitives that check perform addition, subtraction and multiplication with checks for over/under flow. > The implicit decimal point is hard to change and extremely inflexible If you have a Money type, it's not hard to change. The pain point would normally be serialization, but that's not necessarily different from using decimal floating point internally, since very few APIs, serialization protocols or even databases have native support for fp decimals either. > The number of subunits in currencies change with time in the world. I suspect Goverments are equally or more likely to drop trailing zeros (e.g. Iraq did not so long ago), which you also need to deal with anyway and which does not get easier by using a floating point format.
- porker 7y agoThere is a SQLite decimal extension, unfortunately still under development and not finished: https://chiselapp.com/user/lifepillar/repository/sqlite3decimal/home https://chiselapp.com/user/lifepillar/repository/sqlite3deci...
- anitil 7y agoInteresting extension. I'm not from the finance field so I don't know the kind of regulations/standards are used. Do you happen to know?