Truncation of milliseconds when trying to parse DateTime64 strings from NiFi

[jschoefl]

Describe the bug

Using com.clickhouse.jdbc.ClickHouseDriver in Apache NiFi to load DateTime64 formatted strings (yyyy-MM-dd HH:mm:ss.SSS format) into a corresponding DateTime64(3) column results in values being truncated to yyyy-MM-dd HH:mm:ss losing sub-second precision. Even with date_time_input_format set to best effort.

The issue comes up when trying to update driver version from 0.2.4 to versions >=0.3.2 (tested with 0.3.2 and 0.4.6). Earlier versions handled this without any issues. Trying to insert values directly using the driver in java with prepared statement functions like setObject, setString and setTime all worked. Using the parseDateTimeBestEffort function provided by clickhouse itself also truncates these values.

select 'parseDateTimeBestEffort' AS func, parseDateTimeBestEffort('2021-01-01 01:23:45.6789') AS res

Using parseDateTime64BestEffort yields the desired result. My guess is that setting best_effort uses the more non-specific of these functions.

Steps to reproduce

1. Set up DB connection and Json Tree Reader with default values in Apache NiFi
2. Generate a Flowfile with content like:

{
    "URI":"1234567890",
    "TIMESTAMP":"2023-04-05 01:23:45.678"
}

3. Try and insert into CH using PutDatabaseRecord Processor

Expected behaviour

Date inserts into column without truncation.

Configuration

Environment

• Client version: NiFi 1.17.0
• OS: Ubuntu 22.04 (WSL)

ClickHouse server

• ClickHouse Server version: used both 22.2.2.1 / 22.7.2.15
• ClickHouse Server non-default settings, if any: date_time_input_format = best_effort
• CREATE TABLE statements for tables involved:

CREATE TABLE ${database}.NiFiDate64Test
(
    `uri` String,
    `timestamp` DateTime64(3)
)
ENGINE = MergeTree
ORDER BY uri
SETTINGS index_granularity = 8192;