1.2 in SQLite3 Database Is Actually 1.199999998

I am attempting to store a float in m开发者_运维问答y SQLite3 database using java. When I go to store the number 1.2 in the database, it is actually stored as 1.199999998 & the same occurs for every even number (1.4, 1.6, etc.).

This makes is really diffult to delete rows because I delete a row according to its version column(whose type =float). So this line wont work: "DELETE FROM tbl WHERE version=1.2"

Thats because there is no 1.2 but only 1.19999998. How can I make sure that when I store a float in my SQLite3 DB, that it is the exact number I input?

Don't use a float if you need precise accuracy. Try a decimal instead.

Remember that the 1.2 you put in your source code or that the user entered into a textbox and ultimately ended up in the database is actually stored as a binary value (usually in a format known as IEEE754). To understand why this is a problem, try converting 1.2 (1 1/5) to binary by hand (binary .1 is 1/2, .01 is 1/4) and see what you end up with:

1.001100110011001100110011001100110011

You can save time by using this converter (ignore the last "1" that breaks the cycle at the site —its because the converter had to round the last digit).

As you can see, it's a repeating pattern. This goes on pretty much forever. It would be like trying to represent 1/3 as a decimal. To get around this problem, most programming languages have a decimal type (as opposed to float or double) that keeps a base 10 representation. However, calculations done using this type are orders of magnitude slower, and so it's typically reserved for financial transactions and the like.

This is the very nature of floating point numbers. They are not exact. I'd suggest you either use an integer, or text field to store a version.

You should never rely on the accuracy of a float or a double. A float should never be used for keys in a data base or to represent money.

You should probably use decimal in this case.

Floats are not an accurate data type. They are designed to be fast, have a large range of values, and have a small memory footprint.

They are usually implemented using the IEEE standard

http://en.wikipedia.org/wiki/IEEE_754-2008

As Joel Coehoorn has pointed out, 1.2 is the recurring fraction 1.0011 0011 0011... in binary and can't be exactly represented in a finite number of bits.

The closest you can get with an IEEE 754 float is 1.2000000476837158203125. The closest you can get with a double is 1.1999999999999999555910790149937383830547332763671875. I don't know where you're getting 1.199999998 from.

Floating-point was designed for representing approximate quantities: Physical measurements (a swimming pool is never exactly 1.2 meters deep), or irrational-valued functions like sqrt, log, or sin. If you need a value accurate to 15 significant digits, it works fine. If you truly need an exact value, not so much.

For a version number, a more appropriate representation would be a pair of integers: One for the major version and one for the minor version. This would also correctly handle the sequence 1.0, 1.1, ..., 1.9, 1.10, 1.11, which would sort incorrectly in a REAL column.