CODEWITHSUNDEEP
sqlruby
Tintin81
Tintin81

Reputation: 10225

How best to convert decimal to integer?

I am building a financial application with Ruby on Rails 4 and I need to store values such as 0.83423423432534634546 in the database (SQLite as well as MySQL).

I tried storing these values in the database as decimals.

However, I ran into some very nasty floating point errors, so I would like to store them as integers instead.

How can I convert a value like 0.4457546346354664233443 to an integer and vice versa?

Thanks for any help.

Upvotes: 0

Views: 521

Answers (2)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95771

Your financial app simply can't use SQLite.

Values of type decimal(m,n) or numeric(m,n) aren't subject to floating-point errors unless they're being mishandled.

create table test (
  -- 15 digits left of the decimal; 20 digits right of the decimal.
  n decimal(35, 20)
);

insert into test values (123456789012345.83423423432534634546);

select * from test;

123456789012345.83423423432534634546

In SQL, arithmetic using decimal and numeric data types is done in numerics. But do arithmetic with a numeric and a float, and you'll get a float or a double in return. (This is one kind of mishandling.)

This works correctly in MySQL (above), but fails miserably--no error or warning--in SQLite (below).

sqlite> create table test (
   ...>   -- 15 digits left of the decimal; 20 digits right of the decimal.
   ...>   n decimal(35, 20)
   ...> );
sqlite>     
sqlite> insert into test values (123456789012345.83423423432534634546);
sqlite>   
sqlite> select * from test;

123456789012346.0

SQLite has no numeric or decimal data type1. And it only gives you 15 digits for columns besides text, regardless of what you tell it. 

sqlite> delete from test;
sqlite> INSERT INTO "test" VALUES('123456789.123456789012345');
sqlite> select * from test;

123456789.123457

Declaring the column to be text preserves all the digits, but breaks arithmetic.

sqlite> drop table test;
sqlite> create table test (n text);
sqlite> INSERT INTO "test" VALUES('123456789.123456789012345');
sqlite> select n from test;

    123456789.123456789012345

sqlite> select n*2 from test;

246913578.246914

SQLite rounded the answer to 15 digits.

With Rails, I use only MySQL or PostgreSQL in development, test, and production environments. I use SQLite only when I'm fooling around.

  1. SQLite has no data types at all, in the database sense. SQLite has what it calls storage classes instead. Docs

Upvotes: 4

sawa
sawa

Reputation: 168269

The answer to your question is: If you want to keep the values with the maximum precision of n digits below the decimal point, then:

  • original value to integer: (value * 10 ** n).to_i
  • integer to original value: integer / (10 ** n).to_f

But if I were you, I would rather follow Sergio's suggestion.

Upvotes: 1

Related Questions