DateTime

Permet de stocker un instant pouvant être exprimé sous la forme d'une date du calendrier et d'une heure de la journée.

Syntaxe :

DateTime([timezone])

Plage de valeurs prise en charge : [1970-01-01 00:00:00, 2106-02-07 06:28:15].

Résolution : 1 seconde.

Vitesse

Le type de données Date est plus rapide que DateTime dans la plupart des cas.

Le type Date nécessite 2 octets de stockage, tandis que DateTime en nécessite 4. Cependant, lors de la compression, la différence de taille entre Date et DateTime devient plus marquée. Cela s’explique par le fait que les minutes et les secondes de DateTime se compressent moins bien. Le filtrage et l’agrégation sur Date plutôt que sur DateTime sont également plus rapides.

Remarques d’utilisation

L’instant est enregistré sous forme de Unix timestamp, indépendamment du fuseau horaire ou de l’heure d’été. Le fuseau horaire influence la façon dont les valeurs de type DateTime sont affichées au format texte et la façon dont les valeurs spécifiées sous forme de chaînes sont interprétées ('2020-01-01 05:00:01').

Un Unix timestamp indépendant du fuseau horaire est stocké dans les tables, et le fuseau horaire sert à le convertir au format texte, ou inversement, lors de l’import/export des données, ou à effectuer des calculs calendaires sur les valeurs (par exemple : fonctions toDate, toHour, etc.). Le fuseau horaire n’est pas stocké dans les lignes de la table (ni dans le jeu de résultats), mais dans les métadonnées de la colonne.

La liste des fuseaux horaires pris en charge est disponible dans la base de données des fuseaux horaires IANA et peut également être obtenue avec SELECT * FROM system.time_zones. La liste est aussi disponible sur Wikipedia.

Vous pouvez définir explicitement un fuseau horaire pour les colonnes de type DateTime lors de la création d’une table. Exemple : DateTime('UTC'). Si aucun fuseau horaire n’est défini, ClickHouse utilise la valeur du paramètre timezone dans les paramètres du serveur ou dans les paramètres du système d’exploitation au moment du démarrage du serveur ClickHouse.

Le clickhouse-client applique par défaut le fuseau horaire du serveur si aucun fuseau horaire n’est explicitement défini lors de l’initialisation du type de données. Pour utiliser le fuseau horaire du client, exécutez clickhouse-client avec le paramètre --use_client_time_zone.

ClickHouse affiche les valeurs en fonction de la valeur du paramètre date_time_output_format. Le format texte YYYY-MM-DD hh:mm:ss est utilisé par défaut. Vous pouvez également modifier le format de sortie avec la fonction formatDateTime.

Lors de l’insertion de données dans ClickHouse, vous pouvez utiliser différents formats de chaînes de date et d’heure, selon la valeur du paramètre date_time_input_format.

Exemples

1. Création d'une table avec une colonne de type DateTime et insertion de données dans cette table :

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

Lors de l'insertion d'une valeur datetime sous forme de nombre, elle est traitée comme un Unix timestamp (UTC) en secondes. 1546300800 représente '2019-01-01 00:00:00' UTC. Cependant, comme le fuseau horaire Asia/Istanbul (UTC+3) est défini pour la colonne timestamp, lors de l'affichage sous forme de chaîne, la valeur sera affichée comme '2019-01-01 03:00:00'. Un nombre comportant une partie fractionnaire ou un exposant est également accepté et tronqué aux secondes entières, conformément à CAST, toDateTime et au format Values. (Avant la version 26.8, un tel nombre fractionnaire ou avec exposant dans les chemins d'entrée JSON et Values/Quoted — ce dernier couvrant tous les formats qui analysent les champs avec la règle d'échappement Quoted : Values, MySQLDump et Template/CustomSeparated/Regexp configurés avec l'échappement de champ Quoted — n'était pas accepté par l'analyseur de flux ; définissez input_format_read_datetime_number_as_raw_value = 1 pour rétablir ce comportement. Dans le format Values lui-même, un tel littéral fonctionnait toujours — et continue de fonctionner en mode de compatibilité — grâce au mécanisme de repli des expressions SQL, qui le lit comme un nombre de secondes. Dans JSONExtract et le type de données JSON, une valeur fractionnaire est analysée via Float64, de sorte qu'une valeur proche d'une limite de seconde entière peut être arrondie à la seconde adjacente, contrairement aux formats d'entrée par lignes qui tronquent exactement le texte d'origine. Les formats de texte séparés par des tabulations, CSV et autres formats de texte avec échappement ne sont pas régis par ce paramètre.)
Lors de l'insertion d'une valeur de type chaîne comme datetime, elle est traitée comme appartenant au fuseau horaire de la colonne. '2019-01-01 00:00:00' sera interprété dans le fuseau horaire Asia/Istanbul et enregistré comme 1546290000.

2. Filtrage sur les valeurs 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 │
└─────────────────────┴──────────┘

Les valeurs de la colonne DateTime peuvent être filtrées à l’aide d’une chaîne de caractères dans le prédicat WHERE. Celle-ci sera automatiquement convertie en DateTime :

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

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

3. Récupération du fuseau horaire d’une colonne de type DateTime :

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

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

4. Conversion de fuseaux horaires

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

Comme la conversion de fuseau horaire ne modifie que les métadonnées, l’opération n’entraîne aucun coût de calcul.

Limites de la prise en charge des fuseaux horaires

Certains fuseaux horaires peuvent ne pas être entièrement pris en charge. Voici quelques cas :

Si le décalage par rapport à l’UTC n’est pas un multiple de 15 minutes, le calcul des heures et des minutes peut être incorrect. Par exemple, le fuseau horaire de Monrovia, au Liberia, avait un décalage de UTC -0:44:30 avant le 7 janv. 1972. Si vous effectuez des calculs sur des heures historiques dans le fuseau horaire de Monrovia, les fonctions de traitement du temps peuvent produire des résultats incorrects. Les résultats après le 7 janv. 1972 seront toutefois corrects.

Si le changement d’heure (en raison de l’heure d’été ou pour d’autres raisons) a eu lieu à un instant qui n’est pas un multiple de 15 minutes, vous pouvez également obtenir des résultats incorrects ce jour-là.

Dates calendaires non monotones. Par exemple, à Happy Valley - Goose Bay, l’heure a été reculée d’une heure à 00:01:00 le 7 nov. 2010 (une minute après minuit). Ainsi, une fois le 6 nov. terminé, les habitants ont observé une minute entière du 7 nov., puis l’heure est revenue à 23:01 le 6 nov. et, 59 minutes plus tard, le 7 nov. a recommencé. ClickHouse ne prend pas (encore) en charge ce genre de bizarrerie. Pendant ces journées, les résultats des fonctions de traitement du temps peuvent être légèrement incorrects.

Un problème similaire existe pour la station antarctique Casey en 2010. L’heure y a été reculée de trois heures le 5 mars à 02:00. Si vous travaillez dans cette station antarctique, n’hésitez pas à utiliser ClickHouse. Assurez-vous simplement de définir le fuseau horaire sur UTC ou gardez à l’esprit qu’il peut y avoir des imprécisions.

Décalages horaires sur plusieurs jours. Certaines îles du Pacifique ont modifié le décalage de leur fuseau horaire, passant de UTC+14 à UTC-12. Ce n’est pas un problème en soi, mais des imprécisions peuvent survenir si vous effectuez des calculs avec leur fuseau horaire pour des dates historiques correspondant aux jours de transition.

Gestion de l’heure d’été (DST)

Le type DateTime de ClickHouse avec fuseaux horaires peut présenter un comportement inattendu lors des transitions liées à l’heure d’été (DST), en particulier lorsque :

date_time_output_format est défini sur simple.
Les horloges sont reculées (« retour à l’heure d’hiver »), ce qui provoque un chevauchement d’une heure.
Les horloges sont avancées (« passage à l’heure d’été »), ce qui provoque un décalage d’une heure.

Par défaut, ClickHouse choisit toujours la première occurrence d’une heure en chevauchement et peut interpréter des heures inexistantes lors des décalages vers l’avant.

Par exemple, considérons la transition suivante de l’heure d’été (DST) à l’heure normale.

Le 29 octobre 2023, à 02:00:00, les horloges sont reculées à 01:00:00 (BST → GMT).
L’heure 01:00:00 – 01:59:59 apparaît deux fois (une fois en BST et une fois en GMT).
ClickHouse choisit toujours la première occurrence (BST), ce qui entraîne des résultats inattendus lors de l’ajout d’intervalles de temps.

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

┌

De même, lors du passage de l’heure standard à l’heure d’été, une heure peut sembler être sautée.

Par exemple :

Le 26 mars 2023, à 00:59:59, les horloges avancent directement à 02:00:00 (GMT → BST).
L’heure 01:00:00 – 01:59:59 n’existe pas.

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

┌

Dans ce cas, ClickHouse ramène l’heure inexistante 2023-03-26 01:30:00 à 2023-03-26 00:30:00.