Rails rounding float number on DB level

Question

I have table with float column (I'm using MySQL, mysql2 gem, everything standard)

create_table :some_table do |t| 
  t.float  :amount 
end

I was playing around in console, when i do

a = SomeTable.new
a.amount = 9999.99
a.save!
#9999.99
a.amount
#9999.99
a.reload
a.amount
#9999.99

everything ok

a = SomeTable.new
a.amount = 9999.999
a.save!
#9999.999
a.amount 
#9999.999
a.reload
a.amount
#10000.00

as you see ruby (or rails ) rounds the numbers.

Can someone explain me why is that? ...or is just me ?

Daniel Heath · Answer 1 · 2014-02-13T03:52:31.677

4

Accepted answer is correct in general (and using decimal instead of float will work around the problem).

However, there's a deeper issue here.

The mysql2 driver isn't specifying the precision it wants mysql to return, so the database is returning truncated results.

You can force mysql to return the full precision by multiplying the amount by a float.

class SomeTable
  default_scope -> { select("some_tables.*, amount * 1.0000000000000000 as amount")}
end

a = SomeTable.new
a.amount = 9999.999
a.save!
#9999.999
SomeTable.last.amount 
#9999.999
SomeTable.unscoped.last.amount
#10000.00

edited Feb 13 '14 at 03:52

answered Feb 12 '14 at 03:27

Daniel Heath

404
2
7

If it makes you feel any better, at least it's not truncating to zero by default. http://web.archive.org/web/20070626050750/http://www.fluidscape.co.nz/?q=node/71 – Duncan Bayne Feb 12 '14 at 03:38

score 4 · Accepted Answer · edited May 23 '17 at 12:15

4

If you want to know all about floats and why they have rounding errors, see What Every Computer Scientist Should Know About Floating-Point Arithmetic.

If you are tying to do currency calculations, don't use float! Use a fixed point data type. If you use rails migrations, the decimal type is what you want as described here.

edited May 23 '17 at 12:15

Community

1
1

answered Dec 13 '11 at 18:59

DGM

26,629
7
58
79

add_column :something, :amount, :decimal, :precision => 15, :scale => 4 – equivalent8 Dec 14 '11 at 18:11
Yes, floating point numbers have rounding errors. However, IEEE floats have no problem accurately representing 9999.999 (or for that matter 9999.99999999999), so it isn't exactly accurate to say that rounding errors are causing this problem. As far as I can tell from testing it, this is an issue with the mysql2 adapter. – Daniel Heath Feb 12 '14 at 03:16

score 1 · Answer 3 · answered Dec 13 '11 at 18:41

1

The MySQL documentation describes float as an "approximate numeric data type"...probably not good for storing money values. Try defining "amount" as a decimal, instead.

answered Dec 13 '11 at 18:41

Aaron

55,518
11
116
132

score 0 · Answer 4 · answered Dec 13 '11 at 18:41

0

It looks like the database is the one doing the number changing.

I believe what's happening here is that the MSSQL table being created has a precision of only two digits below the decimal point (or has a hard time storing precision in decimal form), so anything put in after that is getting rounded by the database, but not the code.

answered Dec 13 '11 at 18:41

McKay

12,334
7
53
76

yeah I guess that's it I'm only annoyed because its rounding f.e. 4567.7889 to 4567.8 and not 4567.789 (or 4567.79) I'm loosing precision – equivalent8 Dec 14 '11 at 10:11

Rails rounding float number on DB level

4 Answers4