Entrée/sortie de date et d’heure

Dans ce chapitre :

Paramètres de session pour les dates, heures et horodatages

Un ensemble de paramètres de session détermine comment les données de date, d’heure et d’horodatage sont transmises vers et depuis Snowflake, ainsi que le fuseau horaire utilisé dans les formats d’heure et d’horodatage qui prennent en charge les fuseaux horaires.

Les paramètres peuvent être définis aux niveaux du compte, de l’utilisateur et de la session. Exécutez la commande SHOW PARAMETERS pour afficher les paramètres actuels qui s’appliquent à toutes les opérations de la session en cours.

Formats d’entrée

Les paramètres suivants définissent les formats de date, d’heure et d’horodatage reconnus pour DML, y compris les opérations COPY, INSERT et MERGE :

La valeur par défaut des trois paramètres est AUTO. Lorsque la valeur du paramètre est définie sur AUTO, Snowflake tente de faire correspondre les chaînes de date, d’heure et d’horodatage dans toute expression d’entrée avec l’un des formats figurant dans la liste des formats pris en charge pour la détection AUTO :

  • Si un format correspondant est trouvé, Snowflake accepte la chaîne.

  • Si aucun format correspondant n’est trouvé, Snowflake renvoie une erreur.

Formats de sortie

Les paramètres suivants définissent les formats de sortie de date et d’heure de Snowflake :

De plus, le paramètre suivant mappe l’alias de type de donnée TIMESTAMP à l’une des trois variations TIMESTAMP\_\* :

Fuseau horaire

Le paramètre suivant détermine le fuseau horaire :

Options de format de fichier pour le chargement/déchargement de dates, d’heures et d’horodatages

Outre les paramètres de format d’entrée et de sortie, Snowflake offre trois options de format de fichier à utiliser lors du chargement ou du déchargement des données de tables Snowflake :

  • DATE_FORMAT

  • TIME_FORMAT

  • TIMESTAMP_FORMAT

Les options peuvent être spécifiées directement dans la commande COPY ou dans une zone de préparation nommée ou un objet de format de fichier référencé dans la commande COPY. Lorsqu’elles sont spécifiées, ces options remplacent les formats d’entrée correspondants (lors du chargement des données) ou les formats de sortie (lors du déchargement de données).

Chargement des données

Lorsqu’elles sont utilisées dans le chargement de données, les options spécifient le format des chaînes de date, d’heure et d’horodatage dans vos fichiers de données préparés. Les options remplacent les paramètres DATE_INPUT_FORMAT, TIME_INPUT_FORMAT ou TIMESTAMP_INPUT_FORMAT.

La valeur par défaut pour toutes ces options est AUTO, ce qui signifie que la commande COPY INTO <table> tente de faire correspondre toutes les chaînes de date et d’horodatage dans les fichiers de données préparés avec l’un des formats figurant dans la liste des formats pris en charge pour la détection AUTO.

  • Si un format correspondant est trouvé, Snowflake accepte la chaîne.

  • Si aucun format correspondant n’est trouvé, Snowflake renvoie une erreur et effectue ensuite l’action spécifiée pour l’option de copie ON_ERROR.

Avertissement

Snowflake prend en charge la détection automatique des formats de date, d’heure et d’horodatage les plus courants (voir les tables ci-dessous). Cependant, certains formats peuvent produire des résultats ambigus, ce qui peut amener Snowflake à appliquer un format incorrect en utilisant AUTO pour le chargement des données.

Pour garantir un chargement correct des données, Snowflake recommande fortement de définir explicitement les options de format de fichier pour le chargement des données.

Déchargement des données

Lorsqu’elles sont utilisées dans le déchargement de données, les options spécifient le format appliqué aux dates, aux heures et aux horodatages déchargés dans les fichiers de la zone de préparation spécifiée.

La valeur par défaut pour toutes ces options est AUTO, ce qui signifie que Snowflake applique le formatage spécifié dans les paramètres suivants :

  • DATE_OUTPUT_FORMAT

  • TIME_OUTPUT_FORMAT

  • TIMESTAMP_*_OUTPUT_FORMAT (en fonction du paramètre TIMESTAMP_TYPE_MAPPING)

Formats pris en charge pour la détection AUTO

Sur demande, Snowflake détecte et traite automatiquement les formats suivants pour les chaînes d’entrée de date, d’heure et d’horodatage.

Attention

Certaines chaînes peuvent correspondre à plusieurs formats. Par exemple, « 07-04-2016 » est compatible avec les formats MM-DD-YYYY et DD-MM-YYYY, mais a des significations différentes dans chaque format (4 juillet et 7 avril). Le fait qu’un format correspondant soit trouvé ne garantit pas que la chaîne est analysée comme le souhaite l’utilisateur.

Bien que la détection automatique du format de date soit pratique, elle augmente le risque d’interprétation erronée des dates. Snowflake recommande vivement de spécifier explicitement le format plutôt que de s’appuyer sur la détection automatique de la date.

Formats de date

Format

Exemple

Remarques

Formats de date ISO

YYYY-MM-DD

2013-04-28

Autres formats de date

DD-MON-YYYY

17-DEC-1980

MM/DD/YYYY

12/17/1980

Peut produire des dates incorrectes lors du chargement ou de l’utilisation de dates dans des formats européens classiques (c.-à-d. DD/MM/YYYY). Par exemple, 05/02/2013 pourrait être interprété comme le 2 mai 2013 au lieu du 5 février 2013.

Lorsque vous utilisez le formatage de date AUTO, les tirets et les barres obliques ne sont pas interchangeables. Les barres obliques impliquent le format MM/DD/YYYY, tandis que les tirets impliquent le format YYYY-MM-DD. Les chaînes telles que « 2019/01/02 » ou « 01-02-2019 » ne sont pas interprétées comme prévu.

Formats d’heure

Format

Exemple

Remarques

Formats d’heure ISO

HH24:MI:SS.FFTZH:TZM

20:57:01.123456789+07:00

HH24:MI:SS.FF

20:57:01.123456789

HH24:MI:SS

20:57:01

HH24:MI

20:57

Formats d’heure Internet (RFC)

HH12:MI:SS.FF AM

07:57:01.123456789 AM

HH12:MI:SS AM

04:01:07 AM

HH12:MI AM

04:01 AM

Le spécificateur de format AM autorise les valeurs avec AM ou PM.

Formats d’horodatage

Format

Exemple

Remarques

Formats d’horodatage ISO

YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM

2013-04-28T20:57:01.123456789+07:00

Les guillemets doubles autour de T sont facultatifs (voir l’astuce qui suit ce tableau pour plus de détails).

YYYY-MM-DD HH24:MI:SS.FFTZH:TZM

2013-04-28 20:57:01.123456789+07:00

YYYY-MM-DD HH24:MI:SS.FFTZH

2013-04-28 20:57:01.123456789+07

YYYY-MM-DD HH24:MI:SS.FF TZH:TZM

2013-04-28 20:57:01.123456789 +07:00

YYYY-MM-DD HH24:MI:SS.FF TZHTZM

2013-04-28 20:57:01.123456789 +0700

YYYY-MM-DD HH24:MI:SS TZH:TZM

2013-04-28 20:57:01 +07:00

YYYY-MM-DD HH24:MI:SS TZHTZM

2013-04-28 20:57:01 +0700

YYYY-MM-DD"T"HH24:MI:SS.FF

2013-04-28T20:57:01.123456

Les guillemets doubles autour de T sont facultatifs (voir l’astuce qui suit ce tableau pour plus de détails).

YYYY-MM-DD HH24:MI:SS.FF

2013-04-28 20:57:01.123456

YYYY-MM-DD"T"HH24:MI:SS

2013-04-28T20:57:01

Les guillemets doubles autour de T sont facultatifs (voir l’astuce qui suit ce tableau pour plus de détails).

YYYY-MM-DD HH24:MI:SS

2013-04-28 20:57:01

YYYY-MM-DD"T"HH24:MI

2013-04-28T20:57

Les guillemets doubles autour de T sont facultatifs (voir l’astuce qui suit ce tableau pour plus de détails).

YYYY-MM-DD HH24:MI

2013-04-28 20:57

YYYY-MM-DD"T"HH24

2013-04-28T20

Les guillemets doubles autour de T sont facultatifs (voir l’astuce qui suit ce tableau pour plus de détails).

YYYY-MM-DD HH24

2013-04-28 20

YYYY-MM-DD"T"HH24:MI:SSTZH:TZM

2013-04-28T20:57:01-07:00

Les guillemets doubles autour de T sont facultatifs (voir l’astuce qui suit ce tableau pour plus de détails).

YYYY-MM-DD HH24:MI:SSTZH:TZM

2013-04-28 20:57:01-07:00

YYYY-MM-DD HH24:MI:SSTZH

2013-04-28 20:57:01-07

YYYY-MM-DD"T"HH24:MITZH:TZM

2013-04-28T20:57+07:00

Les guillemets doubles autour de T sont facultatifs (voir l’astuce qui suit ce tableau pour plus de détails).

YYYY-MM-DD HH24:MITZH:TZM

2013-04-28 20:57+07:00

Formats d’horodatage Internet (RFC)

DY, DD MON YYYY HH24:MI:SS TZHTZM

Thu, 21 Dec 2000 16:01:07 +0200

DY, DD MON YYYY HH24:MI:SS.FF TZHTZM

Thu, 21 Dec 2000 16:01:07.123456789 +0200

DY, DD MON YYYY HH12:MI:SS AM TZHTZM

Thu, 21 Dec 2000 04:01:07 PM +0200

DY, DD MON YYYY HH12:MI:SS.FF AM TZHTZM

Thu, 21 Dec 2000 04:01:07.123456789 PM +0200

DY, DD MON YYYY HH24:MI:SS

Thu, 21 Dec 2000 16:01:07

DY, DD MON YYYY HH24:MI:SS.FF

Thu, 21 Dec 2000 16:01:07.123456789

DY, DD MON YYYY HH12:MI:SS AM

Thu, 21 Dec 2000 04:01:07 PM

DY, DD MON YYYY HH12:MI:SS.FF AM

Thu, 21 Dec 2000 04:01:07.123456789 PM

Autres formats d’horodatage

MM/DD/YYYY HH24:MI:SS

2/18/2008 02:36:48

Peut produire des dates incorrectes lors du chargement ou de l’utilisation de dates dans des formats européens classiques (c.-à-d. DD/MM/YYYY). Par exemple, 05/02/2013 pourrait être interprété comme le 2 mai 2013 au lieu du 5 février 2013.

DY MON DD HH24:MI:SS TZHTZM YYYY

Mon Jul 08 18:09:51 +0000 2013

Astuce

Dans certains formats d’horodatage, la lettre T est utilisée comme séparateur entre la date et l’heure (par exemple 'YYYY-MM-DD"T"HH24:MI:SS').

Les guillemets autour du T sont facultatifs. Cependant, Snowflake recommande d’utiliser des guillemets doubles autour du T (et d’autres littéraux) pour éviter toute ambiguïté.

Les guillemets doubles ne doivent figurer que dans le spécificateur de format, pas dans les valeurs réelles. Par exemple :

SELECT TO_TIMESTAMP('2019-02-28T23:59:59', 'YYYY-MM-DD"T"HH24:MI:SS');

De plus, les guillemets autour de T doivent être des guillemets doubles.

Informations supplémentaires

Les sections suivantes décrivent les exigences et les meilleures pratiques pour chaque champ relatifs à la date, l’heure et l’horodatage.

Plages de valeurs valides pour les champs

Les plages de valeurs recommandées pour chaque champ sont indiquées ci-dessous :

Champ

Valeurs

Remarques

Années

0001 jusqu’à 9999

Certaines valeurs hors de cette plage peuvent être acceptées dans certains contextes, mais Snowflake recommande d’utiliser uniquement les valeurs de cette plage. Par exemple, l’année 0000 est acceptée, mais elle est incorrecte, car dans le calendrier grégorien, l’année 1 apr. J.-C. vient immédiatement après l’année 1 av. J.-C. ; il n’y a pas d’année 0.

Mois

01 jusqu’à 12

Jours

01 jusqu’à 31

Au cours des mois comptant moins de 31 jours, le maximum réel correspond au nombre de jours dans le mois.

Heures

00 jusqu’à 23

Ou 00-11 si vous utilisez le format HH12.

Minutes

00 jusqu’à 59

Secondes

00 jusqu’à 59

Snowflake ne prend pas en charge les secondes intercalaires ou les secondes intercalaires additionnelles ; les valeurs 60 et 61 sont rejetées.

Fraction

0 jusqu’à 999999999

Le nombre de chiffres après le point décimal dépend en partie du spécificateur de format exact (par exemple, FF3 prend en charge jusqu’à 3 chiffres après le point décimal et FF9 prend en charge jusqu’à 9 chiffres après le point décimal). Vous pouvez entrer moins de chiffres que ce que vous avez spécifié (par exemple, un chiffre est autorisé même si vous utilisez FF9) ; les zéros de fin ne sont pas requis pour remplir le champ à la largeur spécifiée.

Utilisation du nombre correct de chiffres avec des éléments de format

Pour la plupart des champs (année, mois, jour, heure, minute et seconde), les éléments (YYYY, MM, DD, etc.) du spécificateur de format sont composés de deux ou quatre caractères.

Les règles suivantes indiquent le nombre de chiffres à spécifier dans les valeurs littérales :

  • YYYY: Vous pouvez spécifier un, deux, trois ou quatre chiffres de l’année. Cependant, Snowflake recommande de spécifier quatre chiffres. Si nécessaire, ajoutez des zéros non significatifs. Par exemple, l’année 536 après J.C. serait “0536”.

  • YY: Spécifiez un ou deux chiffres de l’année. Cependant, Snowflake recommande de spécifier deux chiffres. Si nécessaire, ajoutez un zéro non significatif.

  • MM: Spécifiez un ou deux chiffres. Par exemple, janvier peut être représenté par « 01 » ou « 1 ». Snowflake recommande d’utiliser deux chiffres.

  • DD: Spécifiez un ou deux chiffres. Snowflake recommande d’utiliser deux chiffres.

  • HH: Spécifiez un ou deux chiffres. Snowflake recommande d’utiliser deux chiffres.

  • MI: Spécifiez un ou deux chiffres. Snowflake recommande d’utiliser deux chiffres.

  • SS: Spécifiez un ou deux chiffres. Snowflake recommande d’utiliser deux chiffres.

  • FF9: Spécifiez entre un et neuf chiffres (inclus). Snowflake recommande de spécifier le nombre de chiffres significatifs réels. Les zéros de fin ne sont pas requis.

  • TZH: Spécifiez un ou deux chiffres. Snowflake recommande d’utiliser deux chiffres.

  • TZM: Spécifiez un ou deux chiffres. Snowflake recommande d’utiliser deux chiffres.

Snowflake recommande de spécifier le nombre maximal de chiffres pour tous les champs (sauf les fractions de seconde). Utilisez des zéros de début si nécessaire. « 0001-02-03 04:05:06 -07:00 » respecte le format recommandé, par exemple.

Pour les fractions de seconde, les zéros de fin sont facultatifs. En règle générale, il est considéré comme une bonne pratique de ne spécifier que le nombre de chiffres fiables et significatifs. Par exemple, si une mesure de temps est exacte à 3 décimales (millisecondes), le spécifier sous une forme à neuf chiffres (par exemple, « .123000000 ») peut induire en erreur.

Espaces dans les spécificateurs de valeurs et de format

Snowflake applique des espaces correspondants dans certaines situations, mais pas toutes. Par exemple, l’instruction suivante génère une erreur, car il n’y a pas d’espace entre les jours et les heures dans la valeur spécifiée, mais entre DD et HH dans le spécificateur de format :

SELECT TO_TIMESTAMP('2019-02-2823:59:59 -07:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM');

Cependant, l’instruction suivante ne génère pas d’erreur, même si la valeur contient un espace là où le spécificateur n’en a pas :

SELECT TO_TIMESTAMP('2019-02-28 23:59:59.000000000 -07:00', 'YYYY-MM-DDHH24:MI:SS.FF TZH:TZM');

La raison de la différence est que dans le premier cas, les valeurs seraient ambiguës si les champs ne sont pas tous à leur largeur maximale. Par exemple, « 213 » peut être interprété comme 2 jours et 13 heures ou bien comme 21 jours et 3 heures. Cependant, DDHH est sans équivoque identique à DD HH (à l’exception des espaces).

Astuce

Bien que certaines différences d’espace soient autorisées afin de traiter des données à format variable, Snowflake recommande que les valeurs et les spécificateurs correspondent exactement, y compris les espaces.

Dépendance de contexte

Toutes les restrictions ne sont pas appliquées de manière égale dans tous les contextes. Par exemple, certaines expressions appliquent un report pour le 31 février, d’autres non.

Résumé des meilleures pratiques

Ces meilleures pratiques minimisent les ambiguïtés et autres problèmes potentiels dans les versions antérieures, actuelles et futures de Snowflake :

  • Soyez conscient des dangers du mélange de données provenant de sources utilisant différents formats (par exemple, du mélange de données qui suivent le format américain courant MM-DD-YYYY et le format européen courant DD-MM-YYYY).

  • Spécifiez le nombre maximal de chiffres pour chaque champ (sauf les secondes fractionnelles). Par exemple, utilisez des années sur quatre chiffres, en spécifiant des zéros de début, si nécessaire.

  • Spécifiez un espace ou la lettre T entre la date et l’heure dans un horodatage.

  • Les espaces (et le séparateur T facultatif entre la date et l’heure) doivent être identiques dans les valeurs et le spécificateur de format.

  • Utilisez l’arithmétique des intervalles si vous avez besoin de l’équivalent d’un report.

  • Soyez prudent lorsque vous utilisez le formatage AUTO. Si possible, spécifiez le format et assurez-vous que les valeurs correspondent toujours au format spécifié.

  • Il est plus sûr de spécifier le format dans la commande plutôt que de spécifier le format en dehors de la commande, par exemple dans un paramètre tel que DATE_INPUT_FORMAT. (Voir les exemples ci-dessous.)

  • Lorsque vous déplacez des scripts d’un environnement à un autre, assurez-vous que les paramètres liés à la date, tels que DATE_INPUT_FORMAT, sont les mêmes dans le nouvel environnement et dans l’ancien (en supposant que les valeurs ont également le même format).

Fonctions de date et d’heure

Snowflake fournit un ensemble de fonctions pour construire, convertir, extraire ou modifier des données DATE/TIME/TIMESTAMP. Pour plus d’informations, voir Fonctions Date & Heure.

Détection AUTO de valeurs de date, d’heure et d’horodatage stockées en entier

Pour les nombres entiers de secondes ou de millisecondes stockés dans une chaîne, Snowflake tente de déterminer la mesure correcte de l’heure en fonction de la longueur de la valeur. Par exemple, pour calculer l’horodatage équivalent à 1487654321 secondes depuis le début de l’epoch Unix :

select to_timestamp('1487654321');
-------------------------------+
  TO_TIMESTAMP('1487654321')   |
-------------------------------+
 2017-02-20 21:18:41.000000000 |
-------------------------------+

Voici un calcul similaire en millisecondes depuis le début de l’epoch :

select to_timestamp('1487654321321');
-------------------------------+
 TO_TIMESTAMP('1487654321321') |
-------------------------------+
 2017-02-20 21:18:41.321000000 |
-------------------------------+

Selon le type de données cible, une échelle d’entrée différente est supposée :

  • TIME: Snowflake utilise les secondes comme échelle.

  • DATE et TIMESTAMP :

    • Une fois la chaîne convertie en entier, l’entier est traité comme un nombre de secondes, millisecondes, microsecondes ou nanosecondes après le début de l’epoch Unix (1970-01-01 00:00:00.000000000 UTC).

      • Si l’entier est inférieur à 31536000000 (le nombre de millisecondes dans une année), alors la valeur est traitée comme un nombre de secondes.

      • Si la valeur est supérieure ou égale à 31536000000 et inférieure à 31536000000000, la valeur est alors traitée en millisecondes.

      • Si la valeur est supérieure ou égale à 31536000000000 et inférieure à 31536000000000000, la valeur est alors traitée en microsecondes.

      • Si la valeur est supérieure ou égale à 31536000000000000, alors la valeur est traitée comme des nanosecondes.

    • Si plusieurs lignes sont évaluées (par exemple, si l’entrée est le nom de colonne d’une table qui contient plus de deux lignes), la première valeur traitée détermine si toutes les valeurs suivantes sont traitées en secondes, millisecondes, microsecondes ou nanosecondes.

      Si la première valeur est supérieure ou égale à 31536000000, toutes les valeurs seront traitées en millisecondes, même si certaines valeurs restantes sont inférieures à 31536000000. Une logique similaire s’applique aux microsecondes et nanosecondes.

    Prudence

    Actuellement, les valeurs négatives sont toujours traitées comme des secondes. Par exemple, -31536000000000000000 est traité comme un nombre de secondes avant l’année 1970, bien que son échelle implique qu’il est destiné à être utilisé comme nanosecondes.

    Ce comportement pourrait changer à l’avenir.

    Prudence

    Avertissement de dépréciation : Les futures versions de Snowflake pourraient interpréter automatiquement les valeurs entières converties en chaînes comme des secondes plutôt que comme des millisecondes, microsecondes ou nanosecondes. Snowflake vous recommande d’appeler TO_DATE ou TO_TIMESTAMP avec des chaînes qui contiennent des entiers uniquement lorsque ces entiers sont destinés à être interprétés comme des secondes.

Pour réduire le risque d’une détection de format incorrecte, Snowflake signale une erreur si un format de nombre entier seulement est détecté avec tout autre format. Par exemple :

select to_timestamp(column1) from values('1487654321');
-------------------------------+
     TO_TIMESTAMP(COLUMN1)     |
-------------------------------+
 2017-02-20 21:18:41.000000000 |
-------------------------------+

select to_timestamp(column1) from values('2013-04-05');
-------------------------------+
     TO_TIMESTAMP(COLUMN1)     |
-------------------------------+
 2013-04-05 00:00:00.000000000 |
-------------------------------+

select to_timestamp(column1) from values('1487654321'),('2013-04-05');
FAILURE: Timestamp '2013-04-05' is not recognized

select to_timestamp(column1) from values ('2013-04-05'), ('1487654321');
FAILURE: Timestamp '1487654321' is not recognized

Prudence

Pour éviter des résultats inattendus, ne mélangez pas différentes échelles de nombres entiers dans la même colonne, comme les nombres entiers d’échelle de secondes et de millisecondes. Par exemple :

select to_timestamp(column1) from values('1487654321321'),('1487654321');
-------------------------------+
     TO_TIMESTAMP(COLUMN1)     |
-------------------------------+
 2017-02-20 21:18:41.321000000 |
 1970-01-17 21:14:14.321000000 |
-------------------------------+

select to_timestamp(column1) from values('1487654321'),('1487654321321');
--------------------------------+
     TO_TIMESTAMP(COLUMN1)      |
--------------------------------+
 2017-02-20 21:18:41.000000000  |
 49111-11-21 23:28:41.000000000 |
--------------------------------+

Date et heure : bonnes pratiques relatives au format

La détection AUTO détermine habituellement le bon format d’entrée ; cependant, il peut arriver que la détermination soit erronée.

Pour éviter ce cas de figure, Snowflake recommande fortement les bonnes pratiques suivantes (remplacer TO_DATE , DATE ou TO_TIME , TIME par TO_TIMESTAMP, le cas échéant) :

  • Évitez d’utiliser le format AUTO s’il y a des risques de résultats ambigus. Spécifiez une chaîne de format explicite par :

  • Pour les chaînes contenant des valeurs d’entier, spécifiez l’échelle, à l’aide de la syntaxe suivante :

    TO_TIMESTAMP(TO_NUMBER(<string_column>), <scale>)