How to round a big number (precision 19, scale 2) to whole integer?

Question

I want to round a big number to the nearest integer:

Ex1:-26,687,872,856,567,000.32, output should be:-26,687,872,856,567,000

Ex2:-26,687,872,856,567,000.51, output should be:-26,687,872,856,567,001

I am using this expression in jasper report:

Math.round($V(cost})

and have set $V{cost}, both as java.lang.Float and java.lang.Double, but I'm not getting correct results. With Float it displays 2147483647.

Answer 1

Your number -26,687,872,856,567,000.51 has 19 significant decimal digits (= precision).

• java.long.Float supports a precision about 7 decimal digits.

• java.long.Double supports a precision about 16 decimal digits.

Conclusion if you like to round to nearest "whole" value you need to use java.math.BigDecimal

Below you can can see a sample jrxml both how to round but more important how to use pattern in jasper-report. The advantage of using pattern is that you will main precision on numbers when exporting to excel and similar, still displaying only the precision you like. I'm using your number -26,687,872,856,567,000.51 that best shows the problem java.lang.double have (note it is not rounding as you like, due to precision problem)

jrxml code

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="bigNumber" pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="597c0716-df6b-42ec-a7c8-863eb1b7174a">
    <variable name="doubleValue" class="java.lang.Double">
        <variableExpression><![CDATA[-26687872856567000.51]]></variableExpression>
        <initialValueExpression><![CDATA[]]></initialValueExpression>
    </variable>
    <variable name="bigDecimal" class="java.math.BigDecimal">
        <variableExpression><![CDATA[new java.math.BigDecimal("-26687872856567000.51")]]></variableExpression>
    </variable>
    <summary>
        <band height="109">
            <staticText>
                <reportElement x="0" y="0" width="128" height="20" uuid="9c05efb5-8b28-43e4-9e48-6dd198427bcd"/>
                <text><![CDATA[Double (Math.round()):]]></text>
            </staticText>
            <textField>
                <reportElement x="128" y="0" width="302" height="20" uuid="32c78ef2-c9a3-4798-8724-bda1dc123a7a"/>
                <textFieldExpression><![CDATA[Math.round($V{doubleValue})]]></textFieldExpression>
            </textField>
            <staticText>
                <reportElement x="0" y="20" width="128" height="20" uuid="b9664b9f-e215-433d-a50d-29131b2bfdc3"/>
                <text><![CDATA[BigDecimal (setScale()):]]></text>
            </staticText>
            <textField>
                <reportElement x="128" y="20" width="302" height="20" uuid="46fbb5a6-3f30-4b55-a353-4874b6bd72d2"/>
                <textFieldExpression><![CDATA[$V{bigDecimal}.setScale(0, RoundingMode.HALF_UP);]]></textFieldExpression>
            </textField>
            <staticText>
                <reportElement x="0" y="40" width="128" height="20" uuid="873cfaa4-68a7-49e5-9884-d7847dd9e652"/>
                <text><![CDATA[BigDecimal (format):]]></text>
            </staticText>
            <textField pattern="#,##0">
                <reportElement x="128" y="40" width="302" height="20" uuid="8f45a367-d1cd-4699-b921-5b0d1e6abcf2"/>
                <textFieldExpression><![CDATA[$V{bigDecimal}]]></textFieldExpression>
            </textField>
        </band>
    </summary>
</jasperReport>

Output

Conclusion

Change all your declaration's (field, variable ecc) to class java.math.BigDecimal

Answer 2

You commented:

I changed the Float to Double, but still I am not getting the expected output. I think Double can handle 64-bits.

There are several misconceptions here. Consult the manual.

• float, float8 and double precision are synonyms in the Postgres type system. You may be thinking of float4 a.k.a. real, which occupies 4 bytes.

• float is an 8-byte quantity, so "64 bits" is right. But float is still only good for:

15 decimal digits precision

Bold emphasis mine.

Your attempt to store a number with a precision of 19 (19 significant decimal digits) is bound to fail. Try:

SELECT '-26687872856567777.77'::float::numeric;

Result:

-26687872856567800

As you can see, the number got rounded to 15 significant digits.

The solution is in the manual, too:

If you require exact storage and calculations (such as for monetary amounts), use the numeric type instead.

Especially for numbers with more than 15 significant digits.

I can't stress this enough. Use numeric. Then your task is simple:

SELECT round('-26687872856567777.77'::numeric);

Result:

-26687872856567778

Or cast to bigint, the number is rounded automatically. Same result, but different data type:

SELECT '-26687872856567777.77'::numeric::bigint;