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 ?

Community · Accepted Answer · 2017-05-23 12:15:50Z

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, 2017 at 12:15

CommunityBot

11 silver badge

answered Dec 13, 2011 at 18:59

DGM

27.1k7 gold badges65 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

equivalent8 Over a year ago

add_column :something, :amount, :decimal, :precision => 15, :scale => 4

Daniel Heath Over a year ago

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 · Accepted Answer · 2014-02-13 03:52:31Z

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, 2014 at 3:52

answered Feb 12, 2014 at 3:27

Daniel Heath

4142 silver badges7 bronze badges

1 Comment

Duncan Bayne Over a year ago

If it makes you feel any better, at least it's not truncating to zero by default. web.archive.org/web/20070626050750/http://www.fluidscape.co.nz/…

Aaron · Accepted Answer · 2011-12-13 18:41:06Z

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, 2011 at 18:41

Aaron

58k11 gold badges123 silver badges142 bronze badges

Comments

McKay · Accepted Answer · 2011-12-13 18:41:11Z

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, 2011 at 18:41

McKay

12.7k7 gold badges58 silver badges81 bronze badges

1 Comment

equivalent8 Over a year ago

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

Collectives™ on Stack Overflow

Rails rounding float number on DB level

4 Answers 4

2 Comments

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related