Clickhouse Invalid DateTime value on INSERT INTO CSV

0
votes

I have the following table in Clickhouse :

CREATE TABLE mydb.mytable (
   `timestampMs`     DateTime,
   `someId`          String,
   `someValue`       Float64
)

I then use the following code to insert rows in this table :

Connection connection = DriverManager.getConnection("jdbc:clickhouse://" + clickhouseEndpoint, user, passwd);
ClickHouseStatement sth = (ClickHouseStatement) connection.createStatement();

String myRow = "1594806134000,myId1,58.8";
String myfields = "(timestampMs, someId, someValue)";

String myQuery = "INSERT INTO mydb.mytable " + myfields;

sth.write()
    .sql(myQuery)
    .data(new ByteArrayInputStream(myRow.getBytes()), ClickHouseFormat.CSV)
    .send();

The code run without any errors or warnings. But when I check the data stored in clickhouse :

curl -XGET https://my/clickhouse/endpoint/?query=SELECT * FROM mydb.mytable

I get the following output :

2013-07-02 15:49:44      myId1      58.8

Here, the fields someId and someValue have the expected value, but the timestamp field is wrong. The expected value was timestampMs = 2020-07-15 9:42:14

What might be the problem here and what should I do to fix it ?

2
javaclickhouse

2 Answers

1
votes

It looks like you add extra three zeros - should be 1594806134 (the count of seconds from 1970-01-01 00:00:00 for type DateTime):

SELECT toDateTime(1594806134000)
/*
┌─toDateTime(1594806134000)─┐
│       2013-07-08 07:06:24 │
└───────────────────────────┘
*/


SELECT toDateTime(1594806134)
/*
┌─toDateTime(1594806134)─┐
│    2020-07-15 09:42:14 │
└────────────────────────┘
*/
1
votes

By default CH expects DateTime in CSV as "2020-07-15 09:42:14", not a timestamp

Doc: The behavior of parsing incorrect dates is implementation specific. ClickHouse may return zero date, throw an exception or do "natural" overflow.

select toUInt32(1594806134000)
1373267184

select toDateTime(1373267184)
2013-07-08 07:06:24