Hi

i have a table with a column amount, it is float 13,2. now i want to
insert a value of 245770,55. I do that with phpMyAdmin:

INSERT INTO mytable (blah, amount, blah) VALUES (blah, 245770,55, blah)

It is inserted without error, but the amount is slightly different.
when i have a hugh amount like 22500534,65 the value inserted is 22500530.00
if i have a smaller amount like 245648,65 it is 245648,66

Any ideas how that can happen?

Thanks for any help,
Martin Nadoll

Martin Nadoll wrote:
yes.
floating point is not infinitely accuruate.

On 10/5/2011 6:06 AM, Martin Nadoll wrote:
That's because of the way float values are stored in a database (and
used in programs). They are only accurate to about 7 digits.
Additionally, they are internally stored in binary instead of decimal,
so numbers like 0.1 cannot be specified exactly (just like 1/3 cannot in
decimal).

You can use DOUBLE, which will give a higher precision (but still not
exact), or DECIMAL, which will give an exact value (but is not supported
by all languages).

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

You asked for floating point, and you got it. There is no way of
representing 0.1 exactly in binary floating point, so everything
gets rounded a bit. Also, floating point has a limited number
of significant digits, depending on the type you select.

A single-precision IEEE floating point (used on most modern hardware
like PCs and Macs) has 6 decimal digits of precision. (Actually,
it's closer to 6.9 digits. Yes, you can have fractional digits of
precision. So most of the time, you'll get within 7). If you
insert a number like 22500534.00 and get errors of less than +/-
22.5, you have no grounds for complaint. You got an error of 4.65
.. If you insert a number like 245648.65 and get errors of less
than +/- 0.245, you have no grounds for complaint. You got an error
of 0.01 .

If you go to double-precision IEEE floating point (also used on
most modern hardware), that has 15 digits of precision. The problem
won't go away, but it will get smaller. But it takes 8 bytes rather
than 4 to store one. Most things can't be measured to 15 digits,
except time and currency, so it usually has enough significant
digits. I'd recommend double over float for most floating-point
values unless storage is particularly tight or calculation time is
particularly critical. The way math units are built, there may not
be that much of a speed difference anyway.

If you ask for a decimal numeric type (fixed, not floating, point),
you won't get decimal-to-binary roundoff. Not all machines support
this type. Also, depending on the number of digits you ask for,
it may take a lot more storage. On my system, (MySQL 5.5, PC
architecture) "help decimal" says I can get up to decimal(65,30).
Calculations are done with precision of 65 digits. Since it says
it's "packed decimal", I would assume it takes 1 byte to store 2
digits, rounded up, and maybe a bit extra for the sign.

Try DOUBLE or DECIMAL with cast on SELECT.