date type convert error

[Jkanon]

versions: clickhouse-jdbc 0.3.2 with mybatis3.5.9

exception: DB::Exception: Cannot convert string 2022-03-10 23:59:59.999 to type DateTime (version 20.8.3.18)

issues:
when i use a java date type field to limit the datetime field in the database via mybatis, I found that the exception occurs for versions above 0.3.2, while everything is ok when it downgraded to 0.3.1

[zhicwu]

Thanks for reporting the issue. Could you provide sample code(Java / SQL) to reproduce the issue? It looks like you're inserting timestamp into DateTime32 column.

[zhicwu]

In case anyone else ran into similar issue:

• use parseDateTime*BestEffort* function in your query so that ClickHouse server will do that for you
• use input function or simply insert into <table>(<columns>)(without values expression) for insertion so that the JDBC driver can help

  try (PreparedStatement ps = conn.prepareStatement(
      "insert into mytable select col1, col2 from input('col1 String, col2 DateTime64(3), col3 Int32')")) {
      // the column definition will be parsed so the driver knows there are 3 parameters: col1, col2 and col3
      ps.setString(1, "test"); // col1
      ps.setObject(2, LocalDateTime.now()); // col2, setTimestamp is slow and not recommended
      ps.setInt(3, 123); // col3
      ps.addBatch(); // parameters will be write into buffered stream immediately in binary format
      ...
      ps.executeBatch(); // stream everything on-hand into ClickHouse
  }