Understanding DateTime and DateTime64 Types in ClickHouse

  

ClickHouse provides two main data types to store timestamps with both date and time components: DateTime and DateTime64. These types allow you to represent specific instants in time with different levels of precision and features.

 

1. DateTime

Used to store a specific moment in time, represented by a calendar date and a time of day. The supported range of values is from 1970-01-01 00:00:00 up to 2106-02-07 06:28:15.

 

Precision: 1 second

 

Syntax

DateTime([timezone])

   

Date vs DateTime

The Date data type is generally faster than DateTime in most scenarios. Date uses 2 bytes of storage, whereas DateTime requires 4 bytes. This difference becomes even more significant when the database applies compression, since the minutes and seconds in DateTime are less compressible. Additionally, filtering and aggregating data using the Date type is typically faster than using DateTime.

 

Remember following points while working with DateTime

·      Internally, DateTime values are stored as Unix timestamps, which are time zone–agnostic.

·      The time zone influences how DateTime values are displayed as text and how string inputs are parsed.

·      Time zone information is stored in column metadata, not in individual rows or result sets.

·      Supported time zones follow the IANA Time Zone Database and can be queried from the system or found on Wikipedia.

·      You can specify a time zone when defining a DateTime column (e.g., DateTime('UTC')). If omitted, the server or OS time zone at startup is used.

·      The default time zone used by clickhouse-client is the server time zone unless overridden with the --use_client_time_zone option.

·      Output format of DateTime values defaults to YYYY-MM-DD hh:mm:ss but can be customized using date_time_output_format or the formatDateTime function.

·      When inserting data, various date/time string formats are accepted depending on the date_time_input_format setting.

 

2. DateTime64

This type stores a precise moment in time, expressed as a calendar date and time of day, with configurable sub-second precision.

 

Precision (tick size): Defined by a value from 0 to 9, representing 10^(-precision) seconds. Commonly used precisions are 3 (milliseconds), 6 (microseconds), and 9 (nanoseconds).

 

Syntax:

DateTime64(precision, [timezone])

   

Internally, DateTime64 stores the timestamp as the number of ticks since the Unix epoch (1970-01-01 00:00:00 UTC) in an Int64 format. The tick size depends on the specified precision.

 

The time zone can be set for the entire column, influencing how values are displayed and parsed (e.g., '2020-01-01 05:00:01.000'). Like DateTime, the time zone is saved in the column metadata but not within individual rows or result sets.

 

Supported value range: From 1900-01-01 00:00:00 up to 2299-12-31 23:59:59.99999999.

 

Note: When using maximum precision (9 digits, nanoseconds), the maximum supported timestamp is 2262-04-11 23:47:16 UTC. For lower precisions, the maximum date extends up to the year 2299.

 

Following snippet demonstrate the examples.

 

CREATE DATABASE IF NOT EXISTS demo_db;

CREATE TABLE demo_db.events_datetime_demo (
    event_name String,
    event_time DateTime,                -- Stores timestamp with 1-second precision
    event_time_high_precision DateTime64(6)  -- Stores timestamp with microseconds precision
) ENGINE = MergeTree()
ORDER BY event_time;

-- Insert some sample data
INSERT INTO demo_db.events_datetime_demo VALUES
('Standard Precision Event', '2025-05-16 14:30:00', '2025-05-16 14:30:00.123456'),
('High Precision Event', '2025-05-16 14:31:00', '2025-05-16 14:31:00.654321');

-- Query the table
SELECT
    event_name,
    event_time,
    event_time_high_precision
FROM demo_db.events_datetime_demo;

krishna :) SELECT
    event_name,
    event_time,
    event_time_high_precision
FROM demo_db.events_datetime_demo;

SELECT
    event_name,
    event_time,
    event_time_high_precision
FROM demo_db.events_datetime_demo

Query id: b4a047ce-dc2f-49bf-91a4-2343e0af1a6e

   ┌─event_name───────────────┬──────────event_time─┬──event_time_high_precision─┐
1. │ Standard Precision Event │ 2025-05-16 14:30:00 │ 2025-05-16 14:30:00.123456 │
2. │ High Precision Event     │ 2025-05-16 14:31:00 │ 2025-05-16 14:31:00.654321 │
   └──────────────────────────┴─────────────────────┴────────────────────────────┘

2 rows in set. Elapsed: 0.004 sec.

 

Previous                                                    Next                                                    Home