Hello Darren,
Thanks for your suggestions. Sorry, my e-mail client is not capable of
indenting messages the way yours does. Just bear with my e-mails, please.
I am communicating with a device which actually prints commercial receipt. That
device is already confirmed by Ministry of Finance. What I am doing will be
linked to VAT. Not that much directly, but still there will be a relation for
the company which uses my software.
This device, I am communicating thru a C# DLL. I have to provide amount and
price separately to the functions. Device uses banker's rounding to calculate
total which is printed on the receipt. Device stores values in itself, I store
my values in the SQLite database. My database stored values have to be
identical with the device stored values.
There are some sectors using that device which uses high decimal digits for
amount values. That's why I want to store amount with high decimal digits.
Price on the other hand is fixed with 2 decimal digits.
However, since I have to be identical with the device;
Amount_device * price_device = total_device
Total_device = banker's rounding to 2 decimal digits
Total_device have to be identical to total_SQLite_database
I am using Delphi 10 for programming. Current variables are defined as Double
(8 bytes real), saved in Database using something
"DataSet.FieldByName('Amount').AsFloat := My_double_variable" However, I am now
considering to convert all Double datatypes to Single (4 bytes real) for
performance issues.
There will be reports generated using data stored in SQLite database. I will
depend on using SQL, mostly SUM() will be run on these fields that is expected
to be same with device itself stored values.
I do want to store Dollars because, I realized that I might be doing something
wrong with SQLite field definitions, today. Application is about 95% complete
as to interaction with database. Unfortunately, converting everything in the
application code to use Cents instead of Dollars might introduce me quite a
good number of bugs.
Regards,
Ertan Küçükoğlu
-----Original Message-----
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Darren Duncan
Sent: Wednesday, July 6, 2016 12:21 AM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Currency & SQLite
Hello Ertan,
On 2016-07-05 1:59 PM, Ertan Küçükoğlu wrote:
> I have following data to store:
>
> My currency values only has 2 decimal digits. (Sample: 1.23)
>
> My amount values goes around 5-6 decimal digits. (Sample: 1.123456)
So what do the 2 fields actually represent? Are Amount and Money both amounts
of currency? If so, why are they stored to different precisions? The field
names should be more precise.
> If I am to use below SQL command:
>
> CREATE TABLE IF NOT EXISTS ACCOUNTING(
>
> ID Integer NOT NULL PRIMARY KEY AUTOINCREMENT,
>
> AmountField float not null,
>
> MoneyField float not null
>
> );
>
> Will it be fine to store these values, and I will retrieve exactly
> what I save?
>
> Stored 1.123456 and retrieved 1.123456 exactly, no rounding or anything.
A float field will never store a fractional decimal value exactly, what it will
store is just a binary approximation. However, if the precision you need is
low enough relative to the size of the float then it may appear to work.
A key thing here is how you represent the values while they are being
transmitted between SQLite and your application. If you are storing them using
runtime-bound placeholders rather than literal SQL, and your placeholders are
also floats of the same precision SQLite uses, then you can be guaranteed that
whatever your application gave to SQLite, it will get back. If the values are
inserted as part of the SQL string then it will be rounded.
Its also important to know if you are doing any math with these values in SQL,
eg sums, or if you are just storing and retrieving. Inexact values would bite
you mainly if you do math.
> One thing I cannot do in my case is to store money values as integers
> multiplied by 100.
And why not? That often is still the best advice.
Really its a matter of conception, what is the unit of the currency?
If say your currency was dollars, just say that the field is storing the number
of cents rather than the number of dollars, and do all your math assuming it is
adding/multiplying/etc counts of cents. Nothing wrong with that. Cents are
still just as valid currency in the real world.
Another thing you can do is store the numbers as text fields instead, which
works perfectly with situations where you input them literally in a SQL string;
in appearance you can put the decimal point whereever you want and the database
will remember it exactly, this even works with hundreds of decimal digits or
something over the bounds of the numeric types. But if you do math in the
database then you may get rounding errors, but if you do math in your app then
your app may have an exact numeric type.
> I appreciate all advises.
>
> Thanks.
>
> Ertan Küçükoğlu
-- Darren Duncan
_______________________________________________
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
