How can I 'trim' a C# double to the value it will be stored as in an sqlite database?

Question

I noticed that when I store a double value such as e.g. x = 0.56657011973046234 in an sqlite database, and then retrieve it later, I get y = 0.56657011973046201. According to the sqlite spec and the .NET spec (neither of which I originally bothered to read :) this is expected and normal.

My problem is that while high precision is not important, my app deals with users inputting/selecting doubles that represent basic 3D info, and then running simulations on them to find a result. And this input can be saved to an sqlite database to be reloaded and re-run later.

The confusion occurs because a freshly created series of inputs will obviously simulate in slightly different way to those same inputs once stored and reloaded (as the double values have changed). This is logical, but not desireable.

I haven't quite come to terms of how to deal with this, but in the meantime I'd like to limit/clamp the user inputs to values which can be exactly stored in an sqlite database. So if a user inputs 0.56657011973046234, it is actually transformed into 0.56657011973046201.

However I haven't been able to figure out, given a number, what value would be stored in the database, short of actually storing and retrieving it from the database, which seems clunky. Is there an established way of doing this?

Answer 1

The answer may be to store the double values as 17 significant digit strings. Look at the difference between how SQLite handles real numbers vs. text (I'll illustrate with the command line interface, for simplicity):

sqlite> create table t1(dr real, dt varchar(25));
sqlite> insert into t1 values(0.56657011973046234,'0.56657011973046234');
sqlite> select * from t1;
0.566570119730462|0.56657011973046234

Storing it with real affinity is the cause of your problem -- SQLite only gives you back a 15 digit approximation. If instead you store it as text, you can retrieve the original string with your C# program and convert it back to the original double.

Answer 2

Double round has an implementation with a parameter that specifies the number of digits. Use this to round to 14 digits (say) with: rval = Math.Round(Val, 14)

Then round when receiving the value from the database, and at the beginning of simulations, ie. So at the values match?

For details:

http://msdn.microsoft.com/en-us/library/75ks3aby.aspx

Another thought if you are not comparing values in the database, just storing them : Why not simply store them as binary data? Then all the bits would be stored and recovered verbatim?

Answer 3

Assuming that both SQL Lite and .NET correctly implement the IEEE specification, you should be able to get the same numeric results if you used the same floating point type on both of the sides (because the value shouldn't be altered when passed from database to C# and vice versa).

Currently you're using 8-byte IEEE floating point (single) (*) in SQL Lite and 16-byte floating-point in C# (double). The float type in C# corresponds to the 8-byte IEEE standard, so using this type instead of double could solve the problem.

(*) The SQL Lite documentation says that REAL is a floating point value, stored as an 8-byte IEEE floating point number.

Answer 4

You can use a string to store the # in the db. Personally I've done what winwaed suggested of rounding before storing and after fetching from the db (which used numeric()).

I recall being burned by bankers rounding but it could just be that didn't meet spec.