Re: [SLUG] Re: slug] Math Precision

From: Paul M Foster (paulf@quillandmouse.com)
Date: Sun Feb 06 2005 - 11:59:32 EST


On Sun, Feb 06, 2005 at 09:16:44AM -0500, Russ Herrold wrote:

> On Sun, 6 Feb 2005, Paul M Foster wrote:
>
> >I'm using SQLite for some payroll database work, and seeing a lot of
> >rounding errors, or what are really errors caused by trying to store
>
> >Any help?
>
> Not that you are going to like. sqlite does not use SQL'92
> typeing data -- it handles type conversions as it deems
> appropriate, as part of its approach to simplicity. It is
> part of its design.

I was aware of this. Oddly, though the last docs I perused for the
package claimed to store everything as strings, they are apparently
doing some conversions that cause inaccuracies. I just verified that it
actually _does_ store the data as strings. Yet on output, the number
gets mangled. This is most annoying for things like tax rates, which can
go out to four decimal places (and can't therefore be stored as integer
pennies).

>
> As a workaround, working in pennies, always truncating (using
> int() to force all math results to integers), and then only
> 'punctuating' those pennies in $zzz.yy form (although
> maintaned internally as 'zzzyy') will work.
>

I built a money "class" (this is C, not C++) that stores amounts as ints
as you suggest, and does all its math in integers, only converting to
decimals on output. I had hoped to simply store the amounts with decimal
points in the database, and ease my travails, but it appears to be
impossible.

> I first hit this issue back in '74, as I was doing the
> computer work, dividing a legal setlement between a couple
> thousand bricklayers, based on hours worked and wage rate
> seniority rules. We had to go to truncating off fractional
> cents, and getting the other party to concur on how we handled
> the 'breakage'; The movie 'Office Space' ['... excuse me,
> but you seem to have my stapler...', mumbled] also dealt with
> the topic.

Hilarious movie, filmed in Dallas, where I grew up.

Paul

-----------------------------------------------------------------------
This list is provided as an unmoderated internet service by Networked
Knowledge Systems (NKS). Views and opinions expressed in messages
posted are those of the author and do not necessarily reflect the
official policy or position of NKS or any of its employees.



This archive was generated by hypermail 2.1.3 : Fri Aug 01 2014 - 18:40:05 EDT