Catégories :

Fonctions de conversion, Fonctions de date et d’heure

TO_TIMESTAMP / TO_TIMESTAMP_*

Convertit une expression d’entrée en horodatage correspondant :

  • TO_TIMESTAMP_LTZ (horodatage avec fuseau horaire local)

  • TO_TIMESTAMP_NTZ (horodatage sans fuseau horaire)

  • TO_TIMESTAMP_TZ (horodatage avec fuseau horaire)

Note

TO_TIMESTAMP mappe vers l’une des autres fonctions d’horodatage, en fonction du paramètre de session TIMESTAMP_TYPE_MAPPING. Le paramètre par défaut est TIMESTAMP_NTZ, de sorte que TO_TIMESTAMP est mappé vers TO_TIMESTAMP_NTZ par défaut.

Voir aussi :

TRY_TO_TIMESTAMP / TRY_TO_TIMESTAMP_* ,

AS_TIMESTAMP_* , IS_TIMESTAMP_* ,

TO_DATE , DATE , TO_TIME , TIME

Syntaxe

timestampFunction ( <numeric_expr> [ , <scale> ] )

timestampFunction ( <date_expr> )

timestampFunction ( <timestamp_expr> )

timestampFunction ( <string_expr> [ , <format> ] )

timestampFunction ( '<integer>' )

timestampFunction ( <variant_expr> )
Copy

Où :

timestampFunction ::=
    TO_TIMESTAMP | TO_TIMESTAMP_LTZ | TO_TIMESTAMP_NTZ | TO_TIMESTAMP_TZ
Copy

Arguments

Obligatoire :

Une des options :

numeric_expr

Un nombre de secondes (si l’échelle = 0 ou est absente) ou des fractions de secondes (par exemple, millisecondes ou nanosecondes) depuis le début de l’epoch Unix (1970-01-01 00:00:00 UTC). Si une expression décimale non entière est entrée, l’échelle du résultat est héritée.

date_expr

Une date à convertir en horodatage.

timestamp_expr

Un horodatage à convertir en un autre horodatage (par exemple, convertir TIMESTAMP_LTZ en TIMESTAMP_NTZ).

string_expr

Chaîne à partir de laquelle extraire un horodatage, par exemple '2019-01-31 01:02:03.004'.

'integer'

Expression qui est évaluée sur (correspond à) une chaîne contenant un entier, par exemple '15000000'. Selon l’ampleur de la chaîne, elle peut être interprétée comme des secondes, des millisecondes, des microsecondes ou des nanosecondes. Pour plus de détails, voir les Notes sur l’utilisation.

variant_expr

Expression de type VARIANT. VARIANT doit contenir l’un des éléments suivants :

  • Une chaîne à partir de laquelle extraire un horodatage.

  • Un horodatage.

  • Entier représentant le nombre de secondes, de millisecondes, de microsecondes ou de nanosecondes.

  • Chaîne contenant un entier représentant le nombre de secondes, de millisecondes, de microsecondes ou de nanosecondes.

Bien que TO_TIMESTAMP accepte une valeur DATE, il n’accepte pas de DATE dans un VARIANT.

Facultatif :

format

Spécificateur de format (uniquement pour string_expr). Pour plus d’informations, voir Formats de date et d’heure dans les fonctions de conversion.

La valeur par défaut est la valeur actuelle du paramètre TIMESTAMP_INPUT_FORMAT (par défaut, AUTO).

scale

Spécificateur d’échelle (uniquement pour numeric_expr). Si spécifié, définit l’échelle des nombres fournis. Par exemple :

  • Pour les secondes, l’échelle = 0.

  • Pour les millisecondes, l’échelle = 3.

  • Pour les microsecondes, l’échelle = 6.

  • Pour les nanosecondes, l’échelle = 9.

Par défaut : 0

Renvoie

Le type de données de la valeur renvoyée est l’un des types de données TIMESTAMP. Par défaut, le type de données est TIMESTAMP_NTZ. Vous pouvez changer cela en définissant le paramètre de session TIMESTAMP_TYPE_MAPPING.

Si l’entrée est NULL, le résultat est NULL.

Notes sur l’utilisation

  • Cette famille de fonctions renvoie des valeurs d’horodatage, en particulier :

    • Pour string_expr : horodatage représenté par une chaîne donnée. Si la chaîne n’a pas de composante temporelle, minuit est utilisé.

    • Pour date_expr : l’horodatage représentant minuit d’un jour donné est utilisé, conformément à la sémantique de mappage d’horodatage spécifique (NTZ/LTZ/TZ).

    • Pour timestamp_expr : horodatage avec un mappage éventuellement différent de l’horodatage source.

    • Pour numeric_expr : horodatage représentant le nombre de secondes (ou fractions de seconde) fournies par l’utilisateur. L’heure UTC est toujours utilisée pour générer le résultat.

    • Pour variant_expr :

      • Si la VARIANT contient une valeur nulle JSON, le résultat est NULL.

      • Si la VARIANT contient une valeur d’horodatage du même type que le résultat, cette valeur est conservée telle quelle.

      • Si la VARIANT contient une valeur d’horodatage de type différent, la conversion s’effectue de la même manière qu’à partir de timestamp_expr.

      • Si la VARIANT contient une chaîne, la conversion à partir d’une valeur de chaîne est effectuée (en utilisant le format automatique).

      • Si la VARIANT contient un nombre, une conversion à partir de numeric_expr est effectuée.

        Note

        Lorsqu’une valeur INTEGER est directement convertie en TIMESTAMP_NTZ, l’entier est traité comme le nombre de secondes depuis le début de l’époque Linux et le fuseau horaire local n’est pas pris en compte. Cependant, si la valeur INTEGER est stockée dans une valeur VARIANT, par exemple, comme illustré ci-dessous, la conversion est indirecte et affectée par le fuseau horaire local, même si le résultat final est TIMESTAMP_NTZ :

        SELECT TO_TIMESTAMP(31000000);
SELECT TO_TIMESTAMP(PARSE_JSON(31000000));
SELECT PARSE_JSON(31000000)::TIMESTAMP_NTZ;
        Copy

        L’horodatage renvoyé par la première requête est différent de l’heure renvoyée par les deuxième et troisième requêtes.

        Pour convertir indépendamment du fuseau horaire local, ajoutez une conversion explicite de l’entier dans l’expression, comme indiqué ci-dessous :

        SELECT TO_TIMESTAMP(31000000);
SELECT TO_TIMESTAMP(PARSE_JSON(31000000)::INT);
SELECT PARSE_JSON(31000000)::INT::TIMESTAMP_NTZ;
        Copy

        L’horodatage renvoyé par les trois requêtes est le même. Cela s’applique, que vous réalisiez une conversion en TIMESTAMP_NTZ ou que vous appeliez la fonction TO_TIMESTAMP_NTZ. Cela s’applique également lors de l’appel de TO_TIMESTAMP lorsque le paramètre TIMESTAMP_TYPE_MAPPING est défini sur TIMESTAMP_NTZ.

        Pour un exemple avec sortie, consultez les exemples à la fin de cette rubrique.

    • Si la conversion n’est pas possible, une erreur est renvoyée.

  • Pour les horodatages avec des fuseaux horaires, la définition du paramètre TIMEZONE affecte la valeur de renvoi. L’horodatage renvoyé se situe dans le fuseau horaire de la session.

  • Le format d’affichage des horodatages dans la sortie est déterminé par le format de sortie d’horodatage qui correspond à la fonction (TIMESTAMP_OUTPUT_FORMAT, TIMESTAMP_LTZ_OUTPUT_FORMAT, TIMESTAMP_NTZ_OUTPUT_FORMAT ou TIMESTAMP_TZ_OUTPUT_FORMAT).

  • Si le format du paramètre d’entrée est une chaîne contenant un entier :

    • 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 d’une ligne), chaque valeur est examinée indépendamment pour déterminer si elle représente des secondes, des millisecondes, des microsecondes ou des nanosecondes.

  • Lorsque vous utilisez la fonction TO_TIMESTAMP_NTZ ou TRY_TO_TIMESTAMP_NTZ pour convertir un horodatage avec des informations de fuseau horaire, les informations de fuseau horaire sont perdues. Si l’horodatage est ensuite reconverti en horodatage avec des informations de fuseau horaire (en utilisant la fonction TO_TIMESTAMP_TZ, par exemple), les informations de fuseau horaire ne peuvent pas être récupérées.

Exemples

Cet exemple montre que TO_TIMESTAMP_TZ crée un horodatage contenant un fuseau horaire de la session, mais que la valeur de TO_TIMESTAMP_NTZ n’a pas de fuseau horaire :

ALTER SESSION SET TIMEZONE = 'America/Los_Angeles';
Copy
SELECT TO_TIMESTAMP_TZ('2024-04-05 01:02:03');
Copy
+----------------------------------------+
| TO_TIMESTAMP_TZ('2024-04-05 01:02:03') |
|----------------------------------------|
| 2024-04-05 01:02:03.000 -0700          |
+----------------------------------------+

SELECT TO_TIMESTAMP_NTZ('2024-04-05 01:02:03');
Copy
+-----------------------------------------+
| TO_TIMESTAMP_NTZ('2024-04-05 01:02:03') |
|-----------------------------------------|
| 2024-04-05 01:02:03.000                 |
+-----------------------------------------+

Les exemples suivants montrent comment des formats différents peuvent influer sur l’analyse d’une date ambiguë. Supposons que TIMESTAMP_TZ_OUTPUT_FORMAT ne soit pas défini et que TIMESTAMP_OUTPUT_FORMAT soit donc utilisé et défini sur la valeur par défaut (YYYY-MM-DD HH24:MI:SS.FF3 TZHTZM).

Cet exemple montre les résultats lorsque le format d’entrée est mm/dd/yyyy hh24:mi:ss (mois/jour/année) :

SELECT TO_TIMESTAMP_TZ('04/05/2024 01:02:03', 'mm/dd/yyyy hh24:mi:ss');
Copy
+-----------------------------------------------------------------+
| TO_TIMESTAMP_TZ('04/05/2024 01:02:03', 'MM/DD/YYYY HH24:MI:SS') |
|-----------------------------------------------------------------|
| 2024-04-05 01:02:03.000 -0700                                   |
+-----------------------------------------------------------------+

Cet exemple montre les résultats lorsque le format d’entrée est dd/mm/yyyy hh24:mi:ss (jour/mois/année) :

SELECT TO_TIMESTAMP_TZ('04/05/2024 01:02:03', 'dd/mm/yyyy hh24:mi:ss');
Copy
+-----------------------------------------------------------------+
| TO_TIMESTAMP_TZ('04/05/2024 01:02:03', 'DD/MM/YYYY HH24:MI:SS') |
|-----------------------------------------------------------------|
| 2024-05-04 01:02:03.000 -0700                                   |
+-----------------------------------------------------------------+

Cet exemple montre comment utiliser une entrée numérique qui représente environ 40 ans à partir de minuit le 1er janvier 1970 (début de l’époque Unix). L’échelle n’étant pas spécifiée, l’échelle par défaut 0 (secondes) est utilisée.

ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF9 TZH:TZM';
Copy
SELECT TO_TIMESTAMP_NTZ(40 * 365.25 * 86400);
Copy
+---------------------------------------+
| TO_TIMESTAMP_NTZ(40 * 365.25 * 86400) |
|---------------------------------------|
| 2010-01-01 00:00:00.000               |
+---------------------------------------+

Cet exemple est similaire à l’exemple précédent, mais fournit la valeur en millisecondes en spécifiant une valeur d’échelle 3 :

SELECT TO_TIMESTAMP_NTZ(40 * 365.25 * 86400 * 1000 + 456, 3);
Copy
+-------------------------------------------------------+
| TO_TIMESTAMP_NTZ(40 * 365.25 * 86400 * 1000 + 456, 3) |
|-------------------------------------------------------|
| 2010-01-01 00:00:00.456                               |
+-------------------------------------------------------+

Cet exemple montre comment les résultats changent lorsque des valeurs d’échelle différentes sont spécifiées pour la même valeur numérique :

SELECT TO_TIMESTAMP(1000000000, 0) AS "Scale in seconds",
       TO_TIMESTAMP(1000000000, 3) AS "Scale in milliseconds",
       TO_TIMESTAMP(1000000000, 6) AS "Scale in microseconds",
       TO_TIMESTAMP(1000000000, 9) AS "Scale in nanoseconds";
Copy
+-------------------------+-------------------------+-------------------------+-------------------------+
| Scale in seconds        | Scale in milliseconds   | Scale in microseconds   | Scale in nanoseconds    |
|-------------------------+-------------------------+-------------------------+-------------------------|
| 2001-09-09 01:46:40.000 | 1970-01-12 13:46:40.000 | 1970-01-01 00:16:40.000 | 1970-01-01 00:00:01.000 |
+-------------------------+-------------------------+-------------------------+-------------------------+

Cet exemple montre comment la fonction détermine les unités à utiliser (secondes, millisecondes, microsecondes ou nanosecondes) lorsque l’entrée est une chaîne contenant un entier, en fonction de l’ampleur de la valeur.

Créer et charger la table avec des chaînes contenant des entiers dans différentes plages :

CREATE OR REPLACE TABLE demo1 (
  description VARCHAR,
  value VARCHAR -- string rather than bigint
);

INSERT INTO demo1 (description, value) VALUES
  ('Seconds',      '31536000'),
  ('Milliseconds', '31536000000'),
  ('Microseconds', '31536000000000'),
  ('Nanoseconds',  '31536000000000000');
Copy

Passez les chaînes à la fonction :

SELECT description,
       value,
       TO_TIMESTAMP(value),
       TO_DATE(value)
  FROM demo1
  ORDER BY value;
Copy
+--------------+-------------------+-------------------------+----------------+
| DESCRIPTION  | VALUE             | TO_TIMESTAMP(VALUE)     | TO_DATE(VALUE) |
|--------------+-------------------+-------------------------+----------------|
| Seconds      | 31536000          | 1971-01-01 00:00:00.000 | 1971-01-01     |
| Milliseconds | 31536000000       | 1971-01-01 00:00:00.000 | 1971-01-01     |
| Microseconds | 31536000000000    | 1971-01-01 00:00:00.000 | 1971-01-01     |
| Nanoseconds  | 31536000000000000 | 1971-01-01 00:00:00.000 | 1971-01-01     |
+--------------+-------------------+-------------------------+----------------+

L’exemple suivant convertit des valeurs en TIMESTAMP_NTZ. L’exemple montre la différence de comportement entre l’utilisation d’un entier et l’utilisation d’une variante qui contient un entier :

SELECT 0::TIMESTAMP_NTZ, PARSE_JSON(0)::TIMESTAMP_NTZ, PARSE_JSON(0)::INT::TIMESTAMP_NTZ;
Copy
+-------------------------+------------------------------+-----------------------------------+
| 0::TIMESTAMP_NTZ        | PARSE_JSON(0)::TIMESTAMP_NTZ | PARSE_JSON(0)::INT::TIMESTAMP_NTZ |
|-------------------------+------------------------------+-----------------------------------|
| 1970-01-01 00:00:00.000 | 1969-12-31 16:00:00.000      | 1970-01-01 00:00:00.000           |
+-------------------------+------------------------------+-----------------------------------+

Les horodatages renvoyés correspondent à un entier et à la conversion d’une variante en entier dans les première et troisième colonnes, mais l’horodatage renvoyé est différent pour la variante qui n’est pas convertie en entier dans la deuxième colonne. Pour plus d’informations, voir Notes sur l’utilisation.

Ce même comportement s’applique lors de l’appel de la fonction TO_TIMESTAMP_NTZ :

SELECT TO_TIMESTAMP_NTZ(0), TO_TIMESTAMP_NTZ(PARSE_JSON(0)), TO_TIMESTAMP_NTZ(PARSE_JSON(0)::INT);
Copy
+-------------------------+---------------------------------+--------------------------------------+
| TO_TIMESTAMP_NTZ(0)     | TO_TIMESTAMP_NTZ(PARSE_JSON(0)) | TO_TIMESTAMP_NTZ(PARSE_JSON(0)::INT) |
|-------------------------+---------------------------------+--------------------------------------|
| 1970-01-01 00:00:00.000 | 1969-12-31 16:00:00.000         | 1970-01-01 00:00:00.000              |
+-------------------------+---------------------------------+--------------------------------------+
Langage: Français