Clickhouse 支持毫秒 纳秒数据

在clickhouse winter/Spring 新功能介绍上说clickhouse支持了毫秒和微妙的存储和计算，来验证和测试下：

DateTime64
Date and Time with subsecond resolution.
DateTime64(3) — milliseconds.
DateTime64(6) — microseconds.

时间范围：1970–2106.

由于暂时不知道clickhouse如何生成微妙和毫秒 使用mysql查询：
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.20    |
+-----------+
1 row in set (0.00 sec)

mysql> select now(6);
+----------------------------+
| now(6)                     |
+----------------------------+
| 2020-07-12 00:39:14.134716 |
+----------------------------+
1 row in set (0.00 sec)

在clickhouse中创建一个表用于测试：

clickhouse 的版本：
Clickhouse> select * from build_options limit 6;

SELECT *
FROM build_options
LIMIT 6

┌─name─────────────┬─value────────────────────────────────────┐
│ VERSION_FULL     │ ClickHouse 20.5.2.7                      │
│ VERSION_DESCRIBE │ v20.5.2.7-stable                         │
│ VERSION_INTEGER  │ 20005002                                 │
│ VERSION_GITHASH  │ b9b4d9a948ceecee689a503296dba93f143dd32f │
│ VERSION_REVISION │ 54435                                    │
│ BUILD_DATE       │ 2020-07-02                               │
└──────────────────┴──────────────────────────────────────────┘

6 rows in set. Elapsed: 0.001 sec. 

Clickhouse> create table t_dt(id int,createtime Datetime64(3),lastmodifytime Datetime64(6))ENGINE=MergeTree() order by id;

CREATE TABLE t_dt
(
    `id` int,
    `createtime` Datetime64(3),
    `lastmodifytime` Datetime64(6)
)
ENGINE = MergeTree()
ORDER BY id


Clickhouse> insert into t_dt(id,createtime,lastmodifytime)values(1,'2020-07-12 00:39:14.134','2020-07-12 00:39:14.134716');

INSERT INTO t_dt (id, createtime, lastmodifytime) VALUES

Ok.

1 rows in set. Elapsed: 0.003 sec. 

Clickhouse> select * from t_dt;

SELECT *
FROM t_dt

┌─id─┬──────────────createtime─┬─────────────lastmodifytime─┐
│  1 │ 2020-07-12 00:39:14.134 │ 2020-07-12 00:39:14.134716 │
└────┴─────────────────────────┴────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec. 

可以看到已经可以支持毫秒和微妙的存储计算了。


Clickhouse> WITH toDateTime64('2020-07-12 19:20:11.123', 6) AS dt64 SELECT toStartOfSecond(dt64);

WITH toDateTime64('2020-07-12 19:20:11.123', 6) AS dt64
SELECT toStartOfSecond(dt64)

┌──────toStartOfSecond(dt64)─┐
│ 2020-07-12 19:20:11.000000 │
└────────────────────────────┘

1 rows in set. Elapsed: 0.002 sec. 

参考：

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

本文地址：https://blog.csdn.net/vkingnew/article/details/107294011