316

Sometimes, Activerecord data types confuse me. Err, often. One of my eternal questions is, for a given case,

Should I use :decimal or :float?

I've often come across this link, ActiveRecord: :decimal vs :float?, but the answers aren't quite clear enough for me to be certain:

I've seen many threads where people recommend flat out to never use float and always use decimal. I've also seen suggestions by some people to use float for scientific applications only.

Here are some example cases:

  • Geolocation/latitude/longitude: -45.756688, 120.5777777, ...
  • Ratio/percentage: 0.9, 1.25, 1.333, 1.4143, ...

I have used :decimal in the past, but I found dealing with BigDecimal objects in Ruby was unnecessarily awkward as compared to a float. I also know I can use :integer to represent money/cents, for example, but it doesn't quite fit for other cases, for example when quantities in which precision could change over time.

  • What are the advantages/disadvantages of using each?
  • What would be some good rules of thumb to know which type to use?
strivedi183
  • 4,749
  • 2
  • 31
  • 38
Jonathan Allard
  • 18,429
  • 11
  • 54
  • 75

3 Answers3

477

I remember my CompSci professor saying never to use floats for currency.

The reason for that is how the IEEE specification defines floats in binary format. Basically, it stores sign, fraction and exponent to represent a Float. It's like a scientific notation for binary (something like +1.43*10^2). Because of that, it is impossible to store fractions and decimals in Float exactly.

That's why there is a Decimal format. If you do this: 

irb:001:0> "%.47f" % (1.0/10)
=> "0.10000000000000000555111512312578270211815834045" # not "0.1"!

whereas if you just do

irb:002:0> (1.0/10).to_s
=> "0.1" # the interprer rounds the number for you

So if you are dealing with small fractions, like compounding interests, or maybe even geolocation, I would highly recommend Decimal format, since in decimal format 1.0/10 is exactly 0.1.

However, it should be noted that despite being less accurate, floats are processed faster. Here's a benchmark:

require "benchmark" 
require "bigdecimal" 

d = BigDecimal.new(3) 
f = Float(3)

time_decimal = Benchmark.measure{ (1..10000000).each { |i| d * d } } 
time_float = Benchmark.measure{ (1..10000000).each { |i| f * f } }

puts time_decimal 
#=> 6.770960 seconds 
puts time_float 
#=> 0.988070 seconds

Answer

Use float when you don't care about precision too much. For example, some scientific simulations and calculations only need up to 3 or 4 significant digits. This is useful in trading off accuracy for speed. Since they don't need precision as much as speed, they would use float.

Use decimal if you are dealing with numbers that need to be precise and sum up to correct number (like compounding interests and money-related things). Remember: if you need precision, then you should always use decimal.

Jonathan Allard
  • 18,429
  • 11
  • 54
  • 75
Iuri G.
  • 10,460
  • 4
  • 22
  • 39
  • 1
    So if I understand correctly, float is in base-2 whereas decimal is in base-10? What would be a good use for float? What does your example do, and demonstrate? – Jonathan Allard Dec 15 '11 at 20:30
  • no. both of them are base 2. The difference is the structure. My example demonstrates that 1.0/10 is 0.1 when represented by decimal data structure and 0.100000000000000005 when represented by float data structure. So if you are working with numbers that need high precision than using Float will be a mistake. – Iuri G. Jan 04 '12 at 16:19
  • When should one use Float, and when Decimal? – Jonathan Allard Oct 22 '12 at 18:34
  • @jonallard use float when you need speed and dont care about precision too much. for example some scientific simulations and calculations only care up to 3 or 4 significant digits. Since they dont need precision as much as speed, you would use float. but if you are dealing with numbers that need to be precise and sum up to correct number (like compunding interests and money related things), then you should always use decimal. – Iuri G. Oct 22 '12 at 20:36
  • @luri G. The answer is nice. But the example does not clarify how BigDecimal work. If I do this, "%.47f" % (BigDecimal.new(1)/10), I get the same result as if I use float anyway. – Phương Nguyễn May 31 '14 at 07:08
  • 1
    Don't you mean `+1.43*2^10` rather than `+1.43*10^2`? – Cameron Martin Jun 11 '14 at 17:04
  • @CameronMartin No. I was giving example of scientific notation in decimal. Basically an example of scientific notation in case you are not aware of what it is. – Iuri G. Jun 11 '14 at 18:20
  • 68
    For future visitors, the best data type for currency is integer, not decimal. If the precision of the field is pennies then the field would be an integer in pennies (not a decimal in dollars). I worked in the IT department of a bank and that is how it was done there. Some fields were in higher precision (such as hundredths of a penny) but they were still integers . – adg Aug 24 '16 at 21:22
  • if they are both base 2 then there will always be a rounding error. kinda like representing 1/3 in base 10 isn't it? – BenKoshy Aug 30 '16 at 23:17
  • Mostly correct. Its not so much that it can be incorrect because it stores things as fractions. If it stored in fractions it could be as accurate as necessary by using an x/10^y type fraction. Rather that the "fraction" in question is a binary number limited by the word size and this can have counter-intuitive effects when rounded to the available word size. Decimals however store numbers in a more textual fashion with a *specified* number of decimal places. – Shayne Sep 12 '16 at 07:00
  • 1
    @adg is right : bigdecimal is also a poor choice for currency. – Eric Duminil Jan 30 '17 at 09:13
  • 1
    @adg you're right. I've been working with some accounting and financial applications over the past few years and we store all our currency fields in integer columns. It's much safer for this cases. – Guilherme Lages Santos Nov 06 '18 at 14:05
  • For future visitors, @adg is right - unless you're implementing cryptocurrencies, the amounts involved to express amounts in "pennies" for ETH for example (1 wei = 10^-18 ETH) involved is just not possible to do with an integer, or a bigint. – bbozo Feb 10 '20 at 18:22
  • Remember ETH uses a 32-byte int to hold its amount. – bbozo Feb 10 '20 at 18:52
  • 2
    The advice that decimal is a poor choice for currency is either outdated or wrong: In DBs like postgres, they are exact precision and work exactly like integers except that they give you the correct decimal placement out of the box. If you use ints for say, currency, that just means you now put the onus on the application to manage scaling the amount properly. It's perfectly acceptable to model currency in a decimal column with a scale of 2. See https://www.postgresql.org/docs/9.1/datatype-numeric.html. They even say there that "Integers can be considered to have a scale of zero" for decimals. – AndrewKS Jun 13 '22 at 17:38
21

In Rails 3.2.18, :decimal turns into :integer when using SQLServer, but it works fine in SQLite. Switching to :float solved this issue for us.

The lesson learned is "always use homogeneous development and deployment databases!"

ryan0
  • 1,482
  • 16
  • 21
17

In Rails 4.1.0, I have faced problem with saving latitude and longitude to MySql database. It can't save large fraction number with float data type. And I change the data type to decimal and working for me. 

  def change
    change_column :cities, :latitude, :decimal, :precision => 15, :scale => 13
    change_column :cities, :longitude, :decimal, :precision => 15, :scale => 13
  end
Rokibul Hasan
  • 4,078
  • 2
  • 19
  • 30
  • I save my :latitude and :longitude as floats in Postgres, and that works just fine. – Scott W Nov 23 '15 at 16:07
  • 4
    @Robikul: yes, that's good, but overkill. `decimal(13,9)` is sufficient for latitude and longitude. @ScottW: I don't recall, but if Postgres uses IEEE floats, it only "works fine" because you haven't run into problems... YET. It is an insufficient format for latitude and longitude. Yo will eventually have errors in the least significant digits. – Lonny Eachus Jun 24 '16 at 20:03
  • @LonnyEachus what makes IEEE floats insufficient for lat/longs? – Alexander Suraphel Nov 21 '16 at 08:13
  • 5
    @AlexanderSuraphel If you're using decimal latitude and longitude, an IEEE float is susceptible to errors in the least significant digits. So your latitude and longitude might have precision of 1 meter for example, but you might have errors of 100 meters or more. This is especially true if you're using them in calculations. – Lonny Eachus Dec 04 '16 at 05:23