CODEWITHSUNDEEP
clickhousecockroachdb
Atheer Abdullatif
Atheer Abdullatif

Reputation: 272

mapping TIMESTAMPTZ to clickhouse datatype

I have a TIMESTAMPTZ column in a cockroachDB source, by using clickhouse kafka consumer to read from cockroachDB changefeed i stored the TIMESTAMPTZ fields as DateTime however this resulted with inaccurate data, something of such sort:

1970-01-01 00:00:00

how to map TIMESTAMPTZ to the accurate date type in Clickhosue?

Upvotes: 1

Views: 429

Answers (1)

Xiang Gu
Xiang Gu

Reputation: 21

Welcome to the CRDB community!

I'm not very familiar with changefeed nor clickhouse but I'll try my best to help.

I tried to set up a CRDB changefeed on a table with a TIMESTAMPZ column:

create table t (i int primary key, j timestamptz);
insert into t values (1, now());

The output string of this TIMESTAMPZ columns uses ISO 8601 format:

root@localhost:26257/defaultdb> EXPERIMENTAL CHANGEFEED FOR t;
{"key":"[1]","table":"t","value":"{\"after\": {\"i\": 1, \"j\": \"2023-01-16T14:44:01.337341Z\"}}"}

So following @Denny Crane's lead, it does seem like using best_effort will allow Clickhouse to parse input date/time in ISO 8601 format.

Can you try and let us know whether it helps? If not, I can engage my colleague who have more expertise on this matter to help you.

Upvotes: 2

Related Questions