DateTime64 - how to use it?

[Michal Nowikowski]

Hello,

I'm trying to add data to column that has DateTime64 type.

What I'm getting is weird.

I'm using:

ClickHouse client version 20.11.4.13 (official build).
ClickHouse server version 20.11.4 revision 54442.

In the docs I find:

https://clickhouse.tech/docs/en/sql-reference/data-types/datetime64/

     When inserting datetime as an integer, it is treated as an appropriately scaled Unix

     Timestamp (UTC). 1546300800000 (with precision 3) represents '2019-01-01 00:00:00' UTC.

But in the client I get:

:) select toDateTime64(1546300800000, 3)
SELECT toDateTime64(1546300800000, 3)
Query id: 057bc596-653a-434d-a66a-19194d092f94
┌─toDateTime64(1546300800000, 3)─┐
│        1985-07-14 3$:00:00.000 │
└────────────────────────────────┘
1 rows in set. Elapsed: 0.004 sec.

This is 1985, not 2019.

What is wrong?

Regards,

Michal Nowikowski

[Denis Zhuravlev]

DateTime64 is based on Decimal

1546300800000  ~ number of seconds NOT MILLISECONDS.

use Decimal style 1546300800.111 with dot

For inserts always use string form  '2019-01-01 00:00:00.000'

https://github.com/ClickHouse/ClickHouse/issues/17178

[Denis Zhuravlev]

https://clickhouse.tech/docs/en/sql-reference/functions/type-conversion-functions/#tounixtimestamp64milli

https://clickhouse.tech/docs/en/sql-reference/functions/type-conversion-functions/#fromunixtimestamp64milli

[Michal Nowikowski]

Thanks for tips. I solved my problem.

Regards,

Michał