Why Apache IoTDB ROUND(2.5) Returns 2 (and How to Fix It)

Understanding Banker's Rounding in Apache IoTDB

If you are using the table model in Apache IoTDB and notice that ROUND(2.5) returns 2.0 while ROUND(3.5) returns 4.0, you are experiencing Banker's Rounding (technically known as round half to even). This is the default behavior in many programming languages (like Java and Python) and database engines adhering to the IEEE 754 floating-point standard.

Why does 'Round Half to Even' exist?

Traditional 'round half up' (where .5 always rounds up to the next positive number) introduces a systematic upward bias when summing rounded numbers. By rounding to the nearest even number when dealing with an exact half (.5), the rounding errors tend to cancel each other out over large datasets. This is why it is preferred in statistical, scientific, and financial reporting.

• 2.5 rounds to the nearest even integer: 2
• 3.5 rounds to the nearest even integer: 4
• -2.5 rounds to the nearest even integer: -2

How to Implement 'Round Half Away from Zero' in Apache IoTDB SQL

If your daily reports require standard arithmetic rounding (where 2.5 becomes 3, and -2.5 becomes -3), you can achieve this using a portable SQL mathematical expression with SIGN(), ABS(), and FLOOR().

The universal formula for rounding half away from zero is:

SIGN(value) * FLOOR(ABS(value) + 0.5)

Example Query

Here is how you can apply this logic to your IoTDB table model query:

SELECT 
    time, 
    value, 
    ROUND(value) AS default_round,
    SIGN(value) * FLOOR(ABS(value) + 0.5) AS round_half_away_from_zero
FROM quality_round_half
ORDER BY time;

Expected Output

Applying this formula yields the expected arithmetic rounding results:

Summary

By default, Apache IoTDB's ROUND() function implements the standard half-to-even rounding rule. For applications requiring strict arithmetic rounding away from zero, substituting the default ROUND() with the SIGN(value) * FLOOR(ABS(value) + 0.5) formula is a robust, native SQL workaround.