Cast milliseconds to DateTime

December 14, 2021

I have Kafka integration objects:

CREATE TABLE topic_kafka
(
    topic_data String
) ENGINE = Kafka()
SETTINGS
    kafka_broker_list = 'kafka:9092',
    kafka_topic_list = 'topic',
    kafka_group_name = 'clickhouse_group',
    kafka_format = 'JSONAsString',
    kafka_num_consumers = 1;

CREATE TABLE topic
(
    time DateTime64(3),
    user_id Int32 NOT NULL,
    version String
) ENGINE = MergeTree()
ORDER BY (user_id, time);

CREATE MATERIALIZED VIEW topic_consumer 
TO topic AS
SELECT
    JSONExtract(topic_data, 'time', 'Int64') as time, 
    toInt32(JSON_VALUE(topic_data, '$.data.user_id')) as user_id,
    JSON_VALUE(topic_data, '$.data.version') as version
FROM topic_kafka;

And Kafka topic of json data with nested objects, like this:

{"time":1639387657456,"data":{"user_id":42,"version":"1.2.3"}}

The problem is that time has values 2282-12-31 00:00:00.000 in the topic table.

It also can be checked with the following query:

select cast (1639387657456 as DateTime64(3)) as dt

But for DML query below implicit date convertation works fine, as the documentation states:

insert into topic (time, user_id) values ( 1640811600000, 42)

I’ve found that such cast works fine too:

select cast (1639387657.456 as DateTime64(3)) as dt

Looks like I’ve missed something from the documentation.

What is the problem with view topic_consumer above? Is it ok to divide milliseconds by 1000 to convert it to DateTime explicitly?

>Solution :

fromUnixTimestamp64Milli

https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions/#tounixtimestamp64nano

select fromUnixTimestamp64Milli(toInt64(1640811600000));
┌─fromUnixTimestamp64Milli(toInt64(1640811600000))─┐
│                          2021-12-29 21:00:00.000 │
└──────────────────────────────────────────────────┘