DateTime

Позволяет хранить момент времени, который можно представить в виде календарной даты и времени суток.

Синтаксис:

DateTime([timezone])

Поддерживаемый диапазон значений: [1970-01-01 00:00:00, 2106-02-07 06:28:15].

Разрешение: 1 секунда.

Тип данных Date работает быстрее, чем DateTime, в большинстве случаев.

Для хранения типа Date требуется 2 байта, а для DateTime — 4. Однако при сжатии разница в размере между Date и DateTime становится еще заметнее. Это связано с тем, что минуты и секунды в DateTime сжимаются хуже. Фильтрация и агрегация по Date вместо DateTime также выполняются быстрее.

Момент времени сохраняется как Unix-временная метка независимо от часового пояса или перехода на летнее время. Часовой пояс влияет на то, как значения типа DateTime отображаются в текстовом формате и как разбираются значения, заданные в виде строк (2020-01-01 05:00:01).

В таблицах хранится Unix-временная метка, не зависящая от часового пояса, а сам часовой пояс используется для преобразования её в текстовый формат и обратно при импорте/экспорте данных, а также для выполнения календарных вычислений над значениями (например, функциями toDate, toHour и т. д.). Часовой пояс не хранится в строках таблицы (или в наборе результатов), а сохраняется в метаданных столбца.

Список поддерживаемых часовых поясов можно найти в IANA Time Zone Database, а также получить запросом SELECT * FROM system.time_zones. Этот список также доступен в Википедии.

Вы можете явно задать часовой пояс для столбцов типа DateTime при создании таблицы. Пример: DateTime('UTC'). Если часовой пояс не задан, ClickHouse использует значение параметра timezone в настройках сервера или в настройках операционной системы на момент запуска сервера ClickHouse.

По умолчанию clickhouse-client использует часовой пояс сервера, если при инициализации типа данных часовой пояс явно не задан. Чтобы использовать часовой пояс клиента, запустите clickhouse-client с параметром --use_client_time_zone.

ClickHouse выводит значения в зависимости от значения настройки date_time_output_format. По умолчанию используется текстовый формат YYYY-MM-DD hh:mm:ss. Кроме того, формат вывода можно изменить с помощью функции formatDateTime.

При вставке данных в ClickHouse можно использовать разные форматы строк даты и времени в зависимости от значения настройки date_time_input_format.

1. Создание таблицы со столбцом типа DateTime и вставка данных в неё:

CREATE TABLE dt
(
    `timestamp` DateTime('Asia/Istanbul'),
    `event_id` UInt8
)
ENGINE = TinyLog;

-- Parse DateTime
-- - from a string,
-- - from a number interpreted as the number of seconds since 1970-01-01 (a fractional part is truncated to whole seconds).
INSERT INTO dt VALUES ('2019-01-01 00:00:00', 1), (1546300800, 2);

SELECT * FROM dt;

┌───────────timestamp─┬─event_id─┐
│ 2019-01-01 00:00:00 │        1 │
│ 2019-01-01 03:00:00 │        2 │
└─────────────────────┴──────────┘

• При вставке datetime в виде числа оно интерпретируется как Unix-временная метка (UTC) в секундах. 1546300800 соответствует '2019-01-01 00:00:00' UTC. Однако, поскольку для столбца timestamp указан часовой пояс Asia/Istanbul (UTC+3), при выводе в виде строки значение будет отображаться как '2019-01-01 03:00:00'. Также принимается число с дробной или экспоненциальной частью, которое усекается до целых секунд, аналогично CAST, toDateTime и формату Values. (До версии 26.8 такое число с дробной или экспоненциальной частью при обработке входных данных в JSON и Values/Quoted — последний охватывает все форматы, разбирающие поля с правилом экранирования Quoted: Values, MySQLDump и Template/CustomSeparated/Regexp, настроенные с экранированием полей Quoted, — не принималось потоковым парсером; чтобы восстановить это поведение, установите input_format_read_datetime_number_as_raw_value = 1. В самом формате Values такой литерал всё ещё работал — и продолжает работать в режиме совместимости — благодаря резервному механизму обработки SQL-выражений, который считывает его как количество секунд. В JSONExtract и типе данных JSON дробное значение разбирается через Float64, поэтому значение вблизи границы целой секунды может округлиться до соседней секунды, в отличие от построчных входных форматов, которые усекают исходный текст без округления. Текстовые форматы с разделителями табуляцией, CSV и другие экранированные текстовые форматы не регулируются этой настройкой.)
• При вставке строкового значения как datetime оно интерпретируется как значение в часовом поясе столбца. '2019-01-01 00:00:00' будет интерпретироваться как значение в часовом поясе Asia/Istanbul и сохранено как 1546290000.

2. Фильтрация значений DateTime

SELECT * FROM dt WHERE timestamp = toDateTime('2019-01-01 00:00:00', 'Asia/Istanbul')

┌───────────timestamp─┬─event_id─┐
│ 2019-01-01 00:00:00 │        1 │
└─────────────────────┴──────────┘

Значения столбца DateTime можно фильтровать с помощью строкового значения в условии WHERE. Оно будет автоматически преобразовано в DateTime:

SELECT * FROM dt WHERE timestamp = '2019-01-01 00:00:00'

┌───────────timestamp─┬─event_id─┐
│ 2019-01-01 00:00:00 │        1 │
└─────────────────────┴──────────┘

3. Определение часового пояса для столбца типа DateTime:

SELECT toDateTime(now(), 'Asia/Istanbul') AS column, toTypeName(column) AS x

┌──────────────column─┬─x─────────────────────────┐
│ 2019-10-16 04:12:04 │ DateTime('Asia/Istanbul') │
└─────────────────────┴───────────────────────────┘

4. Преобразование часовых поясов

SELECT
toDateTime(timestamp, 'Europe/London') AS lon_time,
toDateTime(timestamp, 'Asia/Istanbul') AS istanbul_time
FROM dt

┌───────────lon_time──┬───────istanbul_time─┐
│ 2019-01-01 00:00:00 │ 2019-01-01 03:00:00 │
│ 2018-12-31 21:00:00 │ 2019-01-01 00:00:00 │
└─────────────────────┴─────────────────────┘

Поскольку при преобразовании часового пояса изменяются только метаданные, эта операция не требует вычислительных ресурсов.

Некоторые часовые пояса могут поддерживаться не полностью. Есть несколько случаев:

Если смещение относительно UTC не кратно 15 минутам, вычисление часов и минут может быть некорректным. Например, в Монровии (Либерия) до 7 Jan 1972 действовало смещение UTC -0:44:30. Если вы выполняете вычисления для исторического времени в часовом поясе Монровии, функции обработки времени могут давать некорректные результаты. Тем не менее после 7 Jan 1972 результаты будут корректными.

Если перевод времени (из-за перехода на летнее время или по другим причинам) был выполнен в момент, не кратный 15 минутам, в этот конкретный день вы также можете получить некорректные результаты.

Немонотонные календарные даты. Например, в Happy Valley - Goose Bay время было переведено на один час назад в 00:01:00 7 Nov 2010 (через одну минуту после полуночи). То есть после окончания 6 Nov люди прожили целую одну минуту 7 Nov, затем время перевели назад на 23:01 6 Nov, и ещё через 59 минут 7 Nov началось снова. ClickHouse пока не поддерживает такие занятные случаи. В эти дни результаты функций обработки времени могут быть слегка некорректными.

Похожая проблема существует для антарктической станции Casey в 2010 году. Там время перевели на три часа назад 5 Mar в 02:00. Если вы работаете на антарктической станции, не бойтесь использовать ClickHouse. Просто убедитесь, что установили часовой пояс в UTC, или учитывайте возможные неточности.

Сдвиги времени на несколько дней. Некоторые острова Тихого океана изменили смещение своего часового пояса с UTC+14 на UTC-12. Это допустимо, но при вычислениях с их часовым поясом для исторических моментов времени в дни такого перехода возможны неточности.

Тип DateTime в ClickHouse с часовыми поясами может вести себя неожиданно при переходах на летнее время (DST), особенно в следующих случаях:

• date_time_output_format установлен в simple.
• Часы переводятся назад ("Fall Back"), из-за чего возникает перекрытие в один час.
• Часы переводятся вперед ("Spring Forward"), из-за чего образуется пропуск в один час.

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

Например, рассмотрим следующий переход с летнего времени (DST) на стандартное.

• 29 октября 2023 года в 02:00:00 часы переводятся назад на 01:00:00 (BST → GMT).
• Интервал 01:00:00 – 01:59:59 встречается дважды (один раз в BST и один раз в GMT).
• ClickHouse всегда выбирает первый вариант (BST), что может приводить к неожиданным результатам при добавлении временных интервалов.

SELECT '2023-10-29 01:30:00'::DateTime('Europe/London') AS time, time + toIntervalHour(1) AS one_hour_later

┌

Аналогично, при переходе со стандартного времени на летнее может показаться, что один час как будто пропадает.

Например:

• 26 марта 2023 года в 00:59:59 часы переводятся вперёд на 02:00:00 (GMT → BST).
• Часа 01:00:00 – 01:59:59 не существует.

SELECT '2023-03-26 01:30:00'::DateTime('Europe/London') AS time, time + toIntervalHour(1) AS one_hour_later

┌

В этом случае ClickHouse сдвигает несуществующее время 2023-03-26 01:30:00 назад — к 2023-03-26 00:30:00.

• Функции преобразования типов
• Функции для работы с датами и временем
• Функции для работы с массивами
• Настройка date_time_input_format
• Настройка date_time_output_format
• Параметр timezone в конфигурации сервера
• Настройка session_timezone
• Операторы для работы с датами и временем
• Тип данных Date