Tipos de dados de data e hora¶

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

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

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

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):

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

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