5

I am working with dollar amounts. In MySQL database the following fields fee and rate(percentage) are DECIMAL type with 2 decimal precision.

SELECT ROUND(fee * (1- rate/100))),2 ) as profit
from products

Since query is just returning the values instead of saving them in variables, does the precision problem* still exist (that comes with PHP or JS)? If so is it best to round the floating point number in PHP or JS?

*Yes I mean precision issue that occurs when saving double, e.g., 1.5 may be saved as 1.49999999

user2075371
  • 107
  • 1
  • 7
  • *does the precision problem still exist* ahm what? – donald123 Jul 28 '15 at 10:14
  • "The precision problem with double and money" is just people not knowing how to use floating point numbers. – Vatev Jul 28 '15 at 10:16
  • 2
    Your calculation will return results that spans more than two decimal points. You use two. Therefore, rounding will occur and yes - the problem persists. In my opinion (meaning this isn't a fact) - it's the best to let the data management software - the database - deal with numbers. But this is really difficult to implement because you have to perform calculations in PHP so it's quite tricky to tackle. The core of the problem is that the algorithm you use must be **consistent**. You can round up, down or cut off after N decimals - it's mandatory that you always do the same. – N.B. Jul 28 '15 at 10:26
  • 3
    Short answer: Don't use floating point values for money. Store monetary value as integers wherever possible. – Simba Jul 28 '15 at 10:26
  • 2
    @Simba and how does changing the storage for the values solve the inconsistent rounding problem exactly? – Vatev Jul 28 '15 at 10:35
  • @Vatev; since there are no sub-cent amounts, you need a tuple-like data-structure i.e. `[dollars:3,cents:99]` - integers only – birdspider Jul 28 '15 at 11:11
  • 1
    @birdspider so if the client wants to lets say calculate a 3% interest on 386.74 and round that half to even, I would do what? Reinvent math? And if I do reinvent it, I will have the exact same problems I had before, just now I also have a half baked, slow, error riddled resemblance of a math library. – Vatev Jul 28 '15 at 11:20
  • @Vatev: You will always have a rounding problem of some kind or another (even in real-life you have to cut off long decimals *somewhere*). However the rounding problems with floats are quite specific and particularly problematic for monetary calculations, mainly because simple values like 0.1 cannot be expressed accurately in a floating point value. It is very common to get something that should by 0.5 and expect it round up, but the actual float value is something like 0.49999999999999994 and gets rounded down. – Simba Jul 28 '15 at 12:29
  • Demonstration: open your browser's dev console and evaluate the following: `0.5 * 0.1 * 10`. The answer will be `0.49999999999999994`. The solution is to store everything either in integers scaled down to cents or hundreths of a cent, or whatever precision you need. Some languages/libraries offer BCD (binary coded decimal) but that's really just the same thing anyway with the system handling moving the decimal point around for you. Either way, yes, you'll still lose precision if you have awkward percentages to calcualate, but you'll lose it in a way that won't make your auditor freak out. – Simba Jul 28 '15 at 12:41
  • @Simba "Don't use floating point values for money" I'm using decimal for "fee" and "rate", but I need to calculate the companies cut on the fly. So, fee=what the customer is charged. rate=worker's cut from the fee (in percentage, e.g. 60.00), profit=calculated companies gross profit. – user2075371 Jul 30 '15 at 10:19
  • maybe useful? [Best data type for currency values](http://stackoverflow.com/questions/628637/best-data-type-for-currency-values). – Ryan Vincent Aug 06 '15 at 16:15

1 Answers1

1

Others may have alluded to this, but I wanted to let you know my system for handling money calculations in PHP.

I use integers. The thing is that I have each increment represent the highest precision I need. For most of my applications, this is hundredths of a dollar, or one cent. However, you can have it be millionths or whatever you need.

So in practice, with the precision being in hundredths, $.01 is represented as 1, $.10 is represented as 10, $1.00 is represented as 100, and so forth. This really gets rid of the rounding issue as you are going to be manipulating integers only, since the decimal part of any computation will be truncated. This is OK, though, since the integer represents the finest precision you need.

Admittedly, this takes a bit more developing to handle, but rounding should not be one of the issues that crop up.

stubsthewizard
  • 352
  • 2
  • 12
  • This seems good until you need to divide something. Say $10 / 3, which even starting with integers will give you a float back in php 1000/3 – Jeff Puckett Apr 24 '18 at 20:27
  • Hi Jeff. Thank you for your response. It still works, even with division. You will almost always end up with parts of a penny left over no matter what method you use. Even using a decimal, you still end up with $3.33333333 when dividing $10.00 by three. With the method I outlined, you end up with 333.3333333. Either way you have to decide what to do with the extra parts of a penny. Banks generally round up if you owe them, or down, if they owe you. – stubsthewizard Apr 25 '18 at 12:43
  • Stripe does this too (so this must actually be a very legit approach): https://stackoverflow.com/questions/35326710/stripe-currency-are-all-amounts-in-cents-100ths-or-does-it-depend-on-curren – ᴍᴇʜᴏᴠ Oct 07 '20 at 11:15