Como trabalhar com valores de data e hora¶

Os cálculos de data e hora estão entre os mais utilizados e mais críticos na análise e mineração de dados. Este tópico fornece exemplos práticos de consultas e cálculos comuns de data e hora.

Neste tópico:

Carregamento de datas e carimbos de data/hora¶

Esta seção fornece exemplos para carregar valores de data e carimbo de data/hora e descreve considerações relacionadas a fusos horários ao carregar esses valores.

Carregamento de carimbos de data/hora sem fuso horário anexado¶

No exemplo a seguir, o parâmetro TIMESTAMP_TYPE_MAPPING é definido como TIMESTAMP_LTZ (fuso horário local). O parâmetro TIMEZONE é definido como horário de America/Chicago. Se alguns carimbos de data/hora recebidos não tiverem um fuso horário especificado, Snowflake carregará essas cadeias de caracteres assumindo que os carimbos de data/hora representam a hora local no fuso horário definido para o parâmetro TIMEZONE.

ALTER SESSION SET TIMESTAMP_TYPE_MAPPING = 'TIMESTAMP_LTZ';
ALTER SESSION SET TIMEZONE = 'America/Chicago';

CREATE OR REPLACE TABLE time (ltz TIMESTAMP);
INSERT INTO time VALUES ('2024-05-01 00:00:00.000');

SELECT * FROM time;

Copy

+-------------------------------+
| LTZ                           |
|-------------------------------|
| 2024-05-01 00:00:00.000 -0500 |
+-------------------------------+

Carregamento de carimbos de data/hora com fuso horário anexado¶

No exemplo a seguir, o parâmetro TIMESTAMP_TYPE_MAPPING é definido como TIMESTAMP_LTZ (fuso horário local). O parâmetro TIMEZONE é definido como horário de America/Chicago. Se alguns carimbos de data/hora recebidos tiverem um fuso horário diferente especificado, Snowflake carregará a cadeia de caracteres no horário America/Chicago.

ALTER SESSION SET TIMESTAMP_TYPE_MAPPING = 'TIMESTAMP_LTZ';
ALTER SESSION SET TIMEZONE = 'America/Chicago';

CREATE OR REPLACE TABLE time (ltz TIMESTAMP);
INSERT INTO time VALUES ('2024-04-30 19:00:00.000 -0800');

SELECT * FROM time;

Copy

+-------------------------------+
| LTZ                           |
|-------------------------------|
| 2024-04-30 22:00:00.000 -0500 |
+-------------------------------+

Conversão de carimbos de data/hora em fusos horários alternativos¶

No exemplo a seguir, um conjunto de valores de carimbo de data/hora é armazenado sem dados de fuso horário. Os carimbos de data/hora são carregados na hora UTC e convertidos para outros fusos horários:

ALTER SESSION SET TIMEZONE = 'UTC';
ALTER SESSION SET TIMESTAMP_LTZ_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS TZH:TZM';

CREATE OR REPLACE TABLE utctime (ntz TIMESTAMP_NTZ);
INSERT INTO utctime VALUES ('2024-05-01 00:00:00.000');

Copy

SELECT * FROM utctime;

Copy

+-------------------------+
| NTZ                     |
|-------------------------|
| 2024-05-01 00:00:00.000 |
+-------------------------+

SELECT CONVERT_TIMEZONE('UTC','America/Chicago', ntz)::TIMESTAMP_LTZ AS ChicagoTime
  FROM utctime;

Copy

+---------------------------+
| CHICAGOTIME               |
|---------------------------|
| 2024-04-30 19:00:00 +0000 |
+---------------------------+

SELECT CONVERT_TIMEZONE('UTC','America/Los_Angeles', ntz)::TIMESTAMP_LTZ AS LATime
  FROM utctime;

Copy

+---------------------------+
| LATIME                    |
|---------------------------|
| 2024-04-30 17:00:00 +0000 |
+---------------------------+

Inserção de cadeias de caracteres de datas válidas em colunas de datas em uma tabela¶

Este exemplo insere valores em uma coluna DATE.

CREATE OR REPLACE TABLE my_table(id INTEGER, date1 DATE);
INSERT INTO my_table(id, date1) VALUES (1, TO_DATE('2024.07.23', 'YYYY.MM.DD'));
INSERT INTO my_table(id) VALUES (2);

Copy

SELECT id, date1
  FROM my_table
  ORDER BY id;

Copy

+----+------------+
| ID | DATE1      |
|----+------------|
|  1 | 2024-07-23 |
|  2 | NULL       |
+----+------------+

A função TO_DATE aceita valores TIMESTAMP e cadeias de caracteres no formato TIMESTAMP, mas descarta as informações de horário (horas, minutos e assim por diante).

INSERT INTO my_table(id, date1) VALUES
  (3, TO_DATE('2024.02.20 11:15:00', 'YYYY.MM.DD HH:MI:SS')),
  (4, TO_TIMESTAMP('2024.02.24 04:00:00', 'YYYY.MM.DD HH:MI:SS'));

Copy

SELECT id, date1
  FROM my_table
  WHERE id >= 3;

Copy

+----+------------+
| ID | DATE1      |
|----+------------|
|  3 | 2024-02-20 |
|  4 | 2024-02-24 |
+----+------------+

Se você inserir um DATE que foi definido com apenas uma hora, então a data padrão é 1 de janeiro, 1970.

INSERT INTO my_table(id, date1) VALUES
  (5, TO_DATE('11:20:30', 'hh:mi:ss'));

Copy

SELECT id, date1
  FROM my_table
  WHERE id = 5;

Copy

+----+------------+
| ID | DATE1      |
|----+------------|
|  5 | 1970-01-01 |
+----+------------+

Ao recuperar os valores DATE, é possível formatá-los como valores TIMESTAMP:

SELECT id,
       TO_VARCHAR(date1, 'dd-mon-yyyy hh:mi:ss') AS date1
  FROM my_table
  ORDER BY id;

Copy

+----+----------------------+
| ID | DATE1                |
|----+----------------------|
|  1 | 23-Jul-2024 00:00:00 |
|  2 | NULL                 |
|  3 | 20-Feb-2024 00:00:00 |
|  4 | 24-Feb-2024 00:00:00 |
|  5 | 01-Jan-1970 00:00:00 |
+----+----------------------+

Recuperação de data e hora atuais¶

Obtenha a data atual como um valor DATE:

SELECT CURRENT_DATE();

Copy

Obtenha a data e hora atuais como um valor TIMESTAMP:

SELECT CURRENT_TIMESTAMP();

Copy

Recuperação de datas e dias da semana¶

Obtenha o dia atual da semana como um número usando a função EXTRACT:

SELECT EXTRACT('dayofweek', CURRENT_DATE());

Copy

Nota

A parte dayofweek_iso segue o padrão ISO-8601 de elementos de dados e formatos de intercâmbio. A função retorna o dia da semana como um valor inteiro na faixa 1-7, onde 1 representa a segunda-feira.
Para compatibilidade com alguns outros sistemas, a parte dayofweek segue o padrão UNIX. A função retorna o dia da semana como um valor inteiro na faixa 0-6, onde 0 representa o domingo.

É possível obter o dia atual da semana como uma cadeia de caracteres usando a função TO_VARCHAR ou DECODE.

Execute uma consulta que retorne o nome curto em inglês (por exemplo, “Sun”, “Mon” e assim por diante) para a data atual:

SELECT TO_VARCHAR(CURRENT_DATE(), 'dy');

Copy

Execute uma consulta que retorne os nomes dos dias da semana fornecidos explicitamente para a data atual:

SELECT DECODE(EXTRACT('dayofweek_iso', CURRENT_DATE()),
  1, 'Monday',
  2, 'Tuesday',
  3, 'Wednesday',
  4, 'Thursday',
  5, 'Friday',
  6, 'Saturday',
  7, 'Sunday') AS weekday_name;

Copy

Recuperação de partes de data e hora¶

É possível obter várias partes de data e hora para a data e hora atuais usando a função DATE_PART.

Consulta para o dia atual do mês:

SELECT DATE_PART(day, CURRENT_TIMESTAMP());

Copy

Consulta para o ano atual:

SELECT DATE_PART(year, CURRENT_TIMESTAMP());

Copy

Consulta para o mês atual:

SELECT DATE_PART(month, CURRENT_TIMESTAMP());

Copy

Consulta para a hora atual:

SELECT DATE_PART(hour, CURRENT_TIMESTAMP());

Copy

Consulta para o minuto atual:

SELECT DATE_PART(minute, CURRENT_TIMESTAMP());

Copy

Consulta para o segundo atual:

SELECT DATE_PART(second, CURRENT_TIMESTAMP());

Copy

Você também pode usar a função EXTRACT para obter várias partes de data e hora para a data e hora atuais.

Consulta para o dia atual do mês:

SELECT EXTRACT('day', CURRENT_TIMESTAMP());

Copy

Consulta para o ano atual:

SELECT EXTRACT('year', CURRENT_TIMESTAMP());

Copy

Consulta para o mês atual:

SELECT EXTRACT('month', CURRENT_TIMESTAMP());

Copy

Consulta para a hora atual:

SELECT EXTRACT('hour', CURRENT_TIMESTAMP());

Copy

Consulta para o minuto atual:

SELECT EXTRACT('minute', CURRENT_TIMESTAMP());

Copy

Consulta para o segundo atual:

SELECT EXTRACT('second', CURRENT_TIMESTAMP());

Copy

Esta consulta retorna uma saída tabular com várias partes de data e hora para a data e hora atuais:

SELECT month(CURRENT_TIMESTAMP()) AS month,
       day(CURRENT_TIMESTAMP()) AS day,
       hour(CURRENT_TIMESTAMP()) AS hour,
       minute(CURRENT_TIMESTAMP()) AS minute,
       second(CURRENT_TIMESTAMP()) AS second;

Copy

+-------+-----+------+--------+--------+
| MONTH | DAY | HOUR | MINUTE | SECOND |
|-------+-----+------+--------+--------|
|     8 |  28 |    7 |     59 |     28 |
+-------+-----+------+--------+--------+

Cálculo de datas e horas de calendários comerciais¶

Obtenha o primeiro dia do mês como um valor DATE usando a função DATE_TRUNC. Por exemplo, obtenha o primeiro dia do mês atual:

SELECT DATE_TRUNC('month', CURRENT_DATE());

Copy

Obtenha o último dia do mês atual como um valor DATE usando as funções DATEADD e DATE_TRUNC:

SELECT DATEADD('day',
               -1,
               DATE_TRUNC('month', DATEADD(day, 31, DATE_TRUNC('month',CURRENT_DATE()))));

Copy

Como opção alternativa, o exemplo a seguir usa DATE_TRUNC para recuperar o início do mês atual, adiciona um mês para recuperar o início do mês seguinte e, em seguida, subtrai um dia para determinar o último dia do mês atual.

SELECT DATEADD('day',
               -1,
               DATEADD('month', 1, DATE_TRUNC('month', CURRENT_DATE())));

Copy

Obtenha o último dia do mês anterior como um valor DATE:

SELECT DATEADD(day,
               -1,
               DATE_TRUNC('month', CURRENT_DATE()));

Copy

Obtenha o nome curto em inglês (por exemplo, “Jan”, “Dez” e assim por diante) para o mês atual:

SELECT TO_VARCHAR(CURRENT_DATE(), 'Mon');

Copy

Obtenha o nome do mês atual usando nomes de meses fornecidos explicitamente:

SELECT DECODE(EXTRACT('month', CURRENT_DATE()),
         1, 'January',
         2, 'February',
         3, 'March',
         4, 'April',
         5, 'May',
         6, 'June',
         7, 'July',
         8, 'August',
         9, 'September',
         10, 'October',
         11, 'November',
         12, 'December');

Copy

Obtenha a data para segunda-feira da semana atual:

SELECT DATEADD('day',
               (EXTRACT('dayofweek_iso', CURRENT_DATE()) * -1) + 1,
               CURRENT_DATE());

Copy

Obtenha a data para sexta-feira da semana atual:

SELECT DATEADD('day',
               (5 - EXTRACT('dayofweek_iso', CURRENT_DATE())),
               CURRENT_DATE());

Copy

Obtenha a data para a primeira segunda-feira do mês atual usando a função DATE_PART:

SELECT DATEADD(day,
               MOD( 7 + 1 - DATE_PART('dayofweek_iso', DATE_TRUNC('month', CURRENT_DATE())), 7),
               DATE_TRUNC('month', CURRENT_DATE()));

Copy

Nota

Na consulta acima, o valor 1 em 7 + 1 é convertido em segunda-feira. Para recuperar a data da primeira terça-feira, quarta-feira e assim por diante, substitua 2, 3 e assim por diante, respectivamente, até 7 para Sunday.

Obtenha o primeiro dia do ano atual como um valor DATE:

SELECT DATE_TRUNC('year', CURRENT_DATE());

Copy

Obtenha o último dia do ano atual como um valor DATE:

SELECT DATEADD('day',
               -1,
               DATEADD('year', 1, DATE_TRUNC('year', CURRENT_DATE())));

Copy

Obtenha o último dia do ano anterior como um valor DATE:

SELECT DATEADD('day',
               -1,
               DATE_TRUNC('year',CURRENT_DATE()));

Copy

Obtenha o primeiro dia do trimestre atual como um valor DATE:

SELECT DATE_TRUNC('quarter', CURRENT_DATE());

Copy

Obtenha o último dia do trimestre atual como um valor DATE:

SELECT DATEADD('day',
               -1,
               DATEADD('month', 3, DATE_TRUNC('quarter', CURRENT_DATE())));

Copy

Obtenha a data e o carimbo de data/hora para a meia-noite do dia atual:

SELECT DATE_TRUNC('day', CURRENT_TIMESTAMP());

Copy

+----------------------------------------+
| DATE_TRUNC('DAY', CURRENT_TIMESTAMP()) |
|----------------------------------------|
| Wed, 07 Sep 2016 00:00:00 -0700        |
+----------------------------------------+

Incremento de valores de data e hora¶

Use a função DATEADD para incrementar valores de data e hora.

Adicione dois anos à data atual:

SELECT DATEADD(year, 2, CURRENT_DATE());

Copy

Adicione dois dias à data atual:

SELECT DATEADD(day, 2, CURRENT_DATE());

Copy

Adicione duas horas à data e hora atuais:

SELECT DATEADD(hour, 2, CURRENT_TIMESTAMP());

Copy

Adicione dois minutos à data e hora atuais:

SELECT DATEADD(minute, 2, CURRENT_TIMESTAMP());

Copy

Adicione dois segundos à data e hora atuais:

SELECT DATEADD(second, 2, CURRENT_TIMESTAMP());

Copy

Conversão de cadeias de caracteres válidas em datas, horários ou carimbos de data/hora¶

Na maioria dos casos de uso, o Snowflake manipula corretamente os valores de data e carimbo de data/hora formatados como cadeias de caracteres. Em certos casos, como quando comparações são baseadas em cadeias de cadeias de caracteres ou um resultado depende de um formato de carimbo de data/hora diferente do formato definido nos parâmetros da sessão, recomendamos converter explicitamente os valores para o formato desejado para evitar resultados inesperados.

Por exemplo, sem conversão explícita, a comparação dos valores das cadeias de caracteres produz resultados baseados em cadeias de caracteres:

CREATE OR REPLACE TABLE timestamps(timestamp1 STRING);

INSERT INTO timestamps VALUES
  ('Fri, 05 Apr 2013 00:00:00 -0700'),
  ('Sat, 06 Apr 2013 00:00:00 -0700'),
  ('Sat, 01 Jan 2000 00:00:00 -0800'),
  ('Wed, 01 Jan 2020 00:00:00 -0800');

Copy

A consulta a seguir realiza uma comparação sem conversão explícita:

SELECT * FROM timestamps WHERE timestamp1 < '2014-01-01';

Copy

+------------+
| TIMESTAMP1 |
|------------|
+------------+

A consulta a seguir realiza uma comparação com conversão explícita para DATE:

SELECT * FROM timestamps WHERE timestamp1 < '2014-01-01'::DATE;

Copy

+---------------------------------+
| DATE1                           |
|---------------------------------|
| Fri, 05 Apr 2013 00:00:00 -0700 |
| Sat, 06 Apr 2013 00:00:00 -0700 |
| Sat, 01 Jan 2000 00:00:00 -0800 |
+---------------------------------+

Para obter mais informações sobre funções de conversão, consulte Formatos de data e hora em funções de conversão.

Aplicação de aritmética de data a cadeias de caracteres de data¶

Adicione cinco dias à data expressa em uma cadeia de caracteres:

SELECT DATEADD('day',
               5,
               TO_TIMESTAMP('12-jan-2024 00:00:00','dd-mon-yyyy hh:mi:ss'))
  AS add_five_days;

Copy

+-------------------------+
| ADD_FIVE_DAYS           |
|-------------------------|
| 2024-01-17 00:00:00.000 |
+-------------------------+

É possível calcular a diferença em dias entre a data atual e a data expressa em uma cadeia de caracteres usando a função DATEDIFF.

Calcule a diferença em dias usando a função TO_TIMESTAMP.

SELECT DATEDIFF('day',
                TO_TIMESTAMP ('12-jan-2024 00:00:00','dd-mon-yyyy hh:mi:ss'),
                CURRENT_DATE())
  AS to_timestamp_difference;

Copy

+-------------------------+
| TO_TIMESTAMP_DIFFERENCE |
|-------------------------|
|                     229 |
+-------------------------+

Calcule a diferença em dias usando a função TO_DATE:

SELECT DATEDIFF('day',
                TO_DATE ('12-jan-2024 00:00:00','dd-mon-yyyy hh:mi:ss'),
                CURRENT_DATE())
  AS to_date_difference;

Copy

+--------------------+
| TO_DATE_DIFFERENCE |
|--------------------|
|                229 |
+--------------------+

Adicione um dia a uma data especificada:

SELECT TO_DATE('2024-01-15') + 1 AS date_plus_one;

Copy

+---------------+
| DATE_PLUS_ONE |
|---------------|
| 2024-01-16    |
+---------------+

Subtraia nove dias da data atual (por exemplo, 28 de agosto de 2024):

SELECT CURRENT_DATE() - 9 AS date_minus_nine;

Copy

+-----------------+
| DATE_MINUS_NINE |
|-----------------|
| 2024-08-19      |
+-----------------+

Cálculo de diferenças entre datas ou horas¶

Calcule a diferença entre a data atual e a data em três anos:

SELECT DATEDIFF(year, CURRENT_DATE(),
         DATEADD(year, 3, CURRENT_DATE()));

Copy

Calcule a diferença entre a data atual e a data em três meses:

SELECT DATEDIFF(month, CURRENT_DATE(),
         DATEADD(month, 3, CURRENT_DATE()));

Copy

Calcule a diferença entre a data atual e a data em três dias:

SELECT DATEDIFF(day, CURRENT_DATE(),
         DATEADD(day, 3, CURRENT_DATE()));

Copy

Calcule a diferença entre a hora atual e a hora em três horas:

SELECT DATEDIFF(hour, CURRENT_TIMESTAMP(),
         DATEADD(hour, 3, CURRENT_TIMESTAMP()));

Copy

Calcule a diferença entre a hora atual e a hora em três minutos:

SELECT DATEDIFF(minute, CURRENT_TIMESTAMP(),
         DATEADD(minute, 3, CURRENT_TIMESTAMP()));

Copy

Calcule a diferença entre a hora atual e a hora em três segundos:

SELECT DATEDIFF(second, CURRENT_TIMESTAMP(),
         DATEADD(second, 3, CURRENT_TIMESTAMP()));

Copy

Criação de exibições de calendário anuais¶

CREATE OR REPLACE VIEW calendar_2016 AS
  SELECT n,
         theDate,
         DECODE (EXTRACT('dayofweek',theDate),
           1 , 'Monday',
           2 , 'Tuesday',
           3 , 'Wednesday',
           4 , 'Thursday',
           5 , 'Friday',
           6 , 'Saturday',
           0 , 'Sunday') theDayOfTheWeek,
         DECODE (EXTRACT(month FROM theDate),
           1 , 'January',
           2 , 'February',
           3 , 'March',
           4 , 'April',
           5 , 'May',
           6 , 'June',
           7 , 'July',
           8 , 'August',
           9 , 'september',
           10, 'October',
           11, 'November',
           12, 'December') theMonth,
         EXTRACT(year FROM theDate) theYear
  FROM
    (SELECT ROW_NUMBER() OVER (ORDER BY seq4()) AS n,
            DATEADD(day, ROW_NUMBER() OVER (ORDER BY seq4())-1, TO_DATE('2016-01-01')) AS theDate
      FROM table(generator(rowCount => 365)))
  ORDER BY n ASC;

SELECT * from CALENDAR_2016;

Copy

+-----+------------+-----------------+-----------+---------+
|   N | THEDATE    | THEDAYOFTHEWEEK | THEMONTH  | THEYEAR |
|-----+------------+-----------------+-----------+---------|
|   1 | 2016-01-01 | Friday          | January   |    2016 |
|   2 | 2016-01-02 | Saturday        | January   |    2016 |
  ...
| 364 | 2016-12-29 | Thursday        | December  |    2016 |
| 365 | 2016-12-30 | Friday          | December  |    2016 |
+-----+------------+-----------------+-----------+---------+