Reputation: 10225
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
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.
Upvotes: 4
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:
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