MongoDB - What about Decimal type of value?

ID : 134389

viewed : 43

Tags : mongodbdecimalbigdecimalbsonmongodb





Top 5 Answer for MongoDB - What about Decimal type of value?

vote vote

100

If you want an exact representation for financial purposes, then doubles or floating point values are unsuitable as the fractional parts are subject to rounding error. Certain decimal values cannot not be represented using binary-based floating points and must be approximated.

For a less technical intro, see The trouble with rounding floating point numbers; if you want to geek out, then read What Every Computer Scientist Should Know About Floating-Point Arithmetic.

The recommendation of using an integer type (storing the value in cents) is to avoid potential rounding errors. This approach is described as "Using a Scale Factor" in the MongoDB documentation for modelling monetary data and is a general workaround for MongoDB 3.2 and earlier.

MongoDB 3.4 includes a new Decimal BSON type which provides exact precision for manipulating monetary data fields.

vote vote

89

MongoDb added support for Decimal data type in 3.4 version. It is also available from the shell.

3.4 adds support for the decimal128 format with the new decimal data type. The decimal128 format supports numbers with up to 34 decimal digits (i.e. significant digits) and an exponent range of −6143 to +6144.

Unlike the double data type, which only stores an approximation of the decimal values, the decimal data type stores the exact value. For example, a decimal NumberDecimal("9.99") has a precise value of 9.99 where as a double 9.99 would have an approximate value of 9.9900000000000002131628...

vote vote

72

When you don't want to store currency as cent-values, you could store a currency of $1.34 as an object like this:

{     major: 1,     minor: 34,     currency: "USD" } 

Doing any math with objects like this would not be easy and would not use commercial rounding rules. But you shouldn't do any business logic on the database anyway, especially not when it's a "dumb" database like MongoDB.

What you should be doing is serialize/deserialize these objects from/to a Money class in your application which implements the basic currency math operations respecting the rounding rules and throws an exception when you try to do an operation with different currency units ($12.34 + 14.95€ = error - must convert one currency to the other currency first by providing an exchange rate).

vote vote

63

If you're using Mongoose then you can use getter/setter functions in the schema definition e.g.

function getDecimalNumber(val) {    return (val/1000000); } function setDecimalNumber(val) {    return (val*1000000); } 

Applicable to a schema object such as

balance: { type: Number, default: 0, get: getDecimalNumber, set: setDecimalNumber }, 

The number of zeroes to multiply/divide by depends on the accuracy you want.

vote vote

59

Looks like MongoDB has finally added support for decimals, although at the time of writing this is just finished development, but hopefully it should be available very soon in stable version (3.4?).

https://jira.mongodb.org/browse/SERVER-1393

Top 3 video Explaining MongoDB - What about Decimal type of value?







Related QUESTION?