On 2008 April 13 (Sun) 12:43:22pm PDT, Aladdin Lamp? <[EMAIL PROTECTED]> wrote: > Thank you Nicolas for your answer. I understand that an int64 > certainly gives me enough precision in the general case. > > Now what am I supposed to do if the user decides to add a virtual 4 > decimal digits number to another number which has only 2 decimal > digits? I should first identify this and then multiply by 100 the > second number in order to be able to use sqlite built-in operators > and functions... Not so simple, I think... > > And in my case, it is the user who determines the precision he > desires for each number within the application, so I cannot have the > precision fixed once for all like you suggest. > > Aladdin
You may want to look at <http://www2.hursley.ibm.com/decimal/>, which is about "General Decimal Arithmetic." There's a lot of information there about doing decimal arithmetic, both in fixed-size and arbitrary-precision. You can download implementations with very liberal licenses too. >> Date: Sun, 13 Apr 2008 13:41:46 -0500 >> From: [EMAIL PROTECTED] >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] Dealing with monetary huge values in sqlite >> >> On Sun, Apr 13, 2008 at 07:37:33PM +0200, Aladdin Lamp? wrote: >>> In my opinion (please tell me if I'm wrong), your method only works if >>> you want to *display* the values in your column, and if the decimal >>> precision doesn't change form line to line. >>> >>> I would like to be able to perform operations (+, /, %, etc.) and to >>> store intermediary results into other columns (such as x% of the >>> value, etc.), which may have an arbitrary precision, and - only at the >>> end - round the result to cents/pennies etc. This is required into the >>> technical specifications provided by my client, because high precision >>> matters for them. Indeed, an error of 0.01$ on 10,000,000 lines would >>> lead to a significant error at the end... >> >> The proposed method allows you do perform arithmetic operations using >> SQLite's built-in operators and functions. >> >> If the greatest error your customer is willing to accept is, say, one >> part in a million (that is, a millionth of a cent), then using 64-bit >> would allow you to represent values up to ~ 10e13 -- 10 trillion, not so >> much, but perhaps good enough for you. >> >> If the tolerance is more like one in 10,000 ($0.0000001), then you can >> represent up to ~ 1,000 trillion as the largest value. That's still >> pretty small, IMO, but almost certainly good enough for you. >> >> You can adjust where you put the virtual fixed point. If you can't find >> a suitable break then you should consider your arbitrary precision >> extended function function scheme (or a database engine that provides >> the features you need). >> >> Nico _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
