0

I deal with interest rates in a mysql database. These are stored as a double and are generally to the thousandths decimal place (ex: 4.657) but sometimes more. Sometimes, I need to report on these only to the hundredths place (ex: 4.65) but I cannot round these- I need to have them truncated. So, if my interest rate in the database in 5.6575%, I need to get 5.65%, not 5.66%.

Generally speaking, TRUNCATE(int_rate, 2) will work just fine. This has worked for me with no issues until now.

I am having a problem with the following interest rates: 8.0300%, 8.0400%, 9.0300%, 9.0400%, 10.0300%, 10.0400%... etc. It is only when the interest rate is over 8% and 3 or 4 is in the hundredths place and the rest is 0s.

If 8.04 is in the DB, when I do TRUNCATE(int_rate, 2), it returns 8.03. It also does this if I try to use FLOOR: FLOOR(int_rate*100)/100

If I hard code the number and do not pull it from the DB, it truncates properly: TRUNCATE(8.04, 2) or TRUNCATE(8.0400, 2) returns 8.04, as expected.

4.0400% works just fine! So does 8.0600%! Why is it giving these results? Also, if anyone can suggest how to do this without this error, that would be phenomenal!

MySql: 5.0.95

Charistine
  • 61
  • 1
  • 1
  • 7
  • Floating point is approximate. `8.04` is actually something like `8.0399999999`, so truncating it returns `8.03`. – Barmar Jul 13 '16 at 21:45
  • When you hard-code `8.04`, it's interpreted as `DECIMAL`, not `DOUBLE`, so it doesn't lose precision. Why don't you use a `DECIMAL` type in your table? This is designed for applications that can't lose precision. – Barmar Jul 14 '16 at 15:58

1 Answers1

0

taken from PHP floating point numbers:

Floating point numbers have limited precision. Although it depends on the system, PHP typically uses the IEEE 754 double precision format, which will give a maximum relative error due to rounding in the order of 1.11e-16. Non elementary arithmetic operations may give larger errors, and, of course, error propagation must be considered when several operations are compounded.

Additionally, rational numbers that are exactly representable as floating point numbers in base 10, like 0.1 or 0.7, do not have an exact representation as floating point numbers in base 2, which is used internally, no matter the size of the mantissa. Hence, they cannot be converted into their internal binary counterparts without a small loss of precision. This can lead to confusing results: for example, floor((0.1+0.7)*10) will usually return 7 instead of the expected 8, since the internal representation will be something like 7.9999999999999991118....

Now to a possible fix: Have you tried ROUND instead of TRUNCATE?

patrick
  • 11,519
  • 8
  • 71
  • 80
  • The question is about MySQL, not PHP. The reason is the same, but the last paragraph is not really applicable. – Barmar Jul 13 '16 at 21:46
  • @Barmar... you're right... I removed it... the answer explains the problem, I didn't want to do a cut-n-paste from a website without mentioning the source though... – patrick Jul 13 '16 at 21:47
  • @Barmar: I can't use round because I can't have it rounded legally. If someone has an interest rate of 4.239, I need 4.23, not 4.24. One thing I don't get. With an interest rate of 8.04- int_rate*100 = 804 floor(int_rate*100) = 803 I have to use floor because an int_rate of 4.56789 *100 would be 456.789. – Charistine Jul 14 '16 at 12:58
  • @Charistine I created a table with a `DOUBLE` column, and inserted `8.04` into it. When I did `select int_rate * 100` it printed `803.9999999999999`, not `804`. Does that explain why `FLOOR(int_rate * 100)` is `803`? – Barmar Jul 14 '16 at 15:50
  • truncating or flooring ANY FLOATING POINT could yield these results since the precision of a floating point is not indefinite. Why don't you round at 3 digits and then truncate to 2? That should give you the match you're looking for – patrick Jul 15 '16 at 11:27