Categorias:

Funções de conversão , Funções de data e hora

TO_TIMESTAMP / TO_TIMESTAMP_*

Converte uma expressão de entrada no carimbo de data/hora correspondente:

  • TO_TIMESTAMP_LTZ (carimbo de data/hora com fuso horário local)

  • TO_TIMESTAMP_NTZ (carimbo de data/hora sem fuso horário)

  • TO_TIMESTAMP_TZ (carimbo de data/hora com fuso horário)

Nota

TO_TIMESTAMP faz o mapeamento para uma das outras funções de carimbo de data/hora, com base no parâmetro de sessão TIMESTAMP_TYPE_MAPPING. O parâmetro padrão é TIMESTAMP_NTZ, de forma que TO_TIMESTAMP faz o mapeamento para TO_TIMESTAMP_NTZ por padrão.

Consulte também:

TRY_TO_TIMESTAMP / TRY_TO_TIMESTAMP_* ,

AS_TIMESTAMP_* , IS_TIMESTAMP_* ,

TO_DATE , DATE , TO_TIME , TIME

Sintaxe

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

timestampFunction ( <date_expr> )

timestampFunction ( <timestamp_expr> )

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

timestampFunction ( '<integer>' )

timestampFunction ( <variant_expr> )
Copy

Onde:

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

Argumentos

Obrigatório:

Uma das opções:

numeric_expr

Um número de segundos (se escala = 0 ou está ausente) ou frações de segundo (por exemplo, milissegundos ou nanossegundos) desde o início da época Unix (1970-01-01 00:00:00 UTC). Se uma expressão decimal de números não inteiros for inserida, a escala do resultado é herdada.

date_expr

Uma data a ser convertida em um carimbo de data/hora.

timestamp_expr

Um carimbo de data/hora a ser convertido em outro carimbo de data/hora (por exemplo, conversão de TIMESTAMP_LTZ para TIMESTAMP_NTZ).

string_expr

Uma cadeia de caracteres da qual se pode extrair um carimbo de data/hora, por exemplo, '2019-01-31 01:02:03.004'.

'integer'

Uma expressão que avalia como uma cadeia de caracteres contendo um número inteiro, por exemplo '15000000'. Dependendo da magnitude da cadeia de caracteres, ela pode ser interpretada como segundos, milissegundos, microssegundos ou nanossegundos. Para obter mais detalhes, consulte notas de uso.

variant_expr

Uma expressão do tipo VARIANT. O VARIANT deve conter um dos seguintes:

  • Uma cadeia de caracteres da qual se pode extrair um carimbo de data/hora.

  • Um carimbo de data/hora.

  • Um número inteiro que representa o número de segundos, milissegundos, microssegundos ou nanossegundos.

  • Uma cadeia de caracteres contendo um inteiro que representa o número de segundos, milissegundos, microssegundos ou nanossegundos.

Embora TO_TIMESTAMP aceite um valor DATE, não aceita um DATE dentro de um VARIANT.

Opcional:

format

Especificador do formato (somente para string_expr). Para obter mais informações, consulte Formatos de data e hora em funções de conversão.

O valor padrão é o valor atual do parâmetro TIMESTAMP_INPUT_FORMAT (padrão AUTO).

scale

Especificador de escala (somente para numeric_expr). Se especificado, define a escala dos números fornecidos. Por exemplo:

  • Por segundos, escala = 0.

  • Para milissegundos, escala = 3.

  • Para microssegundos, escala = 6.

  • Para nanossegundos, escala = 9.

Padrão: 0

Retornos

O tipo de dados do valor retornado é um dos tipos de dados de TIMESTAMP. Por padrão, o tipo de dados é TIMESTAMP_NTZ. Você pode alterar isso definindo o parâmetro de sessão TIMESTAMP_TYPE_MAPPING.

Se a entrada for NULL, o resultado será NULL.

Notas de uso

  • Esta família de funções retorna valores de carimbo de data/hora, especificamente:

    • Para string_expr: um carimbo de data/hora representado por uma determinada cadeia de caracteres. Se a cadeia de caracteres não tiver um componente de hora, será usada a meia-noite.

    • Para date_expr: um carimbo de data/hora representando a meia-noite de um determinado dia é usado, de acordo com a semântica de mapeamento do carimbo de data/hora específico (NTZ/LTZ/TZ).

    • Para timestamp_expr: um carimbo de data/hora com mapeamento possivelmente diferente do carimbo de data/hora de origem.

    • Para numeric_expr: um carimbo de data/hora representando o número de segundos (ou frações de segundo) fornecido pelo usuário. A hora UTC é sempre usada para criar o resultado.

    • Para variant_expr:

      • Se VARIANT contiver um valor nulo JSON, o resultado será NULL.

      • Se VARIANT contiver um valor de carimbo de data/hora do mesmo tipo do resultado, este valor será preservado dessa maneira.

      • Se VARIANT contiver um valor de carimbo de data/hora de tipo diferente, a conversão será feita da mesma maneira que timestamp_expr.

      • Se VARIANT contiver uma cadeia de caracteres, a conversão de um valor de cadeia de caracteres será realizada (usando o formato automático).

      • Se VARIANT contiver um número, a conversão de numeric_expr é realizada.

        Nota

        Quando um valor INTEGER é convertido diretamente em TIMESTAMP_NTZ, o número inteiro é tratado como o número de segundos desde o início da época Linux, e o fuso horário local não é levado em conta. No entanto, se o valor INTEGER for armazenado dentro de um valor VARIANT, por exemplo, como mostrado abaixo, então a conversão é indireta e é afetada pelo fuso horário local, mesmo que o resultado final seja TIMESTAMP_NTZ:

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

        O carimbo de data/hora retornado pela primeira consulta é diferente da hora retornada pela segunda e terceira consultas.

        Para fazer a conversão independentemente do fuso horário local, acrescente uma conversão explícita ao número inteiro na expressão, como mostrado abaixo:

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

        O carimbo de data/hora retornado por todas as três consultas é o mesmo. Isso se aplica tanto para converter para TIMESTAMP_NTZ quanto para chamar a função TO_TIMESTAMP_NTZ. Isso também se aplica ao chamar TO_TIMESTAMP quando o parâmetro TIMESTAMP_TYPE_MAPPING é definido como TIMESTAMP_NTZ.

        Para um exemplo com saída, consulte os exemplos no final fim tópico.

    • Se a conversão não for possível, um erro é retornado.

  • Para carimbos de data/hora com fuso horário, o ajuste do parâmetro TIMEZONE afeta o valor de retorno. O carimbo de data/hora retornado está no fuso horário da sessão.

  • O formato de exibição dos carimbos de data/hora na saída é determinado pelo formato de saída do registro de data e hora que corresponde à função (TIMESTAMP_OUTPUT_FORMAT, TIMESTAMP_LTZ_OUTPUT_FORMAT, TIMESTAMP_NTZ_OUTPUT_FORMAT ou TIMESTAMP_TZ_OUTPUT_FORMAT).

  • Se o formato do parâmetro de entrada for uma cadeia de caracteres que contenha um número inteiro:

    • Depois que a cadeia de caracteres é convertida em um inteiro, o inteiro é tratado como um número de segundos, milissegundos, microssegundos ou nanossegundos após o início da época Unix (1970-01-01 00:00:00.000000000 UTC).

      • Se o número inteiro for inferior a 31536000000 (o número de milissegundos em um ano), então o valor é tratado como um número de segundos.

      • Se o valor for maior ou igual a 31536000000 e menor que 31536000000000, então o valor é tratado como milissegundos.

      • Se o valor for maior ou igual a 31536000000000 e menor que 31536000000000000, então o valor é tratado como microssegundos.

      • Se o valor for maior ou igual a 31536000000000000, então o valor é tratado como nanossegundos.

    • Se mais de uma linha for avaliada (por exemplo, se a entrada for o nome da coluna de uma tabela que contém mais de uma linha), cada valor será examinado independentemente para determinar se o valor representa segundos, milissegundos, microssegundos ou nanossegundos.

  • Quando você usa a função TO_TIMESTAMP_NTZ ou TRY_TO_TIMESTAMP_NTZ para converter um carimbo de data/hora com informações de fuso horário, as informações de fuso horário são perdidas. Se o carimbo de data/hora for então convertido novamente em um carimbo de data/hora com informações de fuso horário (usando a função TO_TIMESTAMP_TZ, por exemplo), as informações de fuso horário não são recuperáveis.

Exemplos

Este exemplo mostra que TO_TIMESTAMP_TZ cria um carimbo de data/hora que contém um fuso horário da sessão, mas o valor de TO_TIMESTAMP_NTZ não tem um fuso horário:

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                 |
+-----------------------------------------+

Os exemplos a seguir mostram como diferentes formatos podem influenciar a análise de uma data ambígua. Suponha que TIMESTAMP_TZ_OUTPUT_FORMAT não está definido, então TIMESTAMP_OUTPUT_FORMAT é usado e definido como padrão (YYYY-MM-DD HH24:MI:SS.FF3 TZHTZM).

Este exemplo mostra os resultados quando o formato de entrada é mm/dd/yyyy hh24:mi:ss (mês/dia/ano):

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                                   |
+-----------------------------------------------------------------+

Este exemplo mostra os resultados quando o formato de entrada é dd/mm/yyyy hh24:mi:ss (dia/mês/ano):

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                                   |
+-----------------------------------------------------------------+

Este exemplo mostra como usar uma entrada numérica que representa aproximadamente 40 anos a partir da meia-noite de 1.º de janeiro de 1970 (o início da época Unix): A escala não é especificada, então a escala padrão de 0 (segundos) é usada.

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               |
+---------------------------------------+

Este exemplo é semelhante ao exemplo anterior, mas fornece o valor em milissegundos especificando um valor de escala de 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                               |
+-------------------------------------------------------+

Este exemplo mostra como os resultados mudam quando diferentes valores de escala são especificados para o mesmo valor numérico:

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 |
+-------------------------+-------------------------+-------------------------+-------------------------+

Esse exemplo mostra como a função determina as unidades a serem usadas (segundos, milissegundos, microssegundos ou nanossegundos) quando a entrada é uma cadeia de caracteres que contém um número inteiro, com base na magnitude do valor.

Crie e carregue a tabela com cadeias de caracteres contendo números inteiros dentro de diferentes faixas:

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

Passe as cadeias de caracteres para a função:

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     |
+--------------+-------------------+-------------------------+----------------+

O exemplo a seguir converte valores em TIMESTAMP_NTZ. O exemplo mostra a diferença de comportamento entre o uso de um número inteiro e o uso de uma variante que contém um número inteiro:

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           |
+-------------------------+------------------------------+-----------------------------------+

Os carimbos de data/hora retornados correspondem a um inteiro e a uma variante convertida em um inteiro na primeira e terceira colunas, mas o carimbo de data/hora retornado é diferente para a variante que não é convertida em um inteiro na segunda coluna. Para obter mais informações, consulte Notas de uso.

Este mesmo comportamento se aplica ao chamar a função 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              |
+-------------------------+---------------------------------+--------------------------------------+
Linguagem: Português