Support Convert java.util.Date

[yanye666]

version:

clickhouse:21.7
clickhouse-jdbc 0.3.2-patch11

issue:

Caused by: java.sql.BatchUpdateException: Code: 6, e.displayText() = DB::Exception: Cannot parse string '2023-02-02 18:26:21.363' as DateTime: syntax error at position 19 (parsed just '2023-02-02 18:26:21'): while converting source column '2023-02-02 18:26:21.363' to destination column task_date (version 21.3.4.25)

java.util.Date ，This type of writing needs to be supported，in mybatis，Instead of using the special form ‘input’

related issues

#863

upgrade 0.3.2-patch11

[aadant]

I hit the same issue in an ETL tool called Pentaho

[zhicwu]

Hi @yanye666, is this the issue we discussed on Telegram? You can update your query to use parseDateTimeBestEffort or clean up values expression(e.g. insert into table values(?,now(),?) -> insert into table(a,c) values(?,?)), so that JDBC driver can infer table schema automatically.

--  DB::Exception: Cannot parse string '2023-02-02 18:26:21.363'
select '2023-02-02 18:26:21.363'::DateTime
-- returns 2023-02-02 18:26:21
select parseDateTimeBestEffort('2023-02-02 18:26:21.363')

[zhicwu]

I hit the same issue in an ETL tool called Pentaho

Can you customize insert query to use parseDateTimeBestEffort function as suggested above?

By the way, it's just my personal opinion, that Pentaho Kettle/Data Integration is a dying product, you'd better consider its successor Apache Hop.

[yanye666]

Hi @yanye666, is this the issue we discussed on Telegram? You can update your query to use parseDateTimeBestEffort or clean up values expression(e.g. insert into table values(?,now(),?) -> insert into table(a,c) values(?,?)), so that JDBC driver can infer table schema automatically.

--  DB::Exception: Cannot parse string '2023-02-02 18:26:21.363'

select '2023-02-02 18:26:21.363'::DateTime

-- returns 2023-02-02 18:26:21

select parseDateTimeBestEffort('2023-02-02 18:26:21.363')

I just use
insert into table(a,c) values(?,?))
introduction java.util.date Error occurred

Telegram, you won't reply to me

[aadant]

Can you customize insert query to use parseDateTimeBestEffort function as suggested above?

Nope, I have no control over the ETL code. It also failed on Data Value Lookup using PreparedStatement (workaround toString and create a view or ALIAS columns)

By the way, it's just my personal opinion, that Pentaho Kettle/Data Integration is a dying product, you'd better consider its successor Apache Hop.

100%, this is legacy code. Sending a java.util.Date to a Date field does not seem to be to crazy :-)

[yanye666]

Can you customize insert query to use parseDateTimeBestEffort function as suggested above?

Nope, I have no control over the ETL code. It also failed on Data Value Lookup using PreparedStatement (workaround toString and create a view or ALIAS columns)

By the way, it's just my personal opinion, that Pentaho Kettle/Data Integration is a dying product, you'd better consider its successor Apache Hop.

100%, this is legacy code. Sending a It's crazy not to support java.util.Date It needs to be compatible with this type! to a Date field does not seem to be to crazy :-)

Not supporting java.util.Date is very violent, and this solution of invading SQL is not good.

Later, I will output an abnormal time type, which I have encountered in production, and I am very upset.

@zhicwu

[filimonov]

Workaround: Try to adjust the clickhouse setting

date_time_input_format=best effort (instead of basic which is default)

[yanye666]

解决方法：试调整 clickhouse 设置

date_time_input_format=best effort（而不是默认的基础）

lower version support. Do you need to adjust parameters for an advanced version? I don't think it's reasonable.

[zhicwu]

It also failed on Data Value Lookup using PreparedStatement (workaround toString and create a view or ALIAS columns)

That sounds really painful, any specific query you can share and I'll see if I can help.

Sending a java.util.Date to a Date field does not seem to be to crazy :-)

@aadant, hahaha, very true. The problem is on ClickHouse side. Since it does not support prepared statement like other databases, the JDBC driver has to figure out column types by itself. It either trusts the intention(in this case, java.util.Date is more suitable for DateTime64, not DateTime32 or Date), or try to infer parameter type by parsing the query(when input function or a clean values expression was used). Understood the query and parameters came from Kettle, but there's user defined Java/script step that you can leverage.

Not supporting java.util.Date is very violent, and this solution of invading SQL is not good.
Later, I will output an abnormal time type, which I have encountered in production, and I am very upset.

@yanye666, calm down and don't be sad. Sorry I could not answer all questions in time, as I only work on this project in my spare time. As to the issue, as I explained in above, due to various limitation, you'd better take a workaround instead of counting on a feature being implemented soon.

Sure, feel free to share more cases and I'll try to help.

[aadant]

date_time_input_format=best effort (instead of basic which is default)

thanks, looks like a reasonable workaround (will set it on the user profile that is hit by the issue)

[yanye666]

This is me testing the entry between two versions of different types.
clickhouse version :22.1.3.7

notice:

• in mybatis. mybatis has DateTypeHandler, which is implemented based on setTimeStamp, so java.util.Date is also supported in mybatis
• Note Synchronize the time zone of the server
• When running the test code, be aware that your clickhouse-server has a distributed cluster
• After testing, restore the Settings stated above "date_time_input_format=best_effort", did not work? Is it because the clickhouse-server version has Higher @aadant @filimonov ?

Test	Remark	0.3.1	0.3.2-11patch
testDefault	The test uses the database default	✅	✅
testDefaultNull	Insert null to test the database default	✅	❌ java.sql.SQLException: Cannot set null to non-nullable column #2 [create_time DateTime]
testString	Test insert string	✅	✅
testTimeSecond	Test insert second	✅	❌ Time zone problem exists, org.junit.ComparisonFailure: Expected :2023-02-05 11:42:12 Actual :2023-02-05 19:42:12
testTimeStamp	Test insert {@link Timestamp}	✅	✅
testDate	Test insert {@link Date}	❌ ru.yandex.clickhouse.except.ClickHouseException: ClickHouse exception, code: 6, host: 127.0.0.1, port: 8123; Code: 6. DB::Exception: Cannot parse string 'Sun Feb 05 19:51:12 CST 2023' as DateTime	❌ java.time.format.DateTimeParseException: Text 'Sun Feb 05 19:42:11 CST 2023' could not be parsed at index 0
testLocalDateTime	Test insert {@link LocalDateTime}	✅	✅
testLocalDate	Test insert {@link LocalDate}	✅	✅

see test code ！！！！

`package com.xk.order;

import com.alibaba.druid.pool.DruidPooledConnection;
import com.clickhouse.jdbc.ClickHouseConnection;
import com.clickhouse.jdbc.ClickHouseStatement;
import com.xk.order.constants.Constant;
import org.junit.Assert;
import org.junit.Test;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Date;

public class CkDateTimeTest extends SpringbootTestBase {

static final String TABLE_NAME = "order_detail";
@Resource(name = Constant.CLICKHOUSE_JDBC_DATA_SOURCE_NAME)
DataSource clickHouseDataSource;

DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

//    String custer = "on cluster default_cluster";
String custer = "";

public ClickHouseConnection getClickHouseConnection() throws SQLException {
    DruidPooledConnection connection = (DruidPooledConnection) clickHouseDataSource.getConnection();
    return (ClickHouseConnection) connection.getConnection();
}

/**
 * The test uses the database default
 *
 * @param
 * @return void
 * @author KongQing
 * @date 2023/2/5
 */
@Test
public void testDefault() throws SQLException {

    try (ClickHouseConnection conn = getClickHouseConnection();
         ClickHouseStatement s = conn.createStatement()) {
        s.execute("drop table if exists test_date_time " + custer + ";" +
                "create table test_date_time " + custer + "(customerId UInt32,create_time DateTime default now()) ENGINE = Memory;");
        Date now = new Date();
        try (PreparedStatement ps = conn.prepareStatement("insert into test_date_time(customerId) values(?)")) {
            ps.setObject(1, 10444);
            ps.executeUpdate();
        }
        ResultSet rs = s.executeQuery("select * from test_date_time");
        Assert.assertTrue(rs.next());
        Assert.assertEquals(rs.getInt(1), 10444);

        Assert.assertEquals(rs.getTimestamp(2).toLocalDateTime().format(dateTimeFormatter), simpleDateFormat.format(now));
        Assert.assertFalse(rs.next());
    }
}

/**
 * Insert null to test the database default
 *
 * @param
 * @return void
 * @author KongQing
 * @date 2023/2/5
 */
@Test
public void testDefaultNull() throws SQLException {

    try (ClickHouseConnection conn = getClickHouseConnection();
         ClickHouseStatement s = conn.createStatement()) {
        s.execute("drop table if exists test_date_time " + custer + ";" +
                "create table test_date_time " + custer + "(customerId UInt32,create_time DateTime default now()) ENGINE = Memory;");
        Date now = new Date();
        try (PreparedStatement ps = conn.prepareStatement("insert into test_date_time values(?,?)")) {
            ps.setObject(1, 10444);
            ps.setObject(2, null);
            ps.executeUpdate();
        }
        ResultSet rs = s.executeQuery("select * from test_date_time");
        Assert.assertTrue(rs.next());
        Assert.assertEquals(rs.getInt(1), 10444);

        Assert.assertEquals(rs.getTimestamp(2).toLocalDateTime().format(dateTimeFormatter), simpleDateFormat.format(now));
        Assert.assertFalse(rs.next());
    }
}

/**
 * Test insert string
 *
 * @param
 * @return void
 * @author KongQing
 * @date 2023/2/5
 */
@Test
public void testString() throws SQLException {

    try (ClickHouseConnection conn = getClickHouseConnection();
         ClickHouseStatement s = conn.createStatement()) {
        s.execute("drop table if exists test_date_time " + custer + ";" +
                "create table test_date_time " + custer + "(customerId UInt32,create_time DateTime default now()) ENGINE = Memory;");
        Date now = new Date();
        try (PreparedStatement ps = conn.prepareStatement("insert into test_date_time values(?,?)")) {
            ps.setObject(1, 10444);
            ps.setObject(2, simpleDateFormat.format(now));
            ps.executeUpdate();
        }
        ResultSet rs = s.executeQuery("select * from test_date_time");
        Assert.assertTrue(rs.next());
        Assert.assertEquals(rs.getInt(1), 10444);

        Assert.assertEquals(rs.getTimestamp(2).toLocalDateTime().format(dateTimeFormatter), simpleDateFormat.format(now));
        Assert.assertFalse(rs.next());
    }
}

/**
 * Test insert second
 *
 * @param
 * @return void
 * @author KongQing
 * @date 2023/2/5
 */
@Test
public void testTimeSecond() throws SQLException {
    try (ClickHouseConnection conn = getClickHouseConnection();
         ClickHouseStatement s = conn.createStatement()) {
        s.execute("drop table if exists test_date_time " + custer + ";" +
                "create table test_date_time " + custer + "(customerId UInt32,create_time DateTime default now()) ENGINE = Memory;");
        Date now = new Date();
        try (PreparedStatement ps = conn.prepareStatement("insert into test_date_time values(?,?)")) {
            ps.setObject(1, 10444);
            ps.setObject(2, (int) (now.getTime() / 1000L));
            ps.executeUpdate();
        }
        ResultSet rs = s.executeQuery("select * from test_date_time");
        Assert.assertTrue(rs.next());
        Assert.assertEquals(rs.getInt(1), 10444);
        Assert.assertEquals(rs.getTimestamp(2).toLocalDateTime().format(dateTimeFormatter), simpleDateFormat.format(now));
        Assert.assertFalse(rs.next());
    }
}

/**
 * Test insert {@link Timestamp}
 *
 * @param
 * @return void
 * @author KongQing
 * @date 2023/2/5
 */
@Test
public void testTimeStamp() throws SQLException {
    try (ClickHouseConnection conn = getClickHouseConnection();
         ClickHouseStatement s = conn.createStatement()) {
        s.execute("drop table if exists test_date_time " + custer + ";" +
                "create table test_date_time " + custer + "(customerId UInt32,create_time DateTime default now()) ENGINE = Memory;");
        Timestamp now = new Timestamp(System.currentTimeMillis());
        try (PreparedStatement ps = conn.prepareStatement("insert into test_date_time values(?,?)")) {
            ps.setObject(1, 10444);
            ps.setObject(2, now);
            ps.executeUpdate();
        }
        ResultSet rs = s.executeQuery("select * from test_date_time");
        Assert.assertTrue(rs.next());
        Assert.assertEquals(rs.getInt(1), 10444);
        Assert.assertEquals(rs.getTimestamp(2).toLocalDateTime().format(dateTimeFormatter), simpleDateFormat.format(now));
        Assert.assertFalse(rs.next());
    }
}

/**
 * Test insert {@link Date}
 *
 * @param
 * @return void
 * @author KongQing
 * @date 2023/2/5
 */
@Test
public void testDate() throws SQLException {
    try (ClickHouseConnection conn = getClickHouseConnection();
         ClickHouseStatement s = conn.createStatement()) {
        s.execute("drop table if exists test_date_time " + custer + ";" +
                "create table test_date_time " + custer + "(customerId UInt32,create_time DateTime default now()) ENGINE = Memory;");
        Date now = new Date();
        try (PreparedStatement ps = conn.prepareStatement("insert into test_date_time values(?,?)")) {
            ps.setObject(1, 10444);
            ps.setObject(2, now);
            ps.executeUpdate();
        }
        ResultSet rs = s.executeQuery("select * from test_date_time");
        Assert.assertTrue(rs.next());
        Assert.assertEquals(rs.getInt(1), 10444);
        Assert.assertEquals(rs.getTimestamp(2).toLocalDateTime().format(dateTimeFormatter), simpleDateFormat.format(now));
        Assert.assertFalse(rs.next());
    }
}

/**
 * Test insert {@link LocalDateTime}
 *
 * @param
 * @return void
 * @author KongQing
 * @date 2023/2/5
 */
@Test
public void testLocalDateTime() throws SQLException {
    try (ClickHouseConnection conn = getClickHouseConnection();
         ClickHouseStatement s = conn.createStatement()) {
        s.execute("drop table if exists test_date_time " + custer + ";" +
                "create table test_date_time " + custer + "(customerId UInt32,create_time DateTime default now()) ENGINE = Memory;");
        LocalDateTime now = LocalDateTime.now();
        try (PreparedStatement ps = conn.prepareStatement("insert into test_date_time values(?,?)")) {
            ps.setObject(1, 10444);
            ps.setObject(2, now);
            ps.executeUpdate();
        }
        ResultSet rs = s.executeQuery("select * from test_date_time");
        Assert.assertTrue(rs.next());
        Assert.assertEquals(rs.getInt(1), 10444);
        Assert.assertEquals(rs.getTimestamp(2).toLocalDateTime().format(dateTimeFormatter), now.format(dateTimeFormatter));
        Assert.assertFalse(rs.next());
    }

}

/**
 * Test insert {@link LocalDate}
 *
 * @param
 * @return void
 * @author KongQing
 * @date 2023/2/5
 */
@Test
public void testLocalDate() throws SQLException {
    try (ClickHouseConnection conn = getClickHouseConnection();
         ClickHouseStatement s = conn.createStatement()) {
        s.execute("drop table if exists test_date_time " + custer + ";" +
                "create table test_date_time " + custer + "(customerId UInt32,create_time DateTime default now()) ENGINE = Memory;");
        LocalDate now = LocalDate.now();
        try (PreparedStatement ps = conn.prepareStatement("insert into test_date_time values(?,?)")) {
            ps.setObject(1, 10444);
            ps.setObject(2, now);
            ps.executeUpdate();
        }
        ResultSet rs = s.executeQuery("select * from test_date_time");
        Assert.assertTrue(rs.next());
        Assert.assertEquals(rs.getInt(1), 10444);
        DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        Assert.assertEquals(rs.getTimestamp(2).toLocalDateTime().format(dateTimeFormatter), dateTimeFormatter.format(now));
        Assert.assertFalse(rs.next());
    }

}

}`

[yanye666]

There were other issues after the upgrade

• ClickHouse does not support catalog, please use setSchema instead USE statement does nothing #1160
• Session is locked by a concurrent Remove sessionId before cancelling query #1035

[yanye666]

in mybatis. mybatis has DateTypeHandler, which is implemented based on setTimeStamp, so java.util.Date is also supported in mybatis

According to the previous tests, mybatis should support insertion, but we have a special way to write it, as follows, which will cause an error.

Test	Remark	0.3.1	0.3.2-11patch
testDateByMybatis	insert into test_date_time values(#{customerId},#{date})	✅	✅
insertDateBySelect	insert into test_date_time select #{customerId},#{date}	✅	❌ Cause: java.sql.BatchUpdateException: Code: 6. DB::Exception: Cannot parse string '2023-02-06 14:27:57.357' as DateTime:

[zhicwu]

Thank you @yanye666 for summarizing issues you ran into and providing testing code. Apologize getting back a bit late as I'm still trying to fix an issue I encountered during benchmark over the weekend.

• testDefaultNull - this is caused by the data format change from TabSeparated to RowBinary(not only for performance but also resolving serialization and deserialization issues along with better timezone support), and the latter does not support specifying null for a non-nullable column - null_as_default option was added as a workaround for some but not all cases
• testTimeSecond - this seems related to timezone. The legacy driver does not handle timezone very well.
• testDate - is this the original issue? Let me see if I can enhance the driver a bit, but java.util.Date will be mapped to DateTime64 by default.
• session locked - you may bump driver to 0.4.0 for the fix
• insertDateBySelect - a few workarounds have been mentioned in above, changing user setting or even switching to DateTime64 will help

Since everyone else using mybatis will likely run into the same issues, are you willing to contribution a dialect or so to benefit more users?

[yanye666]

Thanks Your summary and answer,
My test is divided into two parts, the one above is jdbc mode, and the one below is mybatis mode.

on 0.3.1. Inserting clickhouse directly through mybatis has very low performance. So I used jdbc primordial(I don't know if there is any improvement after the upgrade.), which is why I am mainly testing jdbc mode.

Recently, I found your insert example.
insert example

It seems that using jdbc is not the best way. Do you have a test report? If not, I will try to test it later.

I don't think the performance of using mybatis insert in 0.3.2 has improved either.