Re: [sqlite] Example/recipe for truncating fp numbers

2017-07-12 Thread Keith Medcalf
mailinglists.sqlite.org > Subject: Re: [sqlite] Example/recipe for truncating fp numbers > > Thank you M. Medcalf for your nice explanation. > > In my first post, I gave half of the used solution : when storing a > "truncated value", eventual remaining digits are al

Re: [sqlite] Example/recipe for truncating fp numbers

2017-07-12 Thread David Raymond
alf Of Keith Medcalf Sent: Tuesday, July 11, 2017 9:38 AM To: SQLite mailing list Subject: Re: [sqlite] Example/recipe for truncating fp numbers On Tuesday, 11 July, 2017 07:24, David Raymond <david.raym...@tomtom.com> said: > Not to be the new guy here, but would someone be so good as

Re: [sqlite] Example/recipe for truncating fp numbers

2017-07-12 Thread Jean-Marie CUAZ
Thank you M. Medcalf for your nice explanation. In my first post, I gave half of the used solution : when storing a "truncated value", eventual remaining digits are allso, separately, stored as a whole integer. Both parts are reassembled later when needed (i.e. when doing set agregation).

Re: [sqlite] Example/recipe for truncating fp numbers

2017-07-12 Thread Keith Medcalf
ions (to different values also). > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Jean-Marie CUAZ > Sent: Tuesday, 11 July, 2017 05:35 > To: sqlite-users@mailinglists.sqlite.org > Subject: Re: [sqlite] Example/recipe for

Re: [sqlite] Example/recipe for truncating fp numbers

2017-07-11 Thread Jean-Marie CUAZ
To M. Slavin, Medcalf ans Hipp: Thanks for your attention. Yes, "cast(value * 1000 as integer) / 1000.0" is much better and I will switch to your formula when possible, thanks very much. Thank you for your offer of C functions : a trunc() function is certainely what is needed, but I don't

Re: [sqlite] Example/recipe for truncating fp numbers

2017-07-11 Thread Keith Medcalf
On Tuesday, 11 July, 2017 07:24, David Raymond said: > Not to be the new guy here, but would someone be so good as to explain why > no one else is panicking that the modulo operator is horrifically broken? > In http://www.sqlite.org/lang_expr.html it just says: > "The

Re: [sqlite] Example/recipe for truncating fp numbers

2017-07-11 Thread David Raymond
Not to be the new guy here, but would someone be so good as to explain why no one else is panicking that the modulo operator is horrifically broken? In http://www.sqlite.org/lang_expr.html it just says: "The operator % outputs the value of its left operand modulo its right operand." There's

Re: [sqlite] Example/recipe for truncating fp numbers

2017-07-11 Thread R Smith
On 2017/07/11 2:31 PM, Richard Hipp wrote: Truncation is a string operation, not a mathematical operation. So I suggest using string functions: WITH SRC(Val) AS ( VALUES (0),(1.001),(1.12345678),(1.),(1.888), (9.87654321),(1.5),(1.499),(1.49494999),

Re: [sqlite] Example/recipe for truncating fp numbers

2017-07-11 Thread Richard Hipp
Truncation is a string operation, not a mathematical operation. So I suggest using string functions: WITH SRC(Val) AS ( VALUES (0),(1.001),(1.12345678),(1.),(1.888), (9.87654321),(1.5),(1.499),(1.49494999), (12345.67890123), (1234.56) UNION ALL SELECT

Re: [sqlite] Example/recipe for truncating fp numbers

2017-07-11 Thread R Smith
In case any astute mathematicians were paying attention to this thread - the previous solution I offered would obviously fail on Negative values - which may or may not be a requirement. Either way, I've amended it to work for all values, but I think Keith's method might be better than this

Re: [sqlite] Example/recipe for truncating fp numbers

2017-07-11 Thread R Smith
On 2017/07/11 3:51 AM, Richard Hipp wrote: On 7/10/17, Jean-Marie CUAZ wrote: Hello, Below is a recipe on a "best effort" basis, to truncate fp numbers on the right side of the decimal separator with SQLite. I don't understand how this is different from "round(N,3)"? What

Re: [sqlite] Example/recipe for truncating fp numbers

2017-07-10 Thread Keith Medcalf
Though I would use: trunc(value * pow(10, places)) / pow(10, places) so that all the operations are performed using full floating point, but then I have the whole math library loaded into SQLite3 ... I just added an override for the math library trunc function that takes two arguments so

Re: [sqlite] Example/recipe for truncating fp numbers

2017-07-10 Thread Richard Hipp
On 7/10/17, Jean-Marie CUAZ wrote: > Hello, > > Below is a recipe on a "best effort" basis, to truncate fp numbers on > the right side of the decimal separator with SQLite. I don't understand how this is different from "round(N,3)"? What are you trying to do to the fp number N

Re: [sqlite] Example/recipe for truncating fp numbers

2017-07-10 Thread Keith Medcalf
Why not just use: cast(value * 1000 as integer) / 1000.0 > Hello, > > Below is a recipe on a "best effort" basis, to truncate fp numbers on > the right side of the decimal separator with SQLite. > > It is not intended to correct any fp numbers processing, but only to > discard, without any

Re: [sqlite] Example/recipe for truncating fp numbers

2017-07-10 Thread Simon Slavin
On 10 Jul 2017, at 6:17pm, Jean-Marie CUAZ wrote: > Improvements/comments welcome I would suggest you try an equivalent function, starting by turning the number into a string and looking for the decimal point in it. This may or may not work better, but a second way of

[sqlite] Example/recipe for truncating fp numbers

2017-07-10 Thread Jean-Marie CUAZ
Hello, Below is a recipe on a "best effort" basis, to truncate fp numbers on the right side of the decimal separator with SQLite. It is not intended to correct any fp numbers processing, but only to discard, without any rounding, unwanted fractional digits. The following is directed to