clickhouse dateTime with milliseconds

Question

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

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

multiply by 100 an store it in UInt32/64. How do I use the multiply by 100 and store as UInt32?
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!

I guess you meant `multiply by 1000` – Victor Perov Mar 21 '19 at 08:11 — Victor Perov, Mar 21 '19 at 08:11

score 4 · Answer 1 · answered Nov 21 '21 at 08:13

4

answered Nov 21 '21 at 08:13

user2462865

score 0 · Answer 2 · answered Mar 21 '19 at 08:10

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:

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
```
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
```

2 Answers2