DateTime

Documentación del tipo de dato DateTime en ClickHouse, que almacena marcas de tiempo con precisión de segundos

Permite almacenar un instante que puede expresarse como una fecha del calendario y una hora del día.

Sintaxis:

DateTime([timezone])

Rango de valores admitido: [1970-01-01 00:00:00, 2106-02-07 06:28:15].

Resolución: 1 segundo.

​

Velocidad

El tipo de dato Date es más rápido que DateTime en la mayoría de los casos.

El tipo Date requiere 2 bytes de almacenamiento, mientras que DateTime requiere 4. Sin embargo, durante la compresión, la diferencia de tamaño entre Date y DateTime se vuelve más significativa. Esto se debe a que los minutos y segundos de DateTime son menos compresibles. Filtrar y agregar por Date en lugar de DateTime también es más rápido.

​

Observaciones de uso

El instante temporal se guarda como un Unix timestamp, independientemente de la zona horaria o del horario de verano. La zona horaria afecta a cómo se muestran en formato de texto los valores de tipo DateTime y a cómo se interpretan los valores especificados como cadenas ('2020-01-01 05:00:01').

En las tablas se almacena un Unix timestamp independiente de la zona horaria, y la zona horaria se utiliza para convertirlo a formato de texto o viceversa durante la importación/exportación de datos, o para realizar cálculos de calendario sobre los valores (por ejemplo, las funciones toDate, toHour, etc.). La zona horaria no se almacena en las filas de la tabla (ni en el conjunto de resultados), sino en los metadatos de la columna.

Puede encontrar una lista de las zonas horarias compatibles en la IANA Time Zone Database, y también puede consultarla con SELECT * FROM system.time_zones. La lista también está disponible en Wikipedia.

Puede establecer explícitamente una zona horaria para las columnas de tipo DateTime al crear una tabla. Ejemplo: DateTime('UTC'). Si no se establece la zona horaria, ClickHouse usa el valor del parámetro timezone en la configuración del servidor o en la configuración del sistema operativo en el momento en que se inicia el servidor de ClickHouse.

clickhouse-client aplica de forma predeterminada la zona horaria del servidor si no se establece explícitamente una zona horaria al inicializar el tipo de dato. Para usar la zona horaria del cliente, ejecute clickhouse-client con el parámetro --use_client_time_zone.

ClickHouse muestra los valores según el valor de la configuración date_time_output_format. De forma predeterminada, utiliza el formato de texto YYYY-MM-DD hh:mm:ss. Además, puede cambiar la salida con la función formatDateTime.

Al insertar datos en ClickHouse, puede usar diferentes formatos de cadenas de fecha y hora, según el valor de la configuración date_time_input_format.

​

Ejemplos

1. Crear una tabla con una columna de tipo DateTime e insertar datos en ella:

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 │
└─────────────────────┴──────────┘

• Al insertar datetime como número, se interpreta como Unix Timestamp (UTC) en segundos. 1546300800 representa '2019-01-01 00:00:00' UTC. Sin embargo, como la columna timestamp tiene especificada la zona horaria Asia/Istanbul (UTC+3), al mostrarse como cadena el valor aparecerá como '2019-01-01 03:00:00'. También se acepta un número con parte fraccionaria o exponencial, que se trunca a segundos enteros, de forma coherente con CAST, toDateTime y el formato Values. (Antes de la versión 26.8, dichos números con parte fraccionaria o exponencial en las rutas de entrada JSON y Values/Quoted —esta última abarca todos los formatos que analizan campos con la regla de escape Quoted: Values, MySQLDump y Template/CustomSeparated/Regexp configurados con escape de campo Quoted— no eran aceptados por el analizador de streaming; establezca input_format_read_datetime_number_as_raw_value = 1 para restaurar este comportamiento. En el propio formato Values, dicho literal seguía funcionando —y continúa funcionando en el modo de compatibilidad— mediante el mecanismo alternativo de expresiones SQL, que lo lee como un número de segundos. En JSONExtract y el tipo de datos JSON, un valor fraccionario se analiza mediante Float64, por lo que un valor cercano al límite de un segundo entero puede redondearse al segundo adyacente, a diferencia de los formatos de entrada por filas, que truncan exactamente el texto original. Los formatos de texto con tabulaciones, CSV y otros formatos de texto con escape no se rigen por esta configuración.)
• Al insertar un valor de cadena como datetime, se interpreta según la zona horaria de la columna. '2019-01-01 00:00:00' se interpretará en la zona horaria Asia/Istanbul y se guardará como 1546290000.

2. Filtrado por valores 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 │
└─────────────────────┴──────────┘

Los valores de la columna DateTime se pueden filtrar mediante un valor de texto en el predicado WHERE. Este se convertirá automáticamente a DateTime:

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

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

3. Obtener la zona horaria de una columna de tipo DateTime:

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

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

4. Conversión de zona horaria

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 │
└─────────────────────┴─────────────────────┘

Dado que la conversión de zona horaria solo cambia los metadatos, la operación no tiene coste computacional.

​

Limitaciones en la compatibilidad con zonas horarias

Es posible que algunas zonas horarias no sean totalmente compatibles. Hay algunos casos:

Si el desfase con respecto a UTC no es un múltiplo de 15 minutos, el cálculo de horas y minutos puede ser incorrecto. Por ejemplo, la zona horaria de Monrovia, Liberia, tenía un desfase de UTC -0:44:30 antes del 7 de enero de 1972. Si realiza cálculos sobre horas históricas en la zona horaria de Monrovia, las funciones de procesamiento de tiempo pueden dar resultados incorrectos. No obstante, los resultados posteriores al 7 de enero de 1972 serán correctos.

Si el cambio de hora (debido al horario de verano o por otras razones) se realizó en un momento que no es múltiplo de 15 minutos, también puede obtener resultados incorrectos en ese día concreto.

Fechas de calendario no monotónicas. Por ejemplo, en Happy Valley - Goose Bay, la hora se retrasó una hora a las 00:01:00 del 7 de noviembre de 2010 (un minuto después de la medianoche). Así, después de terminar el 6 de noviembre, hubo un minuto completo del 7 de noviembre; luego la hora volvió a las 23:01 del 6 de noviembre y, tras otros 59 minutos, el 7 de noviembre comenzó de nuevo. ClickHouse no admite (todavía) este tipo de peculiaridades. Durante esos días, los resultados de las funciones de procesamiento de tiempo pueden ser ligeramente incorrectos.

Existe un problema similar en la estación antártica Casey en 2010. Retrasaron la hora tres horas el 5 de marzo, a las 02:00. Si trabaja en una estación antártica, no tema usar ClickHouse. Solo asegúrese de establecer la zona horaria en UTC o tenga en cuenta estas imprecisiones.

Desplazamientos horarios de varios días. Algunas islas del Pacífico cambiaron el desfase de su zona horaria de UTC+14 a UTC-12. Esto no supone ningún problema, pero pueden producirse algunas imprecisiones si realiza cálculos con su zona horaria para momentos históricos en los días de la conversión.

​

Gestión del horario de verano (DST)

El tipo DateTime de ClickHouse con zonas horarias puede mostrar un comportamiento inesperado durante las transiciones del horario de verano (DST), especialmente cuando:

• date_time_output_format está configurado como simple.
• Los relojes se atrasan (“Fall Back”), lo que provoca un solapamiento de una hora.
• Los relojes se adelantan (“Spring Forward”), lo que provoca un hueco de una hora.

De forma predeterminada, ClickHouse siempre elige la primera ocurrencia de una hora solapada y puede interpretar horas inexistentes durante los adelantos del reloj.

Por ejemplo, considere la siguiente transición del horario de verano (DST) al horario estándar.

• El 29 de octubre de 2023, a las 02:00:00, los relojes se atrasan hasta la 01:00:00 (BST → GMT).
• La hora 01:00:00 – 01:59:59 aparece dos veces (una vez en BST y otra en GMT)
• ClickHouse siempre elige la primera ocurrencia (BST), lo que provoca resultados inesperados al sumar intervalos de tiempo.

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

┌────────────────time─┬──────one_hour_later─┐
│ 2023-10-29 01:30:00 │ 2023-10-29 01:30:00 │
└─────────────────────┴─────────────────────┘

Del mismo modo, durante la transición de la hora estándar al horario de verano, puede parecer que se salta una hora.

Por ejemplo:

• El 26 de marzo de 2023, a las 00:59:59, los relojes se adelantan a las 02:00:00 (GMT → BST).
• La hora 01:00:00 – 01:59:59 no existe.

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

┌────────────────time─┬──────one_hour_later─┐
│ 2023-03-26 00:30:00 │ 2023-03-26 02:30:00 │
└─────────────────────┴─────────────────────┘

En este caso, ClickHouse retrasa la hora inexistente 2023-03-26 01:30:00 hasta 2023-03-26 00:30:00.

​

Véase también

• Funciones de conversión de tipos
• Funciones para trabajar con fechas y horas
• Funciones para trabajar con arrays
• La configuración date_time_input_format
• La configuración date_time_output_format
• El parámetro de configuración del servidor timezone
• La configuración session_timezone
• Operadores para trabajar con fechas y horas
• El tipo de dato Date