Типы данных даты и времени

Для эффективного управления данными временных рядов необходим подробный набор типов даты и времени, и ClickHouse предоставляет именно такой набор. От компактных представлений даты до высокоточных временных меток с точностью до наносекунд — эти типы разработаны так, чтобы обеспечивать баланс между эффективностью хранения и практическими требованиями разных приложений, работающих с временными рядами.

Независимо от того, работаете ли вы с историческими финансовыми данными, показаниями IoT-датчиков или событиями с датой в будущем, типы даты и времени ClickHouse обеспечивают гибкость, необходимую для работы с различными временными сценариями данных. Диапазон поддерживаемых типов позволяет оптимизировать как пространство хранения, так и производительность запросов, сохраняя при этом точность, необходимую в вашем сценарии использования.

• Тип Date подходит в большинстве случаев. Для хранения даты этому типу требуется 2 байта, а диапазон ограничен значениями [1970-01-01, 2149-06-06].

• Date32 охватывает более широкий диапазон дат. Для хранения даты этому типу требуется 4 байта, а диапазон ограничен значениями [1900-01-01, 2299-12-31]

• DateTime хранит значения даты и времени с точностью до секунды в диапазоне [1970-01-01 00:00:00, 2106-02-07 06:28:15]. Для каждого значения требуется 4 байта.

• В случаях, когда требуется более высокая точность, можно использовать DateTime64. Этот тип позволяет хранить время с точностью до наносекунд в диапазоне [1900-01-01 00:00:00, 2299-12-31 23:59:59.99999999]. Для каждого значения требуется 8 байт.

Давайте создадим таблицу, в которой хранятся различные типы дат:

CREATE TABLE dates
(
    `date` Date,
    `wider_date` Date32,
    `datetime` DateTime,
    `precise_datetime` DateTime64(3),
    `very_precise_datetime` DateTime64(9)
)
ENGINE = MergeTree
ORDER BY tuple();

Можно использовать функцию now(), чтобы вернуть текущее время, а now64() — чтобы получить его с указанной точностью, задаваемой первым аргументом.

INSERT INTO dates 
SELECT now(), 
       now()::Date32 + toIntervalYear(100),
       now(), 
       now64(3), 
       now64(9) + toIntervalYear(200);

При этом столбцы будут заполнены временем в соответствии с их типом:

SELECT * FROM dates
FORMAT Vertical;

Row 1:
──────
date:                  2025-03-12
wider_date:            2125-03-12
datetime:              2025-03-12 11:39:07
precise_datetime:      2025-03-12 11:39:07.196
very_precise_datetime: 2025-03-12 11:39:07.196724000

Типы Time и Time64

В случаях, когда нужно хранить значения времени суток без даты, ClickHouse предоставляет типы Time и Time64, представленные в версии 25.6. Они удобны для описания повторяющихся расписаний, ежедневных шаблонов или других ситуаций, где дату и время имеет смысл хранить раздельно.

Примечание

Чтобы использовать Time и Time64, необходимо включить настройку: SET enable_time_time64_type = 1;

Эти типы были представлены в версии 25.6

Тип Time хранит часы, минуты и секунды с точностью до секунды. Внутри он хранится как знаковое 32-битное целое число и поддерживает диапазон [-999:59:59, 999:59:59], то есть допускает значения больше 24 часов. Это может быть полезно при отслеживании прошедшего времени или выполнении арифметических операций, результат которых выходит за пределы одних суток.

Для субсекундной точности Time64 хранит время с настраиваемой дробной частью секунды как знаковое значение Decimal64. Он принимает параметр точности (0-9), который задает количество дробных знаков. Наиболее распространенные значения точности: 3 (миллисекунды), 6 (микросекунды) и 9 (наносекунды).

Ни Time, ни Time64 не поддерживают часовые пояса — они представляют собой значения времени суток без региональной привязки.

Давайте создадим таблицу со столбцами времени:

SET enable_time_time64_type = 1;

CREATE TABLE time_examples
(
    `event_id` UInt8,
    `basic_time` Time,
    `precise_time` Time64(3)
)
ENGINE = MergeTree
ORDER BY event_id;

Можно вставлять значения времени, используя строковые литералы или числовые значения. Для Time числовые значения интерпретируются как секунды, прошедшие с 00:00:00. Для Time64 числовые значения интерпретируются как секунды, прошедшие с 00:00:00, при этом дробная часть интерпретируется в соответствии с точностью столбца:

INSERT INTO time_examples VALUES 
    (1, '14:30:25', '14:30:25.123'),
    (2, 52225, 52225.456),
    (3, '26:11:10', '26:11:10.789');  -- Values normalize beyond 24 hours

SELECT * FROM time_examples ORDER BY event_id;

┌─event_id─┬─basic_time─┬─precise_time─┐
│        1 │ 14:30:25   │ 14:30:25.123 │
│        2 │ 14:30:25   │ 14:30:25.456 │
│        3 │ 26:11:10   │ 26:11:10.789 │
└──────────┴────────────┴──────────────┘

Значения времени можно фильтровать привычным образом:

SELECT * FROM time_examples WHERE basic_time = '14:30:25';

Часовые пояса

Во многих сценариях использования требуется также хранить часовые пояса. Часовой пояс можно задать как последний аргумент для типов DateTime или DateTime64:

CREATE TABLE dtz
(
    `id` Int8,
    `dt_1` DateTime('Europe/Berlin'),
    `dt_2` DateTime,
    `dt64_1` DateTime64(9, 'Europe/Berlin'),
    `dt64_2` DateTime64(9)
)
ENGINE = MergeTree
ORDER BY id;

Определив часовой пояс в DDL, теперь мы можем вставлять значения времени, используя разные часовые пояса:

INSERT INTO dtz 
SELECT 1, 
       toDateTime('2022-12-12 12:13:14', 'America/New_York'),
       toDateTime('2022-12-12 12:13:14', 'America/New_York'),
       toDateTime64('2022-12-12 12:13:14.123456789', 9, 'America/New_York'),
       toDateTime64('2022-12-12 12:13:14.123456789', 9, 'America/New_York')
UNION ALL
SELECT 2, 
       toDateTime('2022-12-12 12:13:15'),
       toDateTime('2022-12-12 12:13:15'),
       toDateTime64('2022-12-12 12:13:15.123456789', 9),
       toDateTime64('2022-12-12 12:13:15.123456789', 9);

А теперь посмотрим, что в нашей таблице:

SELECT dt_1, dt64_1, dt_2, dt64_2
FROM dtz
FORMAT Vertical;

Row 1:
──────
dt_1:   2022-12-12 18:13:14
dt64_1: 2022-12-12 18:13:14.123456789
dt_2:   2022-12-12 17:13:14
dt64_2: 2022-12-12 17:13:14.123456789

Row 2:
──────
dt_1:   2022-12-12 13:13:15
dt64_1: 2022-12-12 13:13:15.123456789
dt_2:   2022-12-12 12:13:15
dt64_2: 2022-12-12 12:13:15.123456789

В первой строке мы вставили все значения, используя часовой пояс America/New_York.

• dt_1 и dt64_1 автоматически преобразуются в Europe/Berlin при выполнении запроса.
• Для dt_2 и dt64_2 часовой пояс не был указан, поэтому используется локальный часовой пояс сервера, который в данном случае — Europe/London.

Во второй строке мы вставили все значения без указания часового пояса, поэтому был использован локальный часовой пояс сервера. Как и в первой строке, dt_1 и dt64_1 преобразуются в Europe/Berlin, а dt_2 и dt64_2 используют локальный часовой пояс сервера.

Функции даты и времени

ClickHouse также предоставляет набор функций, позволяющих преобразовывать значения из одного типа данных в другой.

Например, Можно использовать toDate, чтобы преобразовать значение DateTime в тип Date:

SELECT
    now() AS current_time,
    toTypeName(current_time),
    toDate(current_time) AS date_only,
    toTypeName(date_only)
FORMAT Vertical;    

Row 1:
──────
current_time:             2025-03-12 12:32:54
toTypeName(current_time): DateTime
date_only:                2025-03-12
toTypeName(date_only):    Date

Можно использовать toDateTime64 для преобразования DateTime в DateTime64:

SELECT
    now() AS current_time,
    toTypeName(current_time),
    toDateTime64(current_time, 3) AS date_only,
    toTypeName(date_only)
FORMAT Vertical;

Row 1:
──────
current_time:             2025-03-12 12:35:01
toTypeName(current_time): DateTime
date_only:                2025-03-12 12:35:01.000
toTypeName(date_only):    DateTime64(3)

И также можно использовать toDateTime, чтобы преобразовать Date или DateTime64 обратно в DateTime:

SELECT
    now64() AS current_time,
    toTypeName(current_time),
    toDateTime(current_time) AS date_time1,
    toTypeName(date_time1),
    today() AS current_date,
    toTypeName(current_date),
    toDateTime(current_date) AS date_time2,
    toTypeName(date_time2)
FORMAT Vertical;

Row 1:
──────
current_time:             2025-03-12 12:41:00.598
toTypeName(current_time): DateTime64(3)
date_time1:               2025-03-12 12:41:00
toTypeName(date_time1):   DateTime
current_date:             2025-03-12
toTypeName(current_date): Date
date_time2:               2025-03-12 00:00:00
toTypeName(date_time2):   DateTime