Which Data Type To Store Money

There are two hard things in computer science: cache invalidation, naming things, and off-by-one errors.

Many of the errors of modern applications are created by rounding errors. These errors are most commonly cause by the way in which money values are stored.

Most developers, when faced with storing money values, jump on the most convenient box that the money value will fit into. In PgSQL there is a money type, in MySQL there is DECIMAL and all databases have a FLOAT type. All of these types suffer the same problem when dealing with money, and that is rounding.

All floating point calculations rely on precistion restrictions. Many developers choose to store money values as NUMERIC(15,4) to conform to the Generally Accepted Accounting Principles (GAAP). Once again, this is usually close enough for small business, but todays currency needs are changing, particularly with the advent of online currencies. BitCoin requires 8 decimal places for trading.

Continuous values are represented approximately in memory, and therefore computing with floats involves rounding errors. These are tiny discrepancies in bit patterns; thus the test e==f is unsafe if e and f are floats.

It is an inherent limitation of how floating point values are represented in memory in a finite number of bits.

To remove any issues caused by precision rounding, money values need to be stored in there smallest amount, for dollars, the smallest value is cents. So $1.23 would 123. The correct datatype is BIGINT.

The BIGINT data type is eight bytes wide, thus values of -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) are possible. If your money storage needs exceed this amount, you need to contact me where we can begin working on a solution, at a competitive rate ;)

Only at the point of display should money values be formatted to decimal, thus the value will always be open for precise calculation at any time in a transaction. At the point of display, decimals, thousand seperators and currency symbols can be added, until then, keep your money values precise in its lowest denomination.