Date and Date32 in ClickHouse

  

Working with dates is a common requirement in analytics and database applications, whether it's tracking user sign-ups, logging historical records, or planning events far in the future. In ClickHouse, a high-performance columnar OLAP database, choosing the right date type can directly impact performance, storage efficiency, and query accuracy.

 

ClickHouse provides two primary types for representing dates:

 

·      Date: a compact type ideal for modern applications with dates in the near past and near future.

·      Date32: an extended-range type suited for historical datasets, long-term planning, or interoperability with other types like DateTime64.

 

Understanding the differences between Date and Date32 from how they store values to their supported ranges will help you model your data better and optimize queries more effectively.

 

In this post, we’ll explore both types in detail, look at their internal storage, supported ranges, use cases, and examples to help you decide when to use which.

 

1. Date type

A Date in ClickHouse is stored as an unsigned 16-bit integer, representing the number of days since 1970-01-01 (the start of the Unix Epoch). It supports dates from just after 1970 up to a compile-time defined upper limit—currently extending to 2149-06-06. This type does not include time zone information, and only the date component is stored.

CREATE DATABASE IF NOT EXISTS demo_db;

CREATE TABLE demo_db.events_date (
    event_name String,
    event_date Date
) ENGINE = MergeTree()
ORDER BY event_date;

INSERT INTO demo_db.events_date VALUES ('Signup', '2024-05-01');

krishna :) SELECT * FROM demo_db.events_date;

SELECT *
FROM demo_db.events_date

Query id: 556bfb6a-0967-48bf-8e39-61ee5bf1b22e

   ┌─event_name─┬─event_date─┐
1. │ Signup     │ 2024-05-01 │
   └────────────┴────────────┘

1 row in set. Elapsed: 0.002 sec.

   

Let's try to insert a data beyond 2149-06-06, for ex: '2149-06-07'

 

krishna :) INSERT INTO demo_db.events_date VALUES ('Signup', '2149-06-07');

INSERT INTO demo_db.events_date FORMAT Values

Query id: 30b7da0e-c176-4232-a6df-34656227002e

Ok.

1 row in set. Elapsed: 0.011 sec. 

krishna :) SELECT * FROM demo_db.events_date;

SELECT *
FROM demo_db.events_date

Query id: f143d334-84f0-4b8e-b038-39febf07d339

   ┌─event_name─┬─event_date─┐
1. │ Signup     │ 2149-06-06 │
2. │ Signup     │ 2024-05-01 │
   └────────────┴────────────┘

2 rows in set. Elapsed: 0.003 sec. 

   

As you observe the output, even though I executed an insert statement with date '2149-06-07', it is restricted the the max value 2149-06-06.

 

2. Date32 type

Date32 represents a date with extended range support, identical to that of DateTime64. It is stored as a signed 32-bit integer in native byte order, where the value indicates the number of days since 1900-01-01. A value of 0 corresponds to 1900-01-01, and negative values represent dates before 1900.

 

CREATE TABLE demo_db.events_date32 (
    event_name String,
    event_date Date32
) ENGINE = MergeTree()
ORDER BY event_date;

INSERT INTO demo_db.events_date32 VALUES 
('Historical Event', '1850-01-01'),
('Future Event', '2200-12-31');

krishna :) SELECT * FROM demo_db.events_date32;

SELECT *
FROM demo_db.events_date32

Query id: e93a885b-ae35-42f0-87e7-06e2e422213f

   ┌─event_name───────┬─event_date─┐
1. │ Historical Event │ 1900-01-01 │
2. │ Future Event     │ 2200-12-31 │
   └──────────────────┴────────────┘

2 rows in set. Elapsed: 0.002 sec.

   

Why the Historical Event set to 1900-01-01?

The reason for historical date 1850-01-01 gets reset to 1900-01-01 in ClickHouse when using the Date32 type is due to how ClickHouse handles out-of-range values for Date32 during insertion.

 

·      Date32 stores the number of days since 1900-01-01 using a signed 32-bit integer.

·      This allows dates far before and after 1900-01-01.

·      However, ClickHouse enforces validation during insert, if a date is outside the supported range, it will be clipped (adjusted) to the nearest valid value.

 

In summary, ClickHouse offers two primary types for storing date-only values: Date and Date32. Choosing the right one depends on your date range requirements and storage efficiency goals

 

 

Previous                                                    Next                                                    Home