Understanding the Custom TIME Column Issue in Apache IoTDB

Apache IoTDB introduced a robust Table Model that allows developers to define custom time column names instead of relying strictly on the default system time column. For instance, you can define a custom time column like event_at TIMESTAMP TIME during table creation. However, users running Apache IoTDB 2.0.8 have encountered a critical bug: executing SHOW CREATE TABLE omits the custom TIME column entirely from the generated DDL output.

This discrepancy creates severe risks for schema backup and replication jobs. If a standby cluster or disaster recovery environment replays the DDL returned by SHOW CREATE TABLE, the recreated table will lack the essential time column metadata.

Demonstrating the Problem

Consider a standard table creation query utilizing a custom time column name:

CREATE TABLE event_samples (
    device_id STRING TAG,
    event_at TIMESTAMP TIME,
    value DOUBLE FIELD
);

If you populate data into this table and then inspect the table structure using DESCRIBE, the system correctly recognizes all columns, including event_at:

DESCRIBE event_samples;

+------------+-----------+----------+
| ColumnName | DataType  | Category |
+------------+-----------+----------+
| device_id  | STRING    | TAG      |
| event_at   | TIMESTAMP | TIME     |
| value      | DOUBLE    | FIELD    |
+------------+-----------+----------+

However, running SHOW CREATE TABLE event_samples; generates incomplete DDL:

CREATE TABLE "event_samples" ("device_id" STRING TAG,"value" DOUBLE FIELD) WITH (ttl='INF')

Notice that "event_at" TIMESTAMP TIME is missing from the DDL, rendering the exported schema invalid for exact reconstruction.

Root Cause: Metadata Exporter Serialization Bug

Is this a bug in Apache IoTDB 2.0.8? Yes, this is a metadata generation bug.

During the serialization process inside the IoTDB SQL parser and DDL generator, the Table Model engine treats the time column implicitly under certain conditions. When serializing column definitions back into SQL text for SHOW CREATE TABLE, the engine inadvertently filters out columns designated with the TIME category if they use custom identifiers, assuming the default system time semantics without explicitly writing out the custom column name and data type.

How to Work Around and Resolve the Bug

To ensure your schema backup scripts and database migrations remain accurate while using Apache IoTDB, consider the following approaches:

1. Upgrade Apache IoTDB

This bug in the DDL generation logic has been reported and addressed in post-2.0.8 maintenance fixes and master branches. Upgrading to the latest IoTDB patch release will restore complete DDL generation, ensuring custom TIME column definitions are properly included in SHOW CREATE TABLE queries.

2. Combine `DESCRIBE` with Custom Rebuilding Scripts

If you cannot immediately upgrade your IoTDB cluster, update your backup/migration tooling to parse DESCRIBE table_name instead of relying solely on SHOW CREATE TABLE. You can construct a reliable DDL script by reading column definitions directly from DESCRIBE:

  • Extract columns marked with TAG, FIELD, and TIME categories.
  • Format the custom time column explicitly as column_name TIMESTAMP TIME.
  • Reconstruct the full CREATE TABLE DDL programmatically before applying it to standby clusters.

3. Verify Backup Scripts Before Replaying

Always perform automated validation on exported DDL files in a staging environment. Ensure that every table schema generated via automated jobs contains a designated TIME column prior to executing schema restore commands on production or standby databases.