Scientific notation problem

Rails
1

We’re developing a system that’s used for tracking company IPO data
and we’re running into a few number storage issues. For large numbers,
i.e. numbers greater than 100 million, MySQL insists on storing these
as scientific notation. I don’t really have a problem with that but
when we want to edit these fields using something like:

<% form_for(@company) do |f| %>

<%= f.text_field :some_large_number_field , :size => 20 %> <%= f.submit "Update" %>

<% end %>

the field is presented to the user with the number in scientific
notation form, not what we want unfortunatly, since the users won’t
understand this. We’ve looked at a bunch of documentation but can only
find info on getting numbers into scientific notation, not the other
way around.
Is there a way to either force MySQL to store the numbers normally or
have them formatted on the way out?

Dale

2

Why not just store the number as a String and convert it to a number in
Ruby?

Chris

3

We’re developing a system that’s used for tracking company IPO data
and we’re running into a few number storage issues. For large numbers,
i.e. numbers greater than 100 million, MySQL insists on storing these
as scientific notation.

What’s wrong with DECIMAL date type?

mysql> create table t(n decimal(12,2));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t values(1234567890.11);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t;
±--------------+
| n |
±--------------+
| 1234567890.11 |
±--------------+
1 row in set (0.00 sec)

Regards,

Rimantas
http://rimantas.com/

4

100 Million isn’t that large, certainly not for a float. There are so
many issues with doing it this way I don’t know where to start. Thanks
for the suggestion though.

5

Because decimals have fixed decimal places, which just doesn’t work
with this kind of financial data - not all numbers are going to be
dollar and cent amounts.

6

To clarify this even further. Can you let us know what kind of object
you
are using to store the value in Ruby and what type you are using for the
row
in MySQL?

7

Because decimals have fixed decimal places, which just doesn’t work
with this kind of financial data - not all numbers are going to be
dollar and cent amounts.

Well it shouldn’t be dollar and cents, you can define how many decimal
places you want.
Other types will give you more rounding errors, is that good for
financial data?

Regards,
Rimantas

http://rimantas.com/

8

If it’s money you are talking about, use the Money gem. It takes care
of lots of the syntax and conversion and accuracy for you. It actually
stores the values as very large integers (i.e. cents).

http://dist.leetsoft.com/api/money/

-Danimal

9

Let me clarify this question. I’m not looking for other storage types,
I’m looking for an answer as to

  1. Why MySQL would be forcing some of the large numbers into
    scientific notation and if there’s a way to stop it from doing that.
  2. Baring that, is there a simple way to force the formatting back
    into standard notation for editing?

Thanks
Dale

10

I don’t know how to attack MySQL. Sorry.

When you pull the info from the database, does @number.to_f give you
scientific still?

-Kyle