Did you actually read the post above yours?
Let me be a little more blunt.
Your first point - you failed to read the documentation.
Your second point - it’s a pathological case, and applies to nearly any database - hence is meaningless.
2 Likes
If you use MongoDB, you should not use operators like $inc or $sum for monetary values, just as you should not use JavaScript’s inbuilt arithmetic operators (+, -, *, /) for monetary calculations because it cannot do base-10 number arithmetic accurately.
2 Likes
woooahhh… i lot discussions… thank Youuu… learn a lot…!!
There’s mention of Fortran above but please don’t forget COBOL. COBOL is still great for handling money calculations thanks to good old Binary Coded Decimal (BCD).
It was so easy if you wanted it to be. If you took 30% of $3.10 you’d get a nice, and rational, $1.03 returned. You did not get 1.333333333333333333333333333333333… like you do these days.
Of course, you still had problems. If you divide a $1 between three people who gets the leftover cent?
I understood that the developer could insert its own bank account for that one cent.
(Full discloser: When I started as a programmer I started with (assembler and) COBOL.)
1 Like
Why not use $inc? It operates on Double data type w\o problems.
As for js + mongo - just store your monetary multiplied by 100. I don’t see no problems with that either.
Arithmetic operations of doubles give imprecise results. That may be OK for non-financial applications.
Do some testing, of the $inc operator in MongoDB. If results were precise it would have been great. But in my testing they weren’t, so I had to write workarounds.
That does not account for applications that require more than two decimal places, e.g. currency rates. USD/JPY requires 3 decimal places. EUR/USD requires 5. Years ago it was 2 and 4 respectively. Who knows, in future USD/JPY may be quoted with 4 decimal places and EUR/USD with 6 decimal places. As a developer you need to be mindful of such possibilities.
These are edge case which sd be addressed when they arise - unless you have limitless resources to deal with every possible outcome imaginable…
It’s better to be aware of the issues at design and planning time. It can be a hassle to redesign at later stages.
My point is that it can be more complicated than you think. Saying simply “there’s no problem, just do this or that” is often not helpful in solving frustrating problems of working with monetary data in JavaScript which I have already been through.
Based on my experience, when developing financial applications in JavaScript, it’s OK to store monetary values in ordinary double data types, but not OK to do ordinary arithmetic operations with them. You can use packages like bignumber.js (which I use) to do precise arithmetic operations in JavaScript.
With MongoDB, $inc or aggregate operators exhibit the same imprecision problems as ordinary arithmetic operations in JavaScript. So I have had to write extra code to use bignumber.js (instead of MongoDB) to do the arithmetic operations and then save the result into the database.
1 Like
Then store it as Micro money as Google does it:
Working with doubles is simply a bad idea, like explained before. Never store money as doubles in your database. Nor use them in your code when doing money calculations.
This is not only a Javascript issue, but goes for almost any programming language.
1 Like
What if I want to store 0.00000001?
What if a stone falls from the sky and hits you on da head? If you live in a meteor-riddled area (hehe, what pun!) - wear Ironman suit.
Edge cases require special solutions.
If you would really require that kind of precision (which I guess you don’t), than you could work with even larger numbers. But if a Google, using all currencies world wide, is able to manage all its use-cases with micromoney, I can’t see why your application wouldn’t be able to do so.
When do you go using something smaller than a cent? Or smaller than micromoney in this case… Or are you now just trolling?
Cryptocurrencies like bitcoin can be expressed with up to 8 decimal places.
Expressing decimal numbers as integers is not a once-and-for-all fix, as I’ve said before. Crockford’s dec64 from 2014 would be if it was implemented in ES.
1 Like
Hadn’t thought of cryptocurrencies yet… Thanks for the clarification. Something like dec64 would be nice in such a case.
So just curious about one more thing: what is your current workaround? You’re probably still using Javascript and dec64 isn’t getting into the language in the near feature. So what now?
I use bignumber.js package for all calculations. I tried storing all numbers as BigNumber objects in the MongoDB database but I had to always cast it back to BigNumber object from a plain object when retrieving. It also didn’t work with a transactions package. So now I just store monetary values as plain numbers (doubles) in MongoDB. In my tests I’ve found that storing as plain doubles is not a problem. It’s the calculating that is the problem.
Here is some example test code:
let w = 0.00000003
let x = 0.00000001
console.log(w - x) // 1.9999999999999997e-8
let y = new BigNumber(w)
let z = new BigNumber(x)
console.log(y.minus(z).toNumber()) // 2e-8
Collection1.remove({})
Collection1.insert({ w: w, x: x, diffViaBN: y.minus(z).toNumber() })
let c1 = Collection1.findOne()
console.log(c1.w, c1.x, c1.w - c1.x, c1.diffViaBN) // 3e-8 1e-8 1.9999999999999997e-8 2e-8
Collection1.remove({})
Collection1.insert({ w: 0.00000001, x: 0.00000001 })
c1 = Collection1.findOne()
console.log(c1.w, c1.x)
Collection1.update({ }, { $inc: { w: 0.00000002 }, $set: { x: new BigNumber(c1.x).plus(0.00000002).toNumber() } })
c1 = Collection1.findOne()
console.log(c1.w, c1.x) // 3.0000000000000004e-8 3e-8
Collection1.remove({})
Collection1.insert({ w: 0.00000001 })
Collection1.insert({ w: 0.00000002 })
let sum = Collection1.aggregate({
$group: { _id: '', total: { $sum: '$w' } }
});
console.log(sum[0].total) // 3.0000000000000004e-8
1 Like
@tab00, if you want precision you must use integers. That 0.1 + 0.2 doesn’t evaluate to 0.3 is unfortunate, but you can’t require that from doubles. This is not a problem for their intended usage, and even if 0.1 + 0.2 would evaluate to 0.3, you would still get into trouble with infinite decimals (such as 1/3 (splitting $1 in 3)).
Going to a smaller unit (usually the smallest possible/necessary) is the traditional solution. With this solution 1/3 is usually not a problem any more since you shouldn’t come across it (the smallest unit can’t be split!).
But if this solution doesn’t work for you, how about moving on to fractions? Instead of trying to represent 1/3 as a single number, represent it as two numbers (one for the numerator and one for the denominator). Now you have precise precision, right? It’s a bit more complicated to work with (wish you were using Lisp now, right? ;)), but it might be the right solution for you.
1 Like
Don’t you think something like dec64 would be the best way to fix the problem? I’ve worked with .NET and I didn’t have any need to use integers for precise decimal arithmetic. 0.1 + 0.2 exactly equals 0.3 if the operands are declared as decimal data types. I didn’t have to waste time thinking about any of this.
I don’t really want to waste any more time discussing this.