The Problem: Why last_value() Creates "Ghost" States

When building real-time IoT dashboards or device status cards, a common requirement is to display the current state of a device (e.g., whether it is RUNNING or STOPPED) alongside its physical metrics (e.g., load current).

However, in time-series databases like Apache IoTDB, different metrics are often sampled at different intervals. If you use the last_value() aggregation function across multiple measurements, IoTDB evaluates the last non-null value for each timeseries independently. This can lead to a misleading "synthetic" snapshot where an old load value from a running state is paired with a newly updated "STOPPED" state, making it look like a stopped machine is still drawing power.

The Root Cause

Consider the following timeseries data in Apache IoTDB:

Time          | load_amp | run_state
------------------------------------
1746500400000 | 72.4     | RUNNING
1746500700000 | null     | STOPPED

When you execute this query:

SELECT last_value(load_amp), last_value(run_state) 
FROM root.stof_posts_tree.snapshot.line1.d01;

IoTDB looks back to find the latest non-null value for load_amp (which is 72.4 at 1746500400000) and the latest non-null value for run_state (which is STOPPED at 1746500700000). It then merges them into a single output row:

72.4 | STOPPED

While technically correct from an individual timeseries perspective, this is semantically incorrect for a device status card that needs a unified latest row snapshot.

The Solution: Use ORDER BY time DESC LIMIT 1

To get the true state of the device at its latest active timestamp without mixing old data, you should avoid aggregate functions like last_value(). Instead, perform a raw query aligned by time, sorted in descending order, and limit the result to 1 row.

Run the following query:

SELECT load_amp, run_state 
FROM root.stof_posts_tree.snapshot.line1.d01 
ORDER BY time DESC 
LIMIT 1;

Why This Works

  • Time Alignment: IoTDB naturally aligns queries by timestamp. By selecting the raw fields, it evaluates rows as they actually occurred in time.
  • Sorting and Limiting: ORDER BY time DESC LIMIT 1 forces IoTDB to find the absolute latest timestamp where at least one of the queried measurements was recorded, and returns only that single row.
  • Preserving Nulls: Because it returns a real historical row rather than a synthetic aggregation, any measurement that was not reported at that specific timestamp (like load_amp when the device stopped) will correctly return as null (or empty).

Expected Output

Time          | load_amp | run_state
------------------------------------
1746500700000 | null     | STOPPED

Your application or frontend card API can now safely read this row. If load_amp is null, your UI can render it as 0, --, or handle it appropriately based on the STOPPED state, preventing confusing "ghost" load values.

Alternative: Querying Multiple Devices Simultaneously

If you are querying status cards for multiple devices at once in a single query, you can leverage IoTDB's ALIGN BY DEVICE syntax combined with the same sorting logic:

SELECT load_amp, run_state 
FROM root.stof_posts_tree.snapshot.line1** 
ORDER BY time DESC 
LIMIT 1 
ALIGN BY DEVICE;

This ensures you get the latest true snapshot row for each matching device path individually.