Implementation of Decimal Numbers
Implementation-specific precise representations in different languages and databases.
On “Double error!” we explored the problem with storing decimal numbers and how it is impossible to store simple numbers such as 0.3 in a float or double type. This post talks about some data types available to work with decimals so your 0.3 does not result in 0.300000011920928955078125 (float, 32-bit) or 0.299999999999999988897769753748434595763683319091796875 (double, 64-bit).
If you need to represent exact decimals, one way to go would be to store the integer and the fraction part in 2 variables (integer or long) and do math with them every time that you need to sum, subtract, etc., to account for the over/underflow.
Of course, nowadays, we don’t have to worry about that. Most modern programming languages and other systems (like databases) provide alternative ways to represent decimal numbers with no loss of precision.
Here is an index so you can jump straight to what you need.
Programming Languages
Databases
Programming Languages
Java’s BigDecimal
Java’s BigDecimal implements Immutable, arbitrary-precision signed decimal numbers. A BigDecimal consists of an arbitrary precision integer unscaled value and a 32-bit integer scale.
The BigDecimal class provides operations for arithmetic, scale manipulation, rounding, comparison, hashing, and format conversion. The toString() method provides a canonical representation of a BigDecimal.
Notes on the use of BigDecimal:
BigDecimal has a value and a scale (number of fraction digits). When using .equals(), it will compare both attributes. Therefore, 5.00 will be different from 5.0000. If you want to compare the values for equality, you must do:
bigDecimal1.compareTo(bigDecimal2) == 0There are 2 strategies to create a BigDecimal from a Double. The recommended way is
BigDecimal.valueOf(double);Which will return a BigDecimal according to the double’s specification. However, if you wish to override the double’s precision, you should use one of the constructors that take a MathContext as a parameter.
Avoid any methods that take an int as a parameter for rounding strategies. They are deprecated in the most recent Java versions. Use it only if you don’t have an alternative. If you are selecting a rounding strategy, make sure that it is the same that the rest of the application uses. If you are choosing one, the half to nearest even is the preferred strategy as it yields the smallest errors after mathematic operations such as sums and subtractions (statistically).
If you are to store this value in a database to recover it, make sure that the database has a data type that won’t convert it to a floating-point representation or your precision will be lost when you read the value back.
Python’s Decimal
Python’s decimal module provides support for fast correctly-rounded decimal floating-point arithmetic.
The decimal module incorporates a notion of significant places so that 1.30 + 1.20 is 2.50. The trailing zero is kept to indicate significance. This is the customary presentation for monetary applications. For multiplication, the “schoolbook” approach uses all the figures in the multiplicands. For instance, 1.3 * 1.2 gives 1.56 while 1.30 * 1.20 gives 1.5600.
Unlike hardware-based binary floating point, the decimal module has a user alterable precision (defaulting to 28 places). The decimal module was designed to support without prejudice, both exact unrounded decimal arithmetic (sometimes called fixed-point arithmetic) and rounded floating-point arithmetic.
Decimal objects are immutable. To learn how to use them, refer to the documentation here.
JavaScript and TypeScript libraries
These languages don’t provide precise decimal representations, so libraries have to be used. Here are the 3 most popular at the time when this post is being written:
big.js: minimalist; easy-to-use; precision specified in decimal places; precision applied to division only; 4 rounding modes.
bignumber.js: configuration options; NaN; Infinity; precision specified in decimal places; precision applied to division only; random numbers; base conversion; 9 rounding modes; modulo modes; modular exponentiation.
decimal.js: configuration options; NaN; Infinity; non-integer powers, exp, ln, log; trigonometric functions; precision specified in significant digits; precision always applied; random numbers; 9 rounding modes; modulo modes; binary, octal, and hexadecimal; binary exponential notation.
JavaScript does provide a method that allows for fixed-point calculation, which partially fixes this problem.
Problems like 0.1 + 0.2 not being equal to 0.3 can be solved by doing:
(0.1 + 0.2).toFixed(1);It will result in 0.3. However, the return will be a String, as this value can’t be represented in any of the number types. For more information, read the reference documentation about toFixed() at W3Schools or toFixed() at ECMAScript Language specification.
.NET / C#
.NET’s Decimal Struct represents decimal numbers ranging from positive to negative 79,228,162,514,264,337,593,543,950,335 .
The binary representation of a Decimal value is 128-bits consisting of a 96-bit integer number, and a 32-bit set of flags representing things such as the sign and scaling factor used to specify what portion of it is a decimal fraction. Therefore, the binary representation of a Decimal value the form, ((-296 to 296) / 10(0 to 28)), where -(296-1) is equal to MinValue, and 296-1 is equal to MaxValue. For more information about the binary representation of Decimal values and an example, see the Decimal(Int32[]) constructor and the GetBits method.
The scaling factor also preserves any trailing zeros in a Decimal number. Trailing zeros do not affect the value of a Decimal number in arithmetic or comparison operations. However, trailing zeros might be revealed by the ToString method if an appropriate format string is applied.
Databases
MySQL’s DECIMAL
MySQL’s DECIMAL type provides the equivalent of Java’s BigDecimal, allowing operations to be performed without introducing floating-point errors (https://dev.mysql.com/doc/refman/8.0/en/fixed-point-types.html).
Syntax
DECIMAL(precision, scale)Example
CREATE TABLE your_table
(
your_column DECIMAL(6,4) NOT NULL
);From MySQL’s documentation about precision math:
The declaration syntax for a DECIMAL column is DECIMAL(M,D). The ranges of values for the arguments are as follows:
-
M is the maximum number of digits (the precision). It has a range of 1 to 65.
-
D is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than M.
If D is omitted, the default is 0. If M is omitted, the default is 10.
When you read from this value, make sure you use a data type such as BigDecimal (or the equivalent in another language) to keep the precision intact. Do not convert it to a floating-point representation.
Oracle’s DECIMAL
Oracle’s DECIMAL provides an exact numeric in which the precision and scale can be arbitrarily sized. You can specify the precision (the total number of digits, both to the left and the right of the decimal point) and the scale (the number of digits of the fractional component). The amount of storage required is based on the precision.
Syntax
{ DECIMAL | DEC } \[\(precision \[, scale ])]Example
CREATE TABLE your_table
(
your_column DECIMAL(18,5)
);The precision must be between 1 and 31. The scale must be less than or equal to the precision.
If the scale is not specified, the default scale is 0. If the precision is not specified, the default precision is 5.
An attempt to put a numeric value into a DECIMAL is allowed as long as any non-fractional precision is not lost. When truncating trailing digits from a DECIMAL value, Derby rounds down.
When mixed with other data types in expressions, the resulting data type follows the rules shown in Numeric type promotion in expressions.
When two decimal values are mixed in an expression, the scale and precision of the resulting value follow the rules shown in Scale for decimal arithmetic.
PostgreSQL
The PostgreSQL’s NUMERIC type can store numbers with a very large number of digits. It is especially recommended for storing monetary amounts and other quantities where exactness is required. Calculations with NUMERIC values yield exact results where possible. PostgreSQL’s NUMERIC is the same as SQL DECIMAL.
Syntax
NUMERIC(precision, scale)Example
CREATE TABLE your_table
(
your_column NUMERIC(18,5)
);The precision of a numeric is the total count of significant digits in the whole number, that is, the number of digits to both sides of the decimal point. The scale of a numeric is the count of decimal digits in the fractional part, to the right of the decimal point. So the number 23.5141 has a precision of 6 and a scale of 4. Integers can be considered to have a scale of zero.
The maximum precision that can be explicitly specified in a NUMERIC type declaration is 1000.
If the scale of a value to be stored is greater than the declared scale of the column, the system will round the value to the specified number of fractional digits. Then, if the number of digits to the left of the decimal point exceeds the declared precision minus the declared scale, an error is raised.
Numeric values are physically stored without any extra leading or trailing zeroes. Thus, the declared precision and scale of a column are maximums, not fixed allocations. (In this sense the numeric type is more akin to varchar(n) than to char(n).) The actual storage requirement is two bytes for each group of four decimal digits, plus three to eight bytes overhead.
When rounding values, the numeric type rounds ties away from zero.
MongoDB
The mongo shell treats all numbers as 64-bit floating-point double values by default and offers the NumberDecimal type as a way to store 128-bit floating-point values. Unfortunately, MongoDB does not provide a data type that doesn’t have a precision loss.
If you like this post, please share it (you can use the buttons in the end of this post). It will help me a lot and keep me motivated to write more. Also, subscribe to get notified of new posts when they come out.