sql - How best to convert decimal to integer? -

i building financial application ruby on rails 4 , need store values such 0.83423423432534634546 in database (sqlite mysql).

i tried storing these values in database decimals.

however, ran nasty floating point errors, store them integers instead.

how can convert value 0.4457546346354664233443 integer , vice versa?

thanks help.

your financial app can't use sqlite.

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

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

 123456789012345.83423423432534634546 

in sql, arithmetic using decimal , numeric data types done in numerics. arithmetic numeric , float, , you'll float or double in return. (this 1 kind of mishandling.)

this works correctly in mysql (above), fails miserably--no error or warning--in sqlite (below).

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

 123456789012346.0 

sqlite has no numeric or decimal data type¹. , gives 15 digits columns besides text, regardless of tell it.

sqlite> delete test; sqlite> insert "test" values('123456789.123456789012345'); sqlite> select * test; 

 123456789.123457 

declaring column text preserves digits, breaks arithmetic.

sqlite> drop table test; sqlite> create table test (n text); sqlite> insert "test" values('123456789.123456789012345'); sqlite> select n test; 

     123456789.123456789012345 

sqlite> select n*2 test; 

 246913578.246914 

sqlite rounded answer 15 digits.

with rails, use mysql or postgresql in development, test, and production environments. use sqlite when i'm fooling around.

---

1. sqlite has no data types @ all, in database sense. sqlite has it calls storage classes instead. docs