February 9, 2005

8:39 AM

A long time ago, I was taught to store to currency values in databases as cents (or the local equivalent) to avoid rounding problems with floating point column types. Is this a sound practice, or a bunch of hogwash?
posted by alan to (10 comments total)

Yes. Well, "don't store fixed-precision values as floating-point" is sound practice, regardless of what those fixed-precision values represent. Most databases will have a fixed-point type, DECIMAL, that lets you avoid having to store things as integers.
posted by mendel at 8:47 AM on February 9, 2005

Some databases also let you declare the precision of a float. Either way, if you're doing math, you should manually round in your application so that you know which way you're rounding. There is nothing worse than an application that handles money and rounds up in some places but rounds down in other random places.
posted by SpecialK at 9:10 AM on February 9, 2005

Don't use binary floating point numbers to store financial numbers, regardless of the precision. If your system doesn't support decimal floating point, do as you have been doing by storing cents.

The Python Enhancement Proposal for its Decimal datatype explains some of the issues.
posted by grouse at 9:22 AM on February 9, 2005

Historically, integer math has been *much* faster than floating-point math. Modern CPUs with Alitvec or MMX are reversing that trend, but the big iron back in the data center may not have the nifty PC style floating point units.

Also, what everyone else has said about precision issues.
posted by b1tr0t at 9:30 AM on February 9, 2005

Yeah, there's a caveat I should add to my previous answer: decimal floating point will be even slower than binary floating point.
posted by grouse at 10:00 AM on February 9, 2005

Related document: Lies My Calculator Told Me
posted by Zed_Lopez at 11:12 AM on February 9, 2005

Most databases have a currency data type that is stored as a scaled integer so you don't have the issue of binary floating point rounding errors.
posted by AstroGuy at 11:25 AM on February 9, 2005

Hope your software isn't going to be used at a gas station, if you're using integer cents. They all seem to charge something like $2.139 per gallon.
posted by knave at 1:02 PM on February 9, 2005

Hmmmm...most modern databases have a currency data type that should be accurate to the mill (1/100 of cent).

There are many, many, many places that price to the mill.

I remember when I did an inventory/ordering system for a custom printing company, they priced things by the ream, or other such quanties, but often they did short runs, but used a ream price adjusted for quantity. Which meant the per page price was often below $0.01, and into the mills.

And when you do partial pricing of items, and don't use mills, if you do large volumes, the rounding errors will kill you.
posted by patrickje at 1:53 PM on February 9, 2005

A mill is a tenth of a cent, not a hundredth.
posted by grouse at 4:53 PM on February 9, 2005