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?

Krems

You know, if the Royal McBee microcomputer had done this, Edward Lorenz would never have discovered chaos theory. :) – Mvd

@fostandy: Are those actual values (0.56657011973046234 and 0.56657011973046201) or did you make them up? 0.56657011973046201 is 17 digits, so it's supposed to round trip to the same double value as 0.56657011973046234 (I checked in C and it doesn't.) – Virile

@Rick Regan - 0.56657011973046234 is something I obtained from random.NextDouble(). 0.56657011973046201 is what I obtained once I stored it in an sqlite numeric field and fetched it again. As I understand it (which is limited) the last 2 digits are not guaranteed to round-trip. – Krems

@fostandy: From the point of view of decimal to floating-point to decimal, yes, only up to 15 digits are guaranteed to round-trip. But from the point of view of floating-point to decimal to floating-point, 17 digits or more are supposed to round-trip. I thought you were interested in the latter; that is, getting the same double value back. In any case, those two 17-digit values don't map to the same double-precision value, which makes we wonder if SQLite is doing the conversion to 17 digits correctly. – Virile

@fostandy: I'm not sure which direction you want to round-trip, but I provided an answer below that addresses both. – Virile

@fostandy: I provided a second answer, which is hopefully what you were looking for. – Virile

Answer 1

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?

Lightfingered

Answer 2

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.

Virile

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.

Remanence

A C# float is 4-bytes, a C# double is 8-bytes, so I'm using a 8-byte floating point in C# (double). As such I originally expected the values to be identical after retrieval, but they are not due to the fact that the C# double, while complying with IEEE spec, actually contains additional information. SQLite does not save this information because it is only complies with IEEE spec. – Krems

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.

Demetricedemetris

Answer 5

You can store the double as a string, and by using the round-trip formatting when converting the double to a string, it's guaranteed to generate the same value when parsed:

string formatted = theDouble.ToString("R", CultureInfo.Invariant);

Quantify

Answer 6

If you want the decimal input values to round-trip, then you'll have to limit them to 15 significant digits. If you want the SQLite internal double-precision floating-point values to round-trip, then you might be out of luck; that requires printing to a minimum of 17 significant digits, but from what I can tell, SQLite prints them to a maximum of 15 (EDIT: maybe an SQLite expert can confirm this? I just read the source code and traced it -- I was correct, the precision is limited to 15 digits.)

I tested your example in the SQLite command interface on Windows. I inserted 0.56657011973046234, and select returned 0.566570119730462. In C, when I assigned 0.566570119730462 to a double and printed it to 17 digits, I got 0.56657011973046201; that's the same value you get from C#. 0.56657011973046234 and 0.56657011973046201 map to different floating-point numbers, so in other words, the SQLite double does not round-trip.

Virile