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é sur 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> )

Où :

timestampFunction ::=
    TO_TIMESTAMP | TO_TIMESTAMP_LTZ | TO_TIMESTAMP_NTZ | TO_TIMESTAMP_TZ

Arguments

Obligatoire :

Une des options :

expr_numérique

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.

expr_date

Une date à convertir en horodatage.

expr_horodatage

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

expr_chaîne

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

entier

Expression qui 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 ci-dessous.

expr_variante

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

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

  • Un horodatage.

  • Un nombre entier de secondes ou de millisecondes.

  • Une chaîne contenant un nombre entier de secondes ou de millisecondes.

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 expr_chaine). Pour plus d’informations, voir Formats de date et d’heure dans Fonctions de conversion.

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

échelle

Spécificateur d’échelle (uniquement pour expr_numérique). 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 nanosecondes, l’échelle = 9.

Notes sur l’utilisation

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

    • Pour les entrées NULL le résultat sera NULL.

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

    • Pour expr_date : l’horodatage représentant l’heure de minuit d’un jour donné sera utilisé, en fonction de la sémantique spécifique de l’horodatage (NTZ/LTZ/TZ).

    • Pour expr_horodatage : un horodatage avec éventuellement une autre valeur que l’horodatage source.

    • Pour expr_numérique : un horodatage représentant le nombre de secondes (ou fractions de seconde) fournies par l’utilisateur. Notez que le temps UTC est toujours utilisé pour construire le résultat.

    • Pour expr_variante :

      • Si la variante contient une valeur JSON null, le résultat sera NULL.

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

      • Si la variante contient une valeur d’horodatage de type différent, la conversion se fera de la même manière qu’à partir de expr_horodatage.

      • Si la variante contient une chaîne de caractères, la conversion à partir d’une valeur de chaîne de caractères sera effectuée (en utilisant le format automatique).

      • Si la variante contient un nombre, la conversion sera effectuée comme à partir de expr_numérique.

        Note

        When an INTEGER value is cast directly to TIMESTAMP_NTZ, the integer is treated as the number of seconds since the beginning of the Linux epoch, and the local time zone is not taken into account. (For details, see TO_TIMESTAMP / TO_TIMESTAMP_*.) However, if the INTEGER value is stored inside a VARIANT value, for example as shown below, then the conversion is indirect, and is affected by the local time zone, even though the final result is TIMESTAMP_NTZ:

        select to_timestamp(parse_json(31000000));
        select parse_json(31000000)::timestamp_ntz;
        

        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(parse_json(31000000)::int);
        select parse_json(31000000)::int::timestamp_ntz;
        

        Cela s’applique que vous convertissiez en TIMESTAMP_NTZ ou 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.

  • 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 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.

Exemples

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

ALTER SESSION SET TIMEZONE = 'America/Los_Angeles';
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
SELECT TO_TIMESTAMP_TZ('2013-04-05 01:02:03');
+----------------------------------------+
| TO_TIMESTAMP_TZ('2013-04-05 01:02:03') |
|----------------------------------------|
| 2013-04-05 01:02:03.000 -0700          |
+----------------------------------------+

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

Cela montre comment différents formats peuvent influer sur l’analyse d’une date ambiguë :

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

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

Ceci montre comment utiliser une entrée numérique qui représente environ 40 années à partir du 1er janvier 1970 à minuit (début de l” EPOCH UNIX) :

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

Ceci est similaire au précédent, mais fournit la valeur en millisecondes en fournissant une valeur scale de 3 :

ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF9 TZH:TZM TZD';
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
SELECT TO_TIMESTAMP_NTZ(40 * 365.25 * 86400 * 1000 + 456, 3);
+-------------------------------------------------------+
| TO_TIMESTAMP_NTZ(40 * 365.25 * 86400 * 1000 + 456, 3) |
|-------------------------------------------------------|
| 2010-01-01 00:00:00.456                               |
+-------------------------------------------------------+

Cet exemple montre comment la première valeur convertie peut contrôler si les valeurs suivantes sont interprétées en secondes ou en millisecondes :

Créer et charger la table :

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

Comparez la sortie des deux requêtes suivantes. La seule différence entre les deux est l’ordre des lignes :

SELECT 
       description, 
       value, 
       TO_TIMESTAMP(value), 
       TO_DATE(value)
    FROM demo1
    ORDER BY value ASC
    ;
+--------------+-------------+-------------------------+----------------+
| DESCRIPTION  | VALUE       | TO_TIMESTAMP(VALUE)     | TO_DATE(VALUE) |
|--------------+-------------+-------------------------+----------------|
| Seconds      | 31535999999 | 2969-05-02 23:59:59.000 | 2969-05-02     |
| Milliseconds | 31536000000 | 2969-05-03 00:00:00.000 | 2969-05-03     |
+--------------+-------------+-------------------------+----------------+
SELECT 
       description, 
       value, 
       TO_TIMESTAMP(value), 
       TO_DATE(value)
    FROM demo1
    ORDER BY value DESC
    ;
+--------------+-------------+-------------------------+----------------+
| DESCRIPTION  | VALUE       | TO_TIMESTAMP(VALUE)     | TO_DATE(VALUE) |
|--------------+-------------+-------------------------+----------------|
| Milliseconds | 31536000000 | 1971-01-01 00:00:00.000 | 1971-01-01     |
| Seconds      | 31535999999 | 1970-12-31 23:59:59.999 | 1970-12-31     |
+--------------+-------------+-------------------------+----------------+

L’exemple suivant 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;
+-------------------------+------------------------------+-----------------------------------+
| 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           |
+-------------------------+------------------------------+-----------------------------------+
SELECT TO_TIMESTAMP_NTZ(0), TO_TIMESTAMP_NTZ(PARSE_JSON(0)), TO_TIMESTAMP_NTZ(PARSE_JSON(0)::INT);
+-------------------------+---------------------------------+--------------------------------------+
| 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              |
+-------------------------+---------------------------------+--------------------------------------+