alibaba/DataX · error · SQLException

TIMESTAMP 类型转换错误：[%s]

Error message

TIMESTAMP 类型转换错误：[%s]

What it means

The TIMESTAMP variant of the same guard in AdsInsertProxy: binding a java.sql.Types.TIMESTAMP parameter, column.asDate() threw DataXException, so the row's value is not interpretable as a timestamp and the proxy raises SQLException 'TIMESTAMP 类型转换错误：[<column>]' including the failing Column.

Source

Thrown at adswriter/src/main/java/com/alibaba/datax/plugin/writer/adswriter/insert/AdsInsertProxy.java:588

                            "TIME 类型转换错误：[%s]", column));
                }

                if (null != utilDate) {
                    sqlTime = new java.sql.Time(utilDate.getTime());
                }
                statement.setTime(preparedPatamIndex + 1, sqlTime);
                break;

            case Types.TIMESTAMP:
                java.sql.Timestamp sqlTimestamp = null;
                try {
                    if ("".equals(column.getRawData())) {
                        utilDate = null;
                    } else {
                        utilDate = column.asDate();
                    }
                } catch (DataXException e) {
                    throw new SQLException(String.format(
                            "TIMESTAMP 类型转换错误：[%s]", column));
                }

                if (null != utilDate) {
                    sqlTimestamp = new java.sql.Timestamp(
                            utilDate.getTime());
                }
                statement.setTimestamp(preparedPatamIndex + 1, sqlTimestamp);
                break;

            case Types.BOOLEAN:
                //case Types.BIT: ads 没有bit
                Boolean booleanValue = column.asBoolean();
                if (null == booleanValue) {
                    statement.setNull(preparedPatamIndex + 1, Types.BOOLEAN);
                } else {
                    statement.setBoolean(preparedPatamIndex + 1, booleanValue);
                }

View on GitHub (pinned to 80ec23d5c5)

Solutions

  1. Take the exact value from the exception text and validate it against the expected timestamp format.
  2. Standardize timestamps upstream to 'yyyy-MM-dd HH:mm:ss' (or configure the reader's format) before the write.
  3. Re-check column order/types between job config and the ADS table after any schema change.
  4. Route repeated offenders to dirty-data handling or convert the target column to VARCHAR if fidelity matters more than type.

Example fix

# before
15/04/2023 10.00.00
# after
2023-04-15 10:00:00
Defensive patterns

Strategy: try-catch

Validate before calling

static final Pattern TS_RE = Pattern.compile("\\d{4}-\\d{2}-\\d{2}[ T]\\d{2}:\\d{2}:\\d{2}(.\\d+)?");
boolean isParseableTimestamp(Column c) {
    String raw = c == null ? null : c.asString();
    return raw == null || "".equals(raw) || TS_RE.matcher(raw.trim()).matches();
}

Try / catch

try {
    proxy.fillStatement(...);
} catch (SQLException e) {
    if (e.getMessage() != null && e.getMessage().contains("TIMESTAMP 类型转换错误")) {
        dirtyCollector.collect(currentRecord, e.getMessage());
        return;
    }
    throw e;
}

Prevention

When it happens

Trigger: A target TIMESTAMP column receives a Column whose asDate() fails - typical with string values like '2023-04-15T10:00:00Z[UTC]' variants the parser rejects, epoch numbers passed as LongColumn in an unexpected unit, or null-adjacent garbage ('null', '--').

Common situations: Mixed-format timestamp exports, timezone-decorated strings, Excel/CSV artifacts ('45123' serial dates), or column misalignment after schema changes on the ADS table.

Related errors

AI-assisted analysis of alibaba/DataX@80ec23d5c5 (2026-08-14). Data as JSON: /api/errors/113ecf0421ad46e9. Report an issue: GitHub.