CODEWITHSUNDEEP
clickhouse
Arnon Rodman
Arnon Rodman

Reputation: 541

clickhouse dateTime with milliseconds

ClickHouse doesn't support, yet, DateTime with milliseconds.

I saw two possible suggestion regarding fields like: 2019-03-17T14:00:32.296Z

  1. multiply by 100 an store it in UInt32/64. How do I use the multiply by 100 and store as UInt32?

  2. to store milliseconds separately. Is there a way to remove milliseconds from 2019-03-17T14:00:32.296Z => 2019-03-17 14:00:32?

Thanks for your help!

Upvotes: 3

Views: 17894

Answers (2)

user2462865
user2462865

Reputation: 121

Should use the datetime64 type - https://clickhouse.com/docs/en/sql-reference/data-types/datetime64/

Upvotes: 4

Victor Perov
Victor Perov

Reputation: 1764

In my mind, the main idea, why ClickHouse does not support milliseconds in DateTime is worse compression.

Long story short: use DateTime and precession by seconds. If you want to store milliseconds, you can go ahead with two ways:

  1. Store milliseconds separately, so you will have a DateTime with your date, that you could use in all possible DateTime functions, as well as primary keys. And put milliseconds part in separate column with type UInt16. You have to prepare data separately before storing. Depends on what language do you use for preprocess data before storing, it could be different ways to do it. In golang it could be done:

    time.Now().UnixNano() / 1e6 % 1e3

  2. Another way, is to store whole as timestamp. This means you should convert your date to unix timestamp with milliseconds by your own and put it into ClickHouse as Uint64. It also depends on what do you use for preparing inserts. For golang it could like: 

    time.Now().UnixNano() / 1e6

Upvotes: 0

Related Questions