ClickHouse Date & Date32 limits

date_limits.sql

// Clickhouse server version 23.2.4 revision 54461.
CREATE TABLE dt
(
    `timestamp` Date,
    `event_id` UInt8
)
ENGINE = TinyLog


a6382f28c85d :) INSERT INTO dt VALUES ('1925-01-01', 1);

INSERT INTO dt FORMAT Values

Query id: 5767445e-792a-46be-ad63-4187f81c10a6

Ok.

1 row in set. Elapsed: 0.004 sec. 

a6382f28c85d :) select * from dt;

SELECT *
FROM dt

Query id: fac98bc8-5562-480c-9d52-619a0b981717

┌──timestamp─┬─event_id─┐
│ 1970-01-01 │        1 │
└────────────┴──────────┘

1 row in set. Elapsed: 0.002 sec. 


// Date 32
CREATE TABLE dt32
(
    `timestamp` Date32,
    `event_id` UInt8
)
ENGINE = TinyLog

Query id: 91a21b15-cc44-4299-b835-2d66ed409c33

Ok.

0 rows in set. Elapsed: 0.004 sec. 

614d15440684 :) INSERT INTO dt32 VALUES ('1925-01-01', 1);
                

INSERT INTO dt32 FORMAT Values

Query id: ff1a5e02-12fb-45d2-8a9c-b6c8d34612d8

Ok.

1 row in set. Elapsed: 0.004 sec. 

614d15440684 :) select * from dt32;

SELECT *
FROM dt32

Query id: 6f582568-5f80-4310-9876-60a7a82e95b4

┌──timestamp─┬─event_id─┐
│ 1925-01-01 │        1 │
└────────────┴──────────┘

1 row in set. Elapsed: 0.001 sec. 

614d15440684 :) INSERT INTO dt32 VALUES ('1900-01-01', 1);

INSERT INTO dt32 FORMAT Values

Query id: f8f4be78-009e-4dd1-b017-d7af97901790

Ok.

1 row in set. Elapsed: 0.003 sec. 

614d15440684 :) select * from dt32;

SELECT *
FROM dt32

Query id: 7b16537d-8c08-479b-807a-dccd1e976caa

┌──timestamp─┬─event_id─┐
│ 1925-01-01 │        1 │
│ 1900-01-01 │        1 │
└────────────┴──────────┘