Format Timestamp for DateTime64 Parameter with millisecond precision not working

When setting a Timestamp for a DateTime64 parameter with millisecond precision on a prepared statement the milliseconds are lost when inserting.

This issue occurs for clickhouse-jdbc version 0.3.0

Example:

Create the following table

CREATE TABLE IF NOT EXISTS test (
    date DateTime64(3),
    value Decimal64(8)

And then insert a record with a Timestamp with millisecond precision:

  ClickHouseConnection conn = getConnection();
  PreparedStatement stmt = conn.prepareStatement("INSERT INTO test (date, value) VALUES (?, ?);");
  long date = 1617028535604L; // Mon Mar 29 2021 14:35:35.604
  stmt.setTimestamp(4, new Timestamp(date));
  stmt.executeUpdate();
  stmt.close();
  conn.close();

When selecting the inserted record you will see that milliseconds are lost, the DateTime64 returned by ClickHouse is then 2021-03-29 16:35:35.000 but it should be 2021-03-29 16:35:35.604.

I have pinpointed the problem to the formatTimestamp function

You can see with the following simple example:

    public static void main(String[] args) {
        long date = 1617028535604L; // Mon Mar 29 2021 14:35:35.604
        Timestamp ts = new Timestamp(date);
        String formatted = ClickHouseValueFormatter.formatTimestamp(ts, TimeZone.getTimeZone("UTC"));
        System.out.println("SQL Timestamp: " + ts);
        System.out.println("ClickHouse JDBC Formatted Timestamp: " + formatted);
    }

This will output:

SQL Timestamp: 2021-03-29 16:35:35.604
ClickHouse JDBC Formatted Timestamp: 2021-03-29 14:35:35

But the ClickHouse JDBC Formatted Timestamp should be 2021-03-29 14:35:35.604

Good catch! Currently there's no context for the method to figure out what the column type is. It's DateTime64 in your case, but it could be DateTime32 as well. I left a TODO there to remind myself to implement a client-side prepared statement, which will provide the context in order to format the given timestamp correctly.

For now, before the issue being fixed, you may use setLong or setObject to set unix timestamp as a workaround.

@zhicwu

Hi @zhicwu thanks for the fast reply :-) I'll use the workaround for now.

I understand the problem, you can't know if the field is a DateTime or DateTime64 in the DB from the context of the prepared statement. So perhaps it must be left to the user to provide this information? Perhaps an option is to use a different data type for setting DateTime and DateTime64 on a PreparedStatement?

A small update; using setLong also does not work with a timestamp in milliseconds. The row stored has a date that is far in the future.

Sorry for the late reponse. I can add methods like setDateTime64 but it's not JDBC-compliant :<

What's the version of ClickHouse you're using? I use latest and below test case worked for me:

// the first column is DateTime64(3, 'UTC')
preparedStatement.setLong(1, 1617028535604L); // preparedStatement.setObject(1, 1617028535604L);
...
assertEquals(rs.getObject(1), new Timestamp(1617028535604L));

@zhicwu

Hi @zhicwu thanks for the reply. I was on ClickHouse 20.8 but yesterday I have updated to 21.3 and now I can indeed use setLong thanks :-)

It is a tough issue to solve this one, because of the reasons you already stated. The PreparedStatement interface does not allow distinguishing between DateTime64 and DateTime and there is also no way for the user to provide this input.

The only thing I can think of currently is to provide DateTime64 and DateTime model classes, similar to java.util.sql Timestamp and set those via setObject like:

stmt.setObject(1, new ClickHouseDateTime64(millis));
stmt.setObject(1, new ClickHouseDateTime(seconds));

Alternatively, use ClickHousePreparedStatement instead of PreparedStatement, but then you cant use the standard JDBC way of working with conn.prepareStatement(...)

@zhicwu

Hi @zhicwu another follow-up issue I found is that using setLong to write DateTime64 in batch results in incorrectly ordered millisecond digits. I have opened another issue for that with an example to reproduce. See #612

I imagine this is an encoding issue :-)

@PHameete

Thanks @PHameete, I think it's a good idea of adding ClickHouseDateTime* to distinguish those types. Timestamp can be mapped to DateTime32 to simplify usage. Alternatively, we can convert timestamp to long and let ClickHouse server to figure it out.

Anyway, I'm working on a few PRs to 1) use RowBinary format in query and mutation instead of TabSeparated(text-based); and 2) client-side prepared statement(additional query to get metadata and then start batch insertion etc.). I believe both of them can address the issue but it takes time. I hope I can get back to this next week or so.

Same is the case for micro-seconds precision with DateTime64(6)

If using this driver with tools like KafkaConnect, or some other 3rd party tools where we just drop-in JDBC compliant driver -- currently proposed solutions will not work. Requiring users to use code as a workaround is a bad solution.

@akamensky

@akamensky the only 'proper' solution is if the JDBC driver knows the data type of the target column. So then I guess then it must be implemented as stated by @zhicwu earlier in this thread:

I left a TODO there to remind myself to implement a client-side prepared statement, which will provide the context in order to format the given timestamp correctly.

So the JDBC driver must retrieve the context for the table its writing (column data types) to so it knows how to format the timestamp. And preferably cache this context to prevent too much overhead.

I think it should be implemented in CH ClickHouse/ClickHouse#28300

To summarize what we discussed and items on my list regarding this:

client-side prepared statement(using parse handler) aiming to support simple scenarios only
For example:
- client sends query insert into my_table(c1, c3, c5) values(1, ?, ?) along with 2 parameters
- jdbc driver parses the query to understand its structure, and executes select c3, c5 from my_table where 1=0 Format TabSeparatedWithNamesAndTypes to get column types
- and then jdbc driver executes substituted query like insert into my_table(c1, c3, c5) values(1, 'a', '2021-08-29 12:12:12.123456') accordingly
batch insert optimization to avoid common mistakes
Apparently the parsing and extra query will slow things down, but it can help to improve performance of batch insert. Let's reuse above example here, if we change the query to below, we no longer have to execute many inserts but only one with streamed data:
```
insert into my_table(c1, c3, c5)
select 1 as c1, c3, c5
from input('c3 String, c5 DateTime64(6)')
Format RowBinary
```

named parameter with or without type - try not to be smart and let the caller to decide
Above two are workarounds for clickhouse-jdbc, the JDBC driver. Named parameter on the other hand will be applied to both jdbc driver and the upcoming clickhouse-client, successor of extended API, which I believe is a better way to address the issue.

// localhost:9100
ClickHouseNode server = ClickHouseNode.builder().withPort(ClickHouseProtocol.GRPC).build();
// prefer to use grpc implementation
try (ClickHouseClient client = ClickHouseClient.newInstance(ClickHouseProtocol.GRPC)) {
  // reusable request object, an immutable copy will be made when calling its send() method
  Mutation request = client.connect(server).write().format(ClickHouseFormat.RowBinary);

  Map<String, ClickHouseValue> namedParams = new HashMap<>();
  namedParams.put("c3", ClickHouseStringValue.of("a"));
  namedParams.put("c5", ClickHouseDateTimeValue.of("2021-08-29 12:12:12.123456789"));

  // named parameter without type
  request.sql("insert into my_table(c1, c3, c5) values(1, :c3, :c5)").params(namedParams).send();
  // with type
  request.sql("insert into my_table(c1, c3, c5) values(2, :c3(String), :c5(DateTime64(6))")
    // parameters can also be specified as array / collection
    .params(ClickHouseStringValue.of("a"), ClickHouseDateTimeValue.of(LocalDateTime.now())).send();
  // batch insert using raw data
  request
    .sql("insert into my_table(c1, c3, c5) select 3 as c1, c3, c5 from input('c3 String, c5 DateTime64(6)')")
    .data("/tmp/data.bin").send();
}

parsing errors
Two issues:
1. parsing date time with fraction is not supported(see this) - we probably should try something like below:
```
static final DateTimeFormatter DATETIME_FORMATTER = new DateTimeFormatterBuilder()
        .appendPattern("yyyy-MM-dd HH:mm:ss").appendFraction(ChronoField.NANO_OF_SECOND, 0, 9, true).toFormatter();
```
1. has problem to distinguish parameter placeholder ? and conditional operator ?:(see this), which is a known issue. Named parameter does not have this issue as of now.

Thanks for your work on this! Let me know if I can be of help to you for example by testing.

@PHameete

Thanks for your work on this! Let me know if I can be of help to you for example by testing.

Thanks @PHameete and sorry it took so long :) It definitely helps if you can help to test. I'm currently adding features(e.g. supporting nested date types) to clickhouse-client and clickhouse-grpc-client, but I can upload WIP version if any of you are interested to play with.

I thought about serializing any java.time data type but LocalDate and friends to a number, but that does not play well with different precisions of DateTime64:

CREATE TABLE datetimes
(
    `foo` DateTime,
    `bar` DateTime64(3),
    `baz` DateTime64(9)
)
ENGINE = Memory;

Try to insert integer or float:

INSERT INTO datetimes (foo, bar, baz)
VALUES
(1638607145.111222333, 1638607145.111222333, 1638607145.111222333),
(1638607145111222333, 1638607145111222333, 1638607145111222333);

Look at the results 😞

SELECT foo, bar, baz FROM datetimes;

foo	bar	baz
2021-12-04 08:39:05	2021-12-04 08:39:05.111	2021-12-04 08:39:05.111222272
2009-10-02 03:49:17	2283-11-11 23:47:02.333	2021-12-04 08:39:05.111222333

I thought about serializing any java.time data type but LocalDate and friends to a number, but that does not play well with different precisions of DateTime64

Yes, the implementation on server side is incomplete according to ClickHouse/ClickHouse#29386. Apart from that, we should use decimal instead of float/double for the conversion - it's been implemented.

Back to the original issue, now with the new driver, there are a few options to set millisecond precision:

use setString with millisecond, for example: 2021-12-04 08:39:05.111
for insert, instead of INSERT INTO test (date, value) VALUES (?, ?), you can use INSERT INTO test (date, value) and the driver will figure out column types for you(by issuing a query like select date, value from test where 0). Alternatively, to gain better performance, you may use input function as mentioned in batch insert example at here

Navigation Menu

Repository navigation

Format Timestamp for DateTime64 Parameter with millisecond precision not working #608

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Format Timestamp for DateTime64 Parameter with millisecond precision not working #608

Description

Activity

zhicwu commented on Mar 29, 2021

PHameete commented on Mar 30, 2021

PHameete commented on Mar 30, 2021

zhicwu commented on Apr 2, 2021

PHameete commented on Apr 2, 2021

PHameete commented on Apr 2, 2021

zhicwu commented on Apr 6, 2021

faisalhasnain commented on Jun 15, 2021

akamensky commented on Aug 27, 2021

PHameete commented on Aug 27, 2021

den-crane commented on Aug 28, 2021

zhicwu commented on Aug 30, 2021

PHameete commented on Aug 30, 2021

zhicwu commented on Aug 30, 2021

enqueue commented on Dec 4, 2021

zhicwu commented on Dec 29, 2021

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions