0

I stored a decimal value 0.57 as number (10,2) in the database. When I retrieve the value on my screen using Ruby on Rails it shows 0.570000000001

kix
  • 3,290
  • 27
  • 39
Mani krishna reddy
  • 17
  • 2
  • Hi Mani, could you post some code? Which dbms you are working with? – Bruno Jun 21 '18 at 09:54
  • Oracle. just printing the value from db. – Mani krishna reddy Jun 21 '18 at 10:13
  • Did you use `BigDecimal` or just a `Float`? How does the database schema for that database column look like? – spickermann Jun 21 '18 at 10:15
  • I just saved it as number(10,2) in the db. I didnt use BigDecimal – Mani krishna reddy Jun 21 '18 at 10:17
  • 4
    This is called a floating point error. It is a general problem in computer science; it's by no means specific to ruby. What are you actually trying to do? Can you post some code? `number(10,2)` is not a complete code sample -- what is `number`? And why does this give you `0.57`? – Tom Lord Jun 21 '18 at 10:40
  • insert into table (price) values (0.57) i retrieved this value on my html page using ruby as <%=price%> instead of printing 0.57 it shows 0.5700000001 @TomLord – Mani krishna reddy Jun 21 '18 at 10:53
  • @Manikrishnareddy Look at the value stored in the database. It's `0.5700000001`. Because it's stored as a floating point number. Again, there's nothing ruby-specific about that problem. – Tom Lord Jun 21 '18 at 11:00
  • I checked in the database. it is stored as 0.57 @TomLord – Mani krishna reddy Jun 21 '18 at 11:02
  • @Manikrishnareddy (This is a mysql link, but the same principle applies to other database types) https://code.rohitink.com/2013/06/12/mysql-integer-float-decimal-data-types-differences/ `Generally, Float values are good for scientific Calculations, but should not be used for Financial/Monetary Values. For Business Oriented Math, always use Decimal.` – Tom Lord Jun 21 '18 at 11:04
  • I don't know what your question is. If it's "why is this happening?", then see the 'duplicate' link. If it's "how can I do solve this problem?", then you need to be more specific about what you're trying to achieve. – Tom Lord Jun 21 '18 at 11:06
  • i just want the value to be printed as 0.57 instead of 0.57000001 . How do i achieve this? – Mani krishna reddy Jun 21 '18 at 11:09
  • 1
    Read those links. For guaranteed "perfect accuracy", use `decimal` instead of `float`. Or maybe if this is a currency, then depending on the database there may be an [even better](https://www.postgresql.org/docs/9.6/static/datatype-money.html) solution. Or maybe you actually want to a always round to 2 decimal places? Use `.round(2)`. You need to be more specific about the actual problem being solved. – Tom Lord Jun 21 '18 at 11:33
  • 1
    @TomLord "(10,2)" looks like the data type to me, i.e. `NUMERIC(10,2)` / `DECIMAL(10,2)` – 10 digits with 2 decimal places. In that case, it's a conversion error between database and Ruby / Rails. – Stefan Jun 21 '18 at 11:41
  • @Manikrishnareddy could you show your table schema / migration, please? It's unclear what "number(10,2)" means. Furthermore, how do you store values to / fetch values from the database? Show some code, please. – Stefan Jun 21 '18 at 11:42
  • table: PRICE_CAL price number(10,2) – Mani krishna reddy Jun 21 '18 at 12:06
  • 3
    @Manikrishnareddy don't post it as a comment, edit your question instead. And provide a little **more details**, please. It seems like you're just posting the bare minimum and we can hardly give a solution based on that. – Stefan Jun 21 '18 at 12:49
  • Can you please tell which class is that data? For example, let's say you have it in the variable `@num`, print its class: `p @num.class` to terminal or `<%= @num.class %>` to browser. I mean if its float you can do `@num.round(2)` – iGian Jun 21 '18 at 14:11
  • in the irb (`2.6.5p114`), running `2 + 0.3 + 0.3` returns `2.5999999999999996`. Certainly a minimal error. It doesn't happen if you do `0.3 + 0.3 + 2`, though. Not quite sure I should `round` when using sum and subtract with 2 position decimals, but I guess I must. – rellampec Sep 30 '22 at 06:41

2 Answers2

1

Instead of saving value as number (10,2), you could save 100 * value as an integer.

In other words, work with cents instead of dollars. This lets you avoid floating point errors.

Eric Duminil
  • 52,989
  • 9
  • 71
  • 124
0

You can use the BigDecimal class, and store it as Decimal or String in database

for example

sum = BigDecimal("0")
10_000.times { sum += BigDecimal("0.0001") }
sum.to_f # => 1.0

another_sum = 0
10_000.times { another_sum += 0.0001 }
another_sum # => 0.9999999999999062
Xuanyu
  • 661
  • 5
  • 12