A double precision floating point value contains about 14.5 to 16 digits of precision WHEN CONVERTED FROM BINARY TO DECIMAL TEXT. However, the double precision number is merely the closest binary approximation of a value as can be encoded in BINARY (base 2) format. Exact DEMAL (base 10) values cannot be represented in binary (base 2) floating point. When you "round off" a floating point value to contain, as you put it, merely 6 digits of precision, what you are doing is throwing away some approximation data and making the value a less precise approximation of your diddled value -- you make it impossible to access the original value or to repeat the rerounding and achieve the same result. You are converting a value which is the best approximation of the value to a worse approximation of a value. That you only want to see 6 digits of displayed resolution is a display problem, not a value storage and retrieval problem.
You are doing the equivalent of taking a beautiful 4096x2048 32-bit CMYK image and compressing it to a 16 KByte JPEG of postage stamp size (which it does by throwing away information such that it becomes permanently inaccessible and permanently irretrievable). When you then "blow up" the JPEG back to the original size you are greeted by pixelated eye-bleed causing crap that bears no resemblance whatsoever to the original data. The same applies to non-lossless audio compression formats as well (nay, all digital audio formats, truth be told). Compressing an analogue signal into a 128kbit mpeg stream (or any other non-lossless format) permanently destroys data content, making it impossible to retrieve the original data (and in the case of audio, leaving only crap that a tone deaf moron using "cheap tinkle" audio equipment could stand -- it makes the rest of us bleed out our ears). So too is "diddling" with double precision floating point numbers causing irretrievable and irreperable damage to the data values contained in them. You are confusing the "data value" (in this case the floating point value) with the display representation for biots (text strings in base-10 decimal representation). Once converted, you are throwing away information which you will never be able to recover ever again. Store and work with ONLY the double value that is provided to you without any tinkering. If you BIOTs require displaying only six digits of precision, then display it to them that way. Do not confuse BIOT display and BIOT input requirements with the internal representation of the data inside a digital computer system. --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. >-----Original Message----- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of Abdul Aziz >Sent: Saturday, 7 February, 2015 07:22 >To: General Discussion of SQLite Database >Subject: Re: [sqlite] Bug in SQLite FLOAT values > >OK, understood, thanks but can anyone explain me I was creating db of >different sensors, I used same methods, but in fields which were FLOAT >were >filling with junk values (after 6 decimal places, see in SENSOR_1) why? >even I was cutting it to 6 decimal places (as shown previously, was then >again converting into FLOAT before insertion), when I used VARCHAR(TEXT), >in different table with same methods, then was getting correct >(formatted, >upto 6 decimal places, see in latitude, longitude) values in SENSOR_99, >please have a look... > >Thank you very much :) > >On Sat, Feb 7, 2015 at 7:39 PM, Igor Tandetnik <i...@tandetnik.org> >wrote: > >> On 2/7/2015 8:47 AM, Abdul Aziz wrote: >> >>> Thanks for reply....but I am now using VARCHARS, then how this is >working? >>> not generating any errors? >>> >> >> When Tim said "Read this", he meant it. http://www.sqlite.org/ >> datatype3.html answers your questions (but only if you read it). >> -- >> Igor Tandetnik >> >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
