Android Question Strange problem

[Alex_197]

I have 2 records in the database (see the attached image). I need to show the sum of Payment field and the max of PaidDate field.

SELECT
sum(PaidAmount) As PaidAmount,
Max(PaidDate) As PaidDate
FROM tblBilling
where WeekEndDate='2024-11-16T00:00:00'

The result is shown in the attached screenshot.

But when I run the same sql statement in my code it returns me PaidAmount=503.41999999999996 instead of 503.42.

Why am I getting this result?

 

[Jeffrey Cameron]

You didn't specify what database engine you are using, but I'm assuming it is SQLite. SQLite only stores REAL or INT internally, you could define the column as decimal(100,100) or decimal(10,2) and still put any double value you like in it.

Datatypes In SQLite

If precision is important treat the column as a scaled integer: https://en.wikipedia.org/wiki/Scale_factor_(computer_science)

Eidt - fixed link

 

[Alex_197]

You didn't specify what database engine you are using, but I'm assuming it is SQLite. SQLite only stores REAL or INT internally, you could define the column as decimal(100,100) or decimal(10,2) and still put any double value you like in it.

Rezoom.SQL/doc/Language/Quirks/SQLiteQuirks.md at master · fsprojects/Rezoom.SQL

Statically typechecks a common SQL dialect and translates it to various RDBMS backends - fsprojects/Rezoom.SQL

github.com

If precision is important treat the column as a scaled integer: https://en.wikipedia.org/wiki/Scale_factor_(computer_science)

Yes, I'm using SQLite and my field is Decimal(10,2)

 

[teddybear]

I have 2 records in the database (see the attached image). I need to show the sum of Payment field and the max of PaidDate field.

SELECT
sum(PaidAmount) As PaidAmount,
Max(PaidDate) As PaidDate
FROM tblBilling
where WeekEndDate='2024-11-16T00:00:00'

The result is shown in the attached screenshot.

But when I run the same sql statement in my code it returns me PaidAmount=503.41999999999996 instead of 503.42.

Why am I getting this result?

Use GetString2(0) or GetString("PaidAmount") to get the sum value

Dim  PaidAmount as String
...
PaidAmount=rs.getString2(0) 'rs.GetString("PaidAmount")
log(PaidAmount)
...

 

[Jeffrey Cameron]

Yes, I'm using SQLite and my field is Decimal(10,2)

Simply put, the datatype in SQLite provides an _affinity_ for the column, the actual definition (10 digits, 2 of which are decimal places in your case) it is treated as a comment. You might just as well define the column as "Decimal(Fluffy, Bunnies)." It is incumbent upon you to handle the appropriate rounding when pulling the numbers out.

If you need to do math, or filter by the column I would use a scaled integer.

 

[teddybear]

not this way, but I run a SQL statement to get a sum of 2 rows and if I run this statement in db viewer it works fine and shows me 503.42 but if I run the same SQL from my code it shows 503.41999999999996

As Klaus said, this is a feature of Double，if you want to show it like db viewer，you can format it with NumberFormat or do that like my post #23

 

[Alex_197]

As Klaus said, this is a feature of Double，if you want to show it like db viewer，you can format it with NumberFormat or do that like my post #23

Will do. Thanks.

 

[QSerg]

It is always like that. Use round or change datatype to decimal.

 

[emexes]

Use round or change datatype to decimal.

Or store monetary values in their smallest units ie cents.

Doubles will give you precise cents up to 2**53 cents (about 90 trillion dollars)

Longs will give you up to 2**63 cents (about 1000 times greater than with Doubles)

 

[MicroDrie]

What I notice in this cents rounding issue is that in post 11 the time is stored, but little attention is paid to which time we are actually talking about. Data processing can be performed around the clock and around the world in all kinds of different time zones. In this forum we have already discussed the differences between PC and local database programs and database servers running on the other side of the world where summer time is also used or not. My personal experience is that you should use one reference time in the world as "master" as the time of the transaction and base your calculations on the "slave(s)" somewhere else in the world. If you don't do that, you will definitely run into problems with the transition to the next day and the start and end of summer time. The saying "There are many roads that lead to Rome" applies to conversions. This makes it unpredictable which conversion and storage "route" a program chooses. Only storing the "Master" date and time and the amount as text gives you the ability to have full control over how you perform the conversions and calculations yourself.

 

[QSerg]

Or store monetary values in their smallest units ie cents.

Doubles will give you precise cents up to 2**53 cents (about 90 trillion dollars)

Longs will give you up to 2**63 cents (about 1000 times greater than with Doubles)

Storing in cents require much more making around in surrounding programs. Decimals effectively doing the same, though decimals (I think) is the slowest. Integer (longs) are fastest though. I use floats and round. It related not only to money but also to weight.

 

[emexes]

Storing in cents require much more making around in surrounding programs. Decimals effectively doing the same

True, but 64-bit integers are built-in to most languages, whereas BCD and Currency types are non-standard extensions.

Although the VB and PowerBasic Currency type is actually a 64-bit integer with 4 decimal places, so the amount of mucking around is minimal - just use the same scaling (10000 rather than 100).

Personally I am like you, assuming that by "floats" you mean Double rather than Float. But I remember there was a rounding trap; let me see if I can recreate it...

 

[emexes]

there was a rounding trap

Dim Price As Double = 1035 / 100    '$10.35
Dim GST As Double = Price * 0.10    'Australian tax rate is 10% to keep things simple (no calculator needed)
Log(GST)
Log(GST * 100)
Log(Round2(GST, 2))
Log(Floor(GST * 100 + 0.5) / 100)
Log(Floor(GST * 100 + 0.501) / 100)

Waiting for debugger to connect...
Program started.
1.035
103.49999999999999
1.03
1.03
1.04
Program terminated (StartMessageLoop was not called).

Line 3 is the correct GST = 103-and-a-half cents
Line 4 is a clue that the half-cent is not stored as exactly 0.5 cents
Line 5 is after rounding with B4J Round2() function to 2 decimal places... and is wrong (the half-cent should round up, to $1.04)
Line 6 is after rounding manually the traditional way, by adding half of the least-significant digit, then cutting everything past the least-significant digit
Line 7 is what we used after an eagle-eye customer spotted the problem and showed it to us

 

[QSerg]

True, but 64-bit integers are built-in to most languages, whereas BCD and Currency types are non-standard extensions.

Although the VB and PowerBasic Currency type is actually a 64-bit integer with 4 decimal places, so the amount of mucking around is minimal - just use the same scaling (10000 rather than 100).

Personally I am like you, assuming that by "floats" you mean Double rather than Float. But I remember there was a rounding trap; let me see if I can recreate it...

I meant float in MS SQL terms. in VB term it will be Double. Rounding trap (your next post) happens because it is not really rounding as a such and if you getting fraction of cents and need round them properly you have to write your own round function to be sure it happens in predictable uniform manner. There are much more crap in VB that never happens in proper defined languages like C. For example if you divide integer on integer result will be funny because stupid VB converting integers in variants, divide them and then convert result into integer. So apart of everything else you can forget about performance when you talk about integer vs float. I suspect that Java doing similar and even worse because it is interpreter, kind of.