When monitoring industrial equipment or production controllers, tracking counter values over time is a fundamental task. However, when a signed 32-bit integer counter reaches its maximum value and rolls over to its minimum, time-series databases can handle the transition in surprising ways.

If you are using Apache IoTDB and notice that DIFFERENCE outputs 1 while DIFF outputs -4294967295 for the exact same data points during a counter rollover, you are seeing a fundamental design distinction between these two functions.

The Scenario: INT32 Counter Rollover

Consider a simple setup in Apache IoTDB where an INT32 counter transitions from its maximum positive value (2,147,483,647) to its minimum negative value (-2,147,483,648):

CREATE TIMESERIES root.operations_review.edge_lab.counter_a.value WITH DATATYPE=INT32, ENCODING=PLAIN;
INSERT INTO root.operations_review.edge_lab.counter_a(timestamp, value) VALUES (1000, 2147483647);
INSERT INTO root.operations_review.edge_lab.counter_a(timestamp, value) VALUES (2000, -2147483648);

Executing the DIFFERENCE function yields:

SELECT value, DIFFERENCE(value) AS delta FROM root.operations_review.edge_lab.counter_a;

Result: 1

However, running the DIFF function on the exact same dataset:

SELECT value, DIFF(value) AS delta FROM root.operations_review.edge_lab.counter_a;

Result: -4.294967295E9 (or -4294967295)

Why Are the Results Opposite?

Yes, your observation is correct: DIFFERENCE performs subtraction using the native data type (INT32) and experiences standard two's complement integer overflow, while DIFF promotes values to a 64-bit floating-point number (DOUBLE) before calculating the change.

1. How DIFFERENCE Works

The DIFFERENCE function maintains the data type of the input series. When operating on INT32 values, subtraction relies on standard 32-bit signed integer arithmetic under two's complement representation:

-2,147,483,648 - 2,147,483,647 = 1 (under 32-bit integer overflow)

In hexadecimal arithmetic:

  • -2,147,483,648 is represented as 0x80000000
  • 2,147,483,647 is represented as 0x7FFFFFFF
  • 0x80000000 - 0x7FFFFFFF = 0x00000001 (which equals 1)

Because integer rollover wraps naturally around the binary representation, DIFFERENCE automatically reports the actual incremental step count (+1) across the rollover boundary.

2. How DIFF Works

The DIFF function is designed as a generic mathematical function that converts or widens input values into 64-bit precision floating-point numbers (DOUBLE) before performing arithmetic subtraction:

-2147483648.0 - 2147483647.0 = -4294967295.0

Because it operates in double precision, it does not overflow at 32-bit boundaries. It reports the absolute numeric delta between the two data points, resulting in a large negative change.

Which Function Should You Use?

Whether this behavior is intended depends on your application's use case:

  • Use DIFFERENCE for Hardware Counters: If you are monitoring a physical device or PLC counter that naturally rolls over from Max-INT to Min-INT (or 0 to Max-UINT), DIFFERENCE leverages native overflow to give you the correct step count without needing manual conditional logic.
  • Use DIFF for Continuous Metrics: If you are measuring a variable where a sharp drop represents a real metric decrease (e.g., system memory available, temperature, or queue length), DIFF correctly captures the negative change in magnitude.

Summary

The difference in output between DIFFERENCE and DIFF in Apache IoTDB during counter rollovers comes down to data type widening. DIFFERENCE preserves the INT32 data type and relies on standard integer overflow, making it ideal for tracking increment steps on hardware counters. DIFF widens calculations to DOUBLE, making it ideal for true continuous metric deltas.