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 you can do:
trunc(value, 3)
which does the truncation (towards 0) and maintains precision.
On the other hand, I have never seen any need to truncate a floating point
number, though there are many times when one needs to apply proper rounding
(half-to-even) for which I have written a "roundhe" function ...
Truncation or rounding of course only *ever* applied to "display to user and
discarded" results and never ever applied to intermediates or stored so having
the ability to do this inside the SQLite engine rather than at the application
level is of dubious value ... other than if you need to keep a log or something
that does not participate in further calculations.
Here is the roundhe function to do statistical / stochastic / bankers' /
half-even rounding:
/*
** Define a Statistical (Gaussian/Bankers) rounding function
**
** Round < 0.5 towards zero
** = 0.5 towards even
** > 0.5 away from zero
**
** Implements recommended IEEE round-half-even
*/
SQLITE_PRIVATE void _heroundingFunc(sqlite3_context *context, int argc,
sqlite3_value **argv)
{
int p = 0;
double x, scale, xval, ipart, fpart, sgn;
if ((argc == 0) || (argc > 2))
return;
if (sqlite3_value_type(argv[0]) == SQLITE_NULL)
return;
x = sqlite3_value_double(argv[0]);
if (argc == 2)
{
if (sqlite3_value_type(argv[1]) == SQLITE_NULL)
return;
p = sqlite3_value_int(argv[1]);
p = p > 15 ? 15 : (p < 0 ? 0 : p);
}
scale = pow(10.0, p);
sgn = 1.0;
if (x < 0)
sgn = -1.0;
xval = sgn * x * scale;
if (log10(xval) > 16.0)
{
sqlite3_result_double(context, x);
return;
}
fpart = modf(xval, &ipart);
if ((fpart > 0.5) || ((fpart == 0.5) && (fmod(ipart, 2.0) == 1.0)))
ipart += 1.0;
xval = sgn * ipart / scale;
sqlite3_result_double(context, xval);
}
and I just added one that truncates without rounding:
/*
** Define a floating point truncation function that allows truncation to some
number of decimal places
** Retains 1 ulp precision
*/
SQLITE_PRIVATE void _fptruncateFunc(sqlite3_context *context, int argc,
sqlite3_value **argv)
{
int p = 0;
double x, scale, xval, ipart, fpart, sgn;
if ((argc == 0) || (argc > 2))
return;
if (sqlite3_value_type(argv[0]) == SQLITE_NULL)
return;
x = sqlite3_value_double(argv[0]);
if (argc == 2)
{
if (sqlite3_value_type(argv[1]) == SQLITE_NULL)
return;
p = sqlite3_value_int(argv[1]);
}
scale = pow(10.0, p);
sgn = 1.0;
if (x < 0)
sgn = -1.0;
xval = sgn * x * scale;
fpart = modf(xval, &ipart);
xval = sgn * ipart / scale;
sqlite3_result_double(context, xval);
}
> 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 rounding, unwanted fractional digits.
> >
> > The following is directed to discard all digits of a number after the
> > third fractional position (slight and easy adjustments must be done for
> > different positional truncations) :
> >
> > round( CAST(/expression/ AS INTEGER)
> >
> > + /((expression/ * 1000 % 1000 ) / 1000 ), 3)
> >
> > (round() is used against eventually imprecise representation resulting
> > from the top level addition operation)
> >
> > Caveat : force an eventual INTEGER representation resulting from
> > /expression/ to its equivalent REAL representation (ie : x -> x.0)
> >
> > (not a pb for me)
> >
> > Improvements/comments welcome (and wil be happy when an equivalent
> > function finds its way into SQLite)
> >
> > Thanks.
> >
> > -jm
> >
> >
> >
> >
> >
> >
> > ---
> > L'absence de virus dans ce courrier électronique a été vérifiée par le
> > logiciel antivirus Avast.
> > https://www.avast.com/antivirus
> > _______________________________________________
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
