Re: [sqlite] Currency & SQLite

[Cory Nelson]

On Tue, Jul 5, 2016 at 3:59 PM, Ertan Küçükoğlu
 wrote:
> Hi,
>
> 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)
>
> 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.
>

You can not store those _exact_ values in SQLite as floating-point.
You might need to represent that with something like 1.234562.

But if reading/writing is the _only thing_ you're going to do with
them, that's fine. You can just round the value back to 6 decimal
places once you pull it out, and i'll be just as good. You can even
let SQL sort by them.

What you shouldn't do is let SQLite perform any significant arithmetic
on the value, because although your low precision offers quite a lot
of wiggle room, the error could in theory grow large enough to be a
problem if you're not careful.

-- 
Cory Nelson
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Currency & SQLite

[J Decker]

On Tue, Jul 5, 2016 at 5:14 PM, Darren Duncan <dar...@darrenduncan.net>
wrote:

> Ertan,
>
> You should read https://www.sqlite.org/datatype3.html#section_2 if you
> haven't already.
>
> SQLite has exactly 1 floating-point type, which is what you get no matter
> if you say float/double/etc, and it is 8 bytes, which corresponds to a
> Double in most programming languages.
>
>
the ALU on the CPU has exactly one type too - it uses double internally.

C# has 'decimal' which is a fixed 4 decimal points(err I guess it's
different)
http://stackoverflow.com/questions/618535/difference-between-decimal-float-and-double-in-net

but you're actually in delphi to the database so that doesn't help.
It would be best to keep it in scaled precision of maximum places you want
to keep ( pennies + 4 = 6 )... x * 1 000 000

0.97 * 10  != 0.97 + 0.97 + ... (10 times) ...

... if you count from 0.01 to 1.00' and test each one you'll find at least
one that fails.

But since SQL math is done as IEEE floating point it's not going to be the
same result as the machine nessecarily.



You can't change what SQLite uses other than explicitly not using a
> floating-point type; you have integer, float, blob, text, and that's it.
>
> Therefore, if you use anything other than a double in your app, you will
> be converting when storing or retrieving, which makes things slower and
> introduces loss, hence I say use Double everywhere.
>
> -- Darren Duncan
>
>
> On 2016-07-05 4:34 PM, Ertan Küçükoğlu wrote:
>
>> Hardware that the application will run is ~1Ghz Atom CPU, ~1GB RAM,
>> regular Laptop HDD (no SSD). Time to time, there will be calculations,
>> Network file transfer, and DLL function calls (all three) will be
>> simultaneously running. Application has Timers and Socket listening
>> components on it. My concern is to have a situation which "seems" like an
>> application freeze.
>>
>> On the other hand, will it be any help to increase accuracy to use
>> Currency (8 bytes fixed-point data type) in my application for Price and
>> Total fields, leave Amount field as Double?
>>
>> Thanks.
>>
>> -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 1:20 AM
>> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>> Subject: Re: [sqlite] Currency & SQLite
>>
>> On 2016-07-05 3:02 PM, Ertan Küçükoğlu wrote:
>>
>>> 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.
>>>
>>
>> Do NOT change to using Single, always use Double, end to end.
>>
>> First of all, Double is what SQLite uses internally for floats, per its
>> documentation:
>>
>> "The value is a floating point value, stored as an 8-byte IEEE
>> floating point number."
>>
>> So if you're already using Double in your app then staying that way
>> should maintain accuracy, especially when you want accuracy for more than
>> say 3-5 decimal digits in total.
>>
>> Secondly, for financial applications, accuracy trumps performance.
>> Converting to Single at any time is just going to cause you trouble.
>>
>> And really, are you sure using Single would actually help performance?
>> Have you measured it in a real workload?  More likely on modern devices it
>> won't be any faster and you will have lost your accuracy for nothing.
>>
>> -- 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

Re: [sqlite] Currency & SQLite

[Darren Duncan]

Ertan,

You should read https://www.sqlite.org/datatype3.html#section_2 if you haven't 
already.


SQLite has exactly 1 floating-point type, which is what you get no matter if you 
say float/double/etc, and it is 8 bytes, which corresponds to a Double in most 
programming languages.


You can't change what SQLite uses other than explicitly not using a 
floating-point type; you have integer, float, blob, text, and that's it.


Therefore, if you use anything other than a double in your app, you will be 
converting when storing or retrieving, which makes things slower and introduces 
loss, hence I say use Double everywhere.


-- Darren Duncan

On 2016-07-05 4:34 PM, Ertan Küçükoğlu wrote:

Hardware that the application will run is ~1Ghz Atom CPU, ~1GB RAM, regular Laptop HDD 
(no SSD). Time to time, there will be calculations, Network file transfer, and DLL 
function calls (all three) will be simultaneously running. Application has Timers and 
Socket listening components on it. My concern is to have a situation which 
"seems" like an application freeze.

On the other hand, will it be any help to increase accuracy to use Currency (8 
bytes fixed-point data type) in my application for Price and Total fields, 
leave Amount field as Double?

Thanks.

-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 1:20 AM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Currency & SQLite

On 2016-07-05 3:02 PM, Ertan Küçükoğlu wrote:

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.


Do NOT change to using Single, always use Double, end to end.

First of all, Double is what SQLite uses internally for floats, per its
documentation:

"The value is a floating point value, stored as an 8-byte IEEE floating point 
number."

So if you're already using Double in your app then staying that way should 
maintain accuracy, especially when you want accuracy for more than say 3-5 
decimal digits in total.

Secondly, for financial applications, accuracy trumps performance.  Converting 
to Single at any time is just going to cause you trouble.

And really, are you sure using Single would actually help performance?  Have 
you measured it in a real workload?  More likely on modern devices it won't be 
any faster and you will have lost your accuracy for nothing.

-- Darren Duncan


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Currency & SQLite

[Ertan Küçükoğlu]

Hardware that the application will run is ~1Ghz Atom CPU, ~1GB RAM, regular 
Laptop HDD (no SSD). Time to time, there will be calculations, Network file 
transfer, and DLL function calls (all three) will be simultaneously running. 
Application has Timers and Socket listening components on it. My concern is to 
have a situation which "seems" like an application freeze.

On the other hand, will it be any help to increase accuracy to use Currency (8 
bytes fixed-point data type) in my application for Price and Total fields, 
leave Amount field as Double?

Thanks.

-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 1:20 AM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Currency & SQLite

On 2016-07-05 3:02 PM, Ertan Küçükoğlu wrote:
> 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.

Do NOT change to using Single, always use Double, end to end.

First of all, Double is what SQLite uses internally for floats, per its
documentation:

   "The value is a floating point value, stored as an 8-byte IEEE floating 
point number."

So if you're already using Double in your app then staying that way should 
maintain accuracy, especially when you want accuracy for more than say 3-5 
decimal digits in total.

Secondly, for financial applications, accuracy trumps performance.  Converting 
to Single at any time is just going to cause you trouble.

And really, are you sure using Single would actually help performance?  Have 
you measured it in a real workload?  More likely on modern devices it won't be 
any faster and you will have lost your accuracy for nothing.

-- 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

Re: [sqlite] Currency & SQLite

[Darren Duncan]

On 2016-07-05 3:02 PM, Ertan Küçükoğlu wrote:

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.


Do NOT change to using Single, always use Double, end to end.

First of all, Double is what SQLite uses internally for floats, per its 
documentation:


  "The value is a floating point value, stored as an 8-byte IEEE floating point 
number."


So if you're already using Double in your app then staying that way should 
maintain accuracy, especially when you want accuracy for more than say 3-5 
decimal digits in total.


Secondly, for financial applications, accuracy trumps performance.  Converting 
to Single at any time is just going to cause you trouble.


And really, are you sure using Single would actually help performance?  Have you 
measured it in a real workload?  More likely on modern devices it won't be any 
faster and you will have lost your accuracy for nothing.


-- Darren Duncan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Currency & SQLite

[Ertan Küçükoğlu]

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

Re: [sqlite] Currency & SQLite

[Darren Duncan]

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