Maybe you should check out http://www.sqlite.org/datatype3.html
A delared type of NUMBER(10,2) has NUMERIC affinity, which means that SQLite will attempt to store (string) values as integers first and floats second before giving up and storing strings. You do realize that there are decimal numbers that have infinite binary expansions? You are also talking presentation (as in formatting) of numeric values as opposed to representation (as in storing/retrieving). The former is best handled in the user interface while the latter is the subject of database engines. Fatihful reproduction of formatting would be possible using TEXT affinity and calling sqlite3_bind_text. Performing arithmetic with these "numbers" would however be tricky, slow and would still not guarantee that calculated values would conform to the desired formatting. -----Ursprüngliche Nachricht----- Von: Paul van Helden [mailto:[email protected]] Gesendet: Montag, 06. Mai 2013 10:05 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Is there a way to select a precision? > > > What do you mean, select precision? The double value you pass to > sqlite3_bind_double() will be used as is. Are you saying you want to > round it first? Then go ahead and do that - I'm not sure what that has > to do with SQLite. > -- > It is an issue with SQLite because the values in NUMBER(10,2) have no effect. Too often I see small values with 15 digits in a table because a double was passed as-is. It is not just about space, it is also about presentation. In engineering we are taught that the number of digits should also tell you the accuracy of the sample, so for example a latitude/longitude obtained from a handheld GPS should be stored with 6 decimal digits (~10cm), the rest is just junk. Since the database does not do this for you, when the programmer knows the accuracy of the sample, he shouldn't be lazy and instead do Round(Longitude*1000000)/1000000 before binding. Of course, when the data is presented it should be properly rounded with zeros added at the end or even zeros replacing digits to the left of the decimal (to indicate precision), but my point is you shouldn't store junk digits in the first place. I love it that you don't have to specify TEXT and NUMBER lengths, but would have preferred that SQLite didn't ignore them when specified and that in a NUMBER(p,s) column, the double is stored as an integer internally if p<=18. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -------------------------------------------------------------------------- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: [email protected] This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
