Tipos de dados de data e hora

Este tópico descreve os tipos de dados compatíveis com o Snowflake para gerenciar datas, horas e carimbos de data/hora (data + hora combinadas). Também descreve os formatos compatíveis para as constantes de cadeias de caracteres utilizadas na manipulação de datas, horas e carimbos de data/hora.

Neste tópico:

Tipos de dados

DATE

O Snowflake oferece suporte a apenas um tipo de dados de DATE para armazenamento de datas (sem elementos de hora).

DATE aceita datas nas formas mais comuns (YYYY-MM-DD, DD-MON-YYYY etc.).

Além disso, todos os valores de TIMESTAMP aceitos são entradas válidas para datas; entretanto, as informações de TIME são truncadas.

Para dados de DATE e TIMESTAMP, o Snowflake recomenda usar anos entre 1582 e 9999. O Snowflake oferece suporte a alguns anos fora deste intervalo, mas anos anteriores a 1582 devem ser evitados devido a limitações no calendário gregoriano.

DATETIME

DATETIME é um alias para TIMESTAMP_NTZ.

TIME

O Snowflake oferece suporte a apenas um tipo de dados de TIME para armazenamento de hora no formato de HH:MI:SS.

TIME oferece suporte a um parâmetro de precisão opcional para segundos fracionários, por exemplo TIME(3). A precisão do tempo pode variar de 0 (segundos) a 9 (nanossegundos). A precisão padrão é 9.

Todos os valores de TIME devem estar entre 00:00:00 e 23:59:59.999999999. TIME armazena internamente a hora do “relógio de parede”, e todas as operações com valores de TIME são realizadas sem levar em consideração o fuso horário.

TIMESTAMP

TIMESTAMP no Snowflake é um alias especificado pelo usuário associado a uma das variações de TIMESTAMP_*. Em todas as operações em que TIMESTAMP é usado, a variação associada de TIMESTAMP_* é automaticamente usada. O tipo de dados de TIMESTAMP nunca é armazenado em tabelas.

A variação de TIMESTAMP_* associada ao TIMESTAMP é especificada pelo parâmetro de sessão TIMESTAMP_TYPE_MAPPING. O padrão é TIMESTAMP_NTZ.

Para dados de DATE e TIMESTAMP, o Snowflake recomenda usar anos entre 1582 e 9999. O Snowflake oferece suporte a alguns anos fora deste intervalo, mas anos anteriores a 1582 devem ser evitados devido a limitações no calendário gregoriano.

Todas as variações do carimbo de data/hora, assim como o alias de TIMESTAMP, oferecem suporte a um parâmetro de precisão opcional de segundos fracionários, por exemplo TIMESTAMP(3). A precisão do carimbo de data/hora pode variar de 0 (segundos) a 9 (nanossegundos). A precisão padrão é 9.

TIMESTAMP_LTZ , TIMESTAMP_NTZ , TIMESTAMP_TZ

O Snowflake oferece suporte a três variações de carimbo de data/hora.

TIMESTAMP_LTZ:

TIMESTAMP_LTZ armazena internamente a hora UTC com uma precisão especificada. Entretanto, todas as operações são realizadas no fuso horário da sessão atual, controlado pelo parâmetro de sessão TIMEZONE.

Aliases para TIMESTAMP_LTZ:

  • TIMESTAMPLTZ

  • TIMESTAMP WITH LOCAL TIME ZONE

TIMESTAMP_NTZ:

TIMESTAMP_NTZ armazena internamente a hora do “relógio de parede” com uma precisão especificada. Todas as operações são realizadas sem considerar o fuso horário.

Se o formato de saída contiver um fuso horário, o indicador de UTC (Z) é exibido.

TIMESTAMP_NTZ é o padrão para TIMESTAMP.

Aliases para TIMESTAMP_NTZ:

  • TIMESTAMPNTZ

  • TIMESTAMP WITHOUT TIME ZONE

TIMESTAMP_TZ:

TIMESTAMP_TZ armazena internamente a hora UTC junto com um deslocamento de fuso horário associado. Quando não é fornecido um fuso horário, é utilizado o deslocamento de fuso horário da sessão. Todas as operações são realizadas com o deslocamento de fuso horário específico para cada registro.

Aliases para TIMESTAMP_TZ:

  • TIMESTAMPTZ

  • TIMESTAMP WITH TIME ZONE

Os valores de TIMESTAMP_TZ são comparados com base em suas horas em UTC. Por exemplo, a seguinte comparação entre diferentes horas em diferentes fusos horários retorna TRUE porque os dois valores têm horas equivalentes em UTC.

select '2021-01-01 00:00:00 +0000'::timestamp_tz = '2021-01-01 01:00:00 +0100'::timestamp_tz;
Copy

Atenção

Atualmente, TIMESTAMP_TZ armazena apenas o deslocamento de um determinado fuso horário, não o fuso horário real, no momento da criação para um determinado valor. É especialmente importante considerar isso quando se trata de horário de verão, que não é utilizado pelo UTC.

Por exemplo, com o parâmetro TIMEZONE definido como "America/Los_Angeles", a conversão de um valor para TIMESTAMP_TZ em janeiro de um determinado ano armazena o deslocamento de fuso horário de -0800. Se depois 6 meses forem adicionados ao valor, o deslocamento de -0800 é mantido, mesmo que em julho o deslocamento para Los Angeles seja -0700. Isto porque, depois que o valor é criado, a informação do fuso horário real ("America/Los_Angeles") não está mais disponível. A seguinte amostra de código ilustra esse comportamento:

SELECT '2017-01-01 12:00:00'::TIMESTAMP_TZ;

-------------------------------------+
 '2017-01-01 12:00:00'::TIMESTAMP_TZ |
-------------------------------------+
 2017-01-01 12:00:00 -0800           |
-------------------------------------+

SELECT DATEADD(MONTH, 6, '2017-01-01 12:00:00'::TIMESTAMP_TZ);

--------------------------------------------------------+
 DATEADD(MONTH, 6, '2017-01-01 12:00:00'::TIMESTAMP_TZ) |
--------------------------------------------------------+
 2017-07-01 12:00:00 -0800                              |
--------------------------------------------------------+
Copy

Exemplos de carimbo de data/hora

Criar tabela usando diferentes carimbos de data/hora:

-- First, use TIMESTAMP (mapped to TIMESTAMP_NTZ)

ALTER SESSION SET TIMESTAMP_TYPE_MAPPING = TIMESTAMP_NTZ;

CREATE OR REPLACE TABLE ts_test(ts TIMESTAMP);

DESC TABLE ts_test;

+------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name | type             | kind   | null? | default | primary key | unique key | check | expression | comment |
|------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| TS   | TIMESTAMP_NTZ(9) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+

-- Next, explicitly use one of the TIMESTAMP variations (TIMESTAMP_LTZ)

CREATE OR REPLACE TABLE ts_test(ts TIMESTAMP_LTZ);

DESC TABLE ts_test;

+------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name | type             | kind   | null? | default | primary key | unique key | check | expression | comment |
|------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| TS   | TIMESTAMP_LTZ(9) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+
Copy

Usar TIMESTAMP_LTZ com fusos horários diferentes:

CREATE OR REPLACE TABLE ts_test(ts timestamp_ltz);

ALTER SESSION SET TIMEZONE = 'America/Los_Angeles';

INSERT INTO ts_test values('2014-01-01 16:00:00');
INSERT INTO ts_test values('2014-01-02 16:00:00 +00:00');

-- Note that the time for January 2nd is 08:00 in Los Angeles (which is 16:00 in UTC)

SELECT ts, hour(ts) FROM ts_test;

+---------------------------------+----------+
| TS                              | HOUR(TS) |
|---------------------------------+----------|
| Wed, 01 Jan 2014 16:00:00 -0800 |       16 |
| Thu, 02 Jan 2014 08:00:00 -0800 |        8 |
+---------------------------------+----------+

-- Next, note that the times change with a different time zone

ALTER SESSION SET TIMEZONE = 'America/New_York';

SELECT ts, hour(ts) FROM ts_test;

+---------------------------------+----------+
| TS                              | HOUR(TS) |
|---------------------------------+----------|
| Wed, 01 Jan 2014 19:00:00 -0500 |       19 |
| Thu, 02 Jan 2014 11:00:00 -0500 |       11 |
+---------------------------------+----------+
Copy

Usar TIMESTAMP_NTZ:

CREATE OR REPLACE TABLE ts_test(ts timestamp_ntz);

ALTER SESSION SET TIMEZONE = 'America/Los_Angeles';

INSERT INTO ts_test values('2014-01-01 16:00:00');
INSERT INTO ts_test values('2014-01-02 16:00:00 +00:00');

-- Note that both times from different time zones are converted to the same "wallclock" time

SELECT ts, hour(ts) FROM ts_test;

+---------------------------+----------+
| TS                        | HOUR(TS) |
|---------------------------+----------|
| Wed, 01 Jan 2014 16:00:00 |       16 |
| Thu, 02 Jan 2014 16:00:00 |       16 |
+---------------------------+----------+

-- Next, note that changing the session time zone does not influence the results

ALTER SESSION SET TIMEZONE = 'America/New_York';

SELECT ts, hour(ts) FROM ts_test;

+---------------------------+----------+
| TS                        | HOUR(TS) |
|---------------------------+----------|
| Wed, 01 Jan 2014 16:00:00 |       16 |
| Thu, 02 Jan 2014 16:00:00 |       16 |
+---------------------------+----------+
Copy

Usar TIMESTAMP_TZ:

CREATE OR REPLACE TABLE ts_test(ts timestamp_tz);

ALTER SESSION SET TIMEZONE = 'America/Los_Angeles';

INSERT INTO ts_test values('2014-01-01 16:00:00');
INSERT INTO ts_test values('2014-01-02 16:00:00 +00:00');

-- Note that the January 1st record inherited the session time zone,
-- and "America/Los_Angeles" was converted into a numeric time zone offset

SELECT ts, hour(ts) FROM ts_test;

+---------------------------------+----------+
| TS                              | HOUR(TS) |
|---------------------------------+----------|
| Wed, 01 Jan 2014 16:00:00 -0800 |       16 |
| Thu, 02 Jan 2014 16:00:00 +0000 |       16 |
+---------------------------------+----------+

-- Next, note that changing the session time zone does not influence the results

ALTER SESSION SET TIMEZONE = 'America/New_York';

SELECT ts, hour(ts) FROM ts_test;

+---------------------------------+----------+
| TS                              | HOUR(TS) |
|---------------------------------+----------|
| Wed, 01 Jan 2014 16:00:00 -0800 |       16 |
| Thu, 02 Jan 2014 16:00:00 +0000 |       16 |
+---------------------------------+----------+
Copy

Calendário com suporte

O Snowflake usa o calendário gregoriano para todas as datas e carimbos de data/hora. O calendário gregoriano começa no ano 1582, mas reconhece os anos anteriores, o que é importante observar porque o Snowflake não ajusta datas anteriores a 1582 (ou faz cálculos envolvendo datas anteriores a 1582) para corresponder ao calendário juliano. O elemento de formato UUUU oferece suporte a anos negativos.

Formatos de data e hora

Todos esses tipos de dados aceitam a maioria dos formatos de data, hora ou data + hora razoáveis e não ambíguos. Consulte Formatos com suporte para detecção AUTO para ver os formatos que o Snowflake reconhece quando configurado para detectar o formato automaticamente.

Você também pode especificar o formato de data e hora manualmente. Ao especificar o formato, você pode usar os elementos que não diferenciam maiúsculas e minúsculas listados na tabela a seguir:

Elemento de formato

Descrição

YYYY

Ano com quatro dígitos.

YY

Ano com dois dígitos, controlado pelo parâmetro de sessão TWO_DIGIT_CENTURY_START, por exemplo, quando definido como 1980, valores de 79 e 80 analisados como 2079 e 1980 respectivamente.

MM

Mês com dois dígitos (01 = janeiro, etc.).

MON

Nome do mês completo ou abreviado.

MMMM

Nome completo do mês.

DD

Dia do mês com dois dígitos (01 até 31).

DY

Dia da semana abreviado.

HH24

Hora com dois dígitos (00 até 23). Você não deve especificar AM / PM.

HH12

Hora com dois dígitos (01 até 12). Você pode especificar AM / PM.

AM , PM

Ante meridiem (am) / post meridiem (pm). Use isso somente com HH12 (não com HH24).

MI

Minuto com dois dígitos (00 até 59).

SS

Segundo com dois dígitos (00 até 59).

FF[0-9]

Fracionamento de segundos com precisão 0 (segundos) a 9 (nanossegundos), por exemplo FF, FF0, FF3, FF9. Especificando que FF é equivalente a FF9 (nanossegundos).

TZH:TZM , TZHTZM , TZH

Hora e minuto do fuso horário, ajustados em relação ao UTC. Pode ser prefixado por + / - para sinalizar.

UUUU

Ano de quatro dígitos no formato ISO, que são negativos para anos BCE.

Nota

  • Quando se utiliza um formato somente de data, presume-se que a hora associada seja a meia-noite desse dia.

  • Qualquer elemento no formato entre aspas duplas ou elementos diferentes dos elementos acima é analisado/formatado sem ser interpretado.

  • Para obter mais detalhes sobre intervalos válidos, número de dígitos e práticas recomendadas, consulte Informações adicionais sobre o uso de formatos de data, hora e carimbo de data/hora.

Exemplos de utilização de formatos de data e hora

O exemplo a seguir usa “FF” para indicar que a saída deve ter 9 dígitos no campo de segundos fracionários:

CREATE TABLE timestamp_demo_table(t  TIMESTAMP,
                                  t_tz TIMESTAMP_TZ,
                                  t_ntz TIMESTAMP_NTZ,
                                  t_ltz TIMESTAMP_LTZ);
INSERT INTO timestamp_demo_table (t, t_tz, t_ntz, t_ltz) VALUES (
    '2020-03-12 01:02:03.123456789',
    '2020-03-12 01:02:03.123456789',
    '2020-03-12 01:02:03.123456789',
    '2020-03-12 01:02:03.123456789'
    );
Copy
ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
ALTER SESSION SET TIMESTAMP_TZ_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
ALTER SESSION SET TIMESTAMP_NTZ_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
ALTER SESSION SET TIMESTAMP_LTZ_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
Copy
SELECT t, t_tz, t_ntz, t_ltz 
    FROM timestamp_demo_table;
+-------------------------------+-------------------------------+-------------------------------+-------------------------------+
| T                             | T_TZ                          | T_NTZ                         | T_LTZ                         |
|-------------------------------+-------------------------------+-------------------------------+-------------------------------|
| 2020-03-12 01:02:03.123456789 | 2020-03-12 01:02:03.123456789 | 2020-03-12 01:02:03.123456789 | 2020-03-12 01:02:03.123456789 |
+-------------------------------+-------------------------------+-------------------------------+-------------------------------+
Copy

Constantes de data e hora

Constantes (também conhecidas como literais) referem-se a valores de dados fixos. O Snowflake oferece suporte ao uso de constantes de cadeias de caracteres para especificar valores fixos de data, hora ou carimbo de data/hora. As constantes de cadeias de caracteres devem estar sempre posicionadas entre caracteres delimitadores. O Snowflake oferece suporte ao uso de aspas simples para delimitar as constantes de cadeias de caracteres.

Por exemplo:

date '2010-09-14'
time '10:03:56'
timestamp '2009-09-15 10:59:43'
Copy

A cadeia de caracteres é interpretada como um valor DATE, TIME ou TIMESTAMP com base no formato de entrada para o tipo de dados, conforme definido pelos seguintes parâmetros:

DATE:

DATE_INPUT_FORMAT

TIME:

TIME_INPUT_FORMAT

TIMESTAMP:

TIMESTAMP_INPUT_FORMAT

Por exemplo, para inserir uma data específica em uma coluna em uma tabela:

CREATE TABLE t1 (d1 DATE);

INSERT INTO t1 (d1) VALUES (DATE '2011-10-29');
Copy

Constantes de intervalo

Você pode usar constantes de intervalo para adicionar ou subtrair um período de tempo a/de uma data, hora ou carimbo de data/hora. As constantes de intervalo são implementadas usando a palavra-chave INTERVAL, que tem a seguinte sintaxe:

{ + | - } INTERVAL '<integer> [ <date_time_part> ] [ , <integer> [ <date_time_part> ] ... ]'
Copy

Como em todas as constantes de cadeias de caracteres, o Snowflake exige aspas simples para delimitar as constantes de intervalo.

A palavra-chave INTERVAL permite mais um número inteiro e, opcionalmente, uma ou mais partes de data ou hora. Por exemplo:

  • INTERVAL '1 YEAR' representa 1 ano.

  • INTERVAL '4 years, 5 months, 3 hours' representa 4 anos, 5 meses e 3 horas.

Se uma data ou hora não for especificada, o intervalo representa segundos (por exemplo, INTERVAL '2' é o mesmo que INTERVAL '2 seconds'). Note que isto é diferente da unidade de tempo padrão para realizar a aritmética de datas. Para obter mais detalhes, consulte Aritmética simples para datas (neste tópico).

Para a lista de partes de data e hora compatíveis, consulte Partes compatíveis de data e hora para intervalos (neste tópico).

Nota

  • A ordem dos incrementos de intervalo é importante. Os incrementos são adicionados ou subtraídos na ordem listada. Por exemplo:

    • INTERVAL '1 year, 1 day' primeiro adiciona/subtrai um ano e depois um dia.

    • INTERVAL '1 day, 1 year' primeiro adiciona/subtrai um dia e depois um ano.

    Isto pode afetar os cálculos influenciados por eventos do calendário, como anos bissextos:

    SELECT TO_DATE ('2019-02-28') + INTERVAL '1 day, 1 year';
    
    +---------------------------------------------------+
    | TO_DATE ('2019-02-28') + INTERVAL '1 DAY, 1 YEAR' |
    |---------------------------------------------------|
    | 2020-03-01                                        |
    +---------------------------------------------------+
    
    SELECT TO_DATE ('2019-02-28') + INTERVAL '1 year, 1 day';
    
    +---------------------------------------------------+
    | TO_DATE ('2019-02-28') + INTERVAL '1 YEAR, 1 DAY' |
    |---------------------------------------------------|
    | 2020-02-29                                        |
    +---------------------------------------------------+
    
    Copy
  • INTERVAL não é um tipo de dados (ou seja, não é possível definir uma coluna de tabela para ser do tipo de dados de INTERVAL). Os intervalos só podem ser usados em aritmética de data, hora e carimbo de data/hora.

Partes de data e hora com suporte para intervalos

A palavra-chave INTERVAL permite as seguintes partes de data e hora como argumentos (sem diferenciação de maiúsculas e minúsculas):

Parte de data ou hora

Abreviações/variações

year

y , yy , yyy , yyyy , yr , years , yrs

quarter

q , qtr , qtrs , quarters

month

mm , mon , mons , months

week

w , wk , weekofyear , woy , wy , weeks

day

d , dd , days, dayofmonth

hour

h , hh , hr , hours , hrs

minute

m , mi , min , minutes , mins

second

s , sec , seconds , secs

millisecond

ms , msec , milliseconds

microsecond

us , usec , microseconds

nanosecond

ns , nsec , nanosec , nsecond , nanoseconds , nanosecs , nseconds

Exemplos de intervalos

Adicionar um intervalo de um ano a uma data específica:

select to_date('2018-04-15') + INTERVAL '1 year';

+-------------------------------------------+
| TO_DATE('2018-04-15') + INTERVAL '1 YEAR' |
|-------------------------------------------|
| 2019-04-15                                |
+-------------------------------------------+
Copy

Adicionar um intervalo de 3 horas e 18 minutos a uma hora específica:

select to_time('04:15:29') + INTERVAL '3 hours, 18 minutes';

+------------------------------------------------------+
| TO_TIME('04:15:29') + INTERVAL '3 HOURS, 18 MINUTES' |
|------------------------------------------------------|
| 07:33:29                                             |
+------------------------------------------------------+
Copy

Adicionar um intervalo complexo à saída da função CURRENT_TIMESTAMP:

select current_timestamp + INTERVAL
                           '1 year, 3 quarters, 4 months, 5 weeks, 6 days, 7 minutes, 8 seconds,
                            1000 milliseconds, 4000000 microseconds, 5000000001 nanoseconds'
                        as complex_interval1;

+-------------------------------+
| COMPLEX_INTERVAL              |
|-------------------------------|
| 2020-12-28 08:08:01.325 -0800 |
+-------------------------------+
Copy

Adicionar um intervalo complexo com notação abreviada de parte de data/hora a uma data específica:

select to_date('2025-01-17') + INTERVAL
                               '1 y, 3 q, 4 mm, 5 w, 6 d, 7 h, 9 m, 8 s,
                                1000 ms, 445343232 us, 898498273498 ns'
                            as complex_interval2;

+-------------------------+
| COMPLEX_INTERVAL2       |
|-------------------------|
| 2027-03-30 07:31:32.841 |
+-------------------------+
Copy

Consultar uma tabela de informações de funcionários e retornar os nomes dos funcionários que foram contratados nos últimos 2 anos e 3 meses:

select name, hire_date from employees where hire_date > current_date - INTERVAL '2 y, 3 month';
Copy

Filtrar uma coluna de carimbo de data/hora chamada ts em uma tabela chamada tl e adicionar 4 segundos a cada valor retornado:

select ts + INTERVAL '4 seconds' from t1 where ts > to_timestamp('2014-04-05 01:02:03');
Copy

Aritmética simples para datas

Além de usar constantes de intervalo para adicionar e subtrair datas, hora e carimbos de data/hora, o Snowflake também permite adição e subtração básica de dias em relação a valores de DATE, na forma de { + | - } <integer>, em que <integer> especifica o número de dias a adicionar/subtrair.

Nota

Os valores de TIME e TIMESTAMP ainda não têm suporte para aritmética simples.

Exemplos de aritmética de datas

Adicionar 1 dia a uma data específica:

select to_date('2018-04-15') + 1;

+---------------------------+
| TO_DATE('2018-04-15') + 1 |
|---------------------------|
| 2018-04-16                |
+---------------------------+
Copy

Subtrair 4 dias de uma data específica:

select to_date('2018-04-15') - 4;

+---------------------------+
| TO_DATE('2018-04-15') - 4 |
|---------------------------|
| 2018-04-11                |
+---------------------------+
Copy

Consultar uma tabela chamada employees e retornar os nomes das pessoas que deixaram a empresa, mas foram empregadas por mais de 365 dias:

select name from employees where end_date > start_date + 365;
Copy