Laravel - Calculations returning unexpected results with decimal/numeric data type (Postgres)

Question

I'm attempting to build a very simple function to convert units to/from a 'Base Unit of Measurement' (ie: various different units to/from KGs), but I'm running into some inconsistent results. When I convert a value to the Base UOM I get the expected value; however, when I convert it back, I'm getting a result that does not match my expectations.

I'm currently using the decimal (numeric) data type, on my Postgres database, but if I convert the quantity column to float, the outcome is inline with what I'm expecting.

Migrations

conversions_table

$table->decimal('lb', 18, 16, true);
// value = 0.4535923700000000

consumtion_table

$table->decimal('quantity', 16, 10);

Code

// Convert LBs to KG
$quantity * $conversions->lb

// Convert KGs to LBs
$quantity / $conversions->lb

Example

If a $value = 0.125 is converted to KGs it results in the function returning 0.0566990463 KGs; therefore, when I run the function in the reverse direction to return the value in KGs back to LBs, it results in 0.12500000011023113

But, if I change the data type to float(), that same function returns 0.05669904625 KGs, which then returns the expected result when converting back to LBs -> 0.125.

Summary

I'm sure this is a 'newbie' mistake and perhaps expected behaviour due to a misunderstanding of these different data types, but I'd be very thankful if someone could clarify what the best approach is tackling this kind of scenario!

Erwin Brandstetter · Accepted Answer · 2020-12-06 20:16:52Z

2

You defined your column consumption.quantity as:

$table->decimal('quantity', 16, 10);

That's a numeric with a precision of 16 and a scale of 10. I.e. 10 fractional decimal digits.

The result of 0.125 * 0.45359237 is 0.05669904625, which requires 11 fractional decimal digits to be represented exactly. Since your column only allows 10, the value is rounded to 0.0566990463, thereby introducing the error. (Or there are additional links in the food chain that (also) round to a scale of 10.)

Current Lavarel documentation.
Quoting the current Postgres manual:

If the scale of a value to be stored is greater than the declared scale of the column, the system will round the value to the specified number of fractional digits.

Solution: Allow more fractional digits. Example with a scale of 16 in your table columns:

$table->decimal('quantity', 20, 16);

Or drop maximum precision and scale altogether and use plain numeric. Or whatever suits your needs.

To be precise, this is slightly incorrect:

If a $value = 0.125 is converted to KGs it results in the function returning 0.0566990463 KGs;

The calculation should return the exact number. It is later rounded to a scale of 10.

edited Dec 6, 2020 at 20:16

answered Dec 6, 2020 at 20:06

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MrThePlague Over a year ago

After all that, it was simply a problem with basic arithmetic. It doesn't look as though Laravel currently has a way of defining just a plain numeric column type so I'll take the approach of adding more fractional digits. Thanks.

Collectives™ on Stack Overflow

Laravel - Calculations returning unexpected results with decimal/numeric data type (Postgres)

Migrations

conversions_table

consumtion_table

Code

Example

Summary

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Migrations

conversions_table

consumtion_table

Code

Example

Summary

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related