Funções de data e hora no SQL padrão

O BigQuery é compatível com as funções DATETIME a seguir.

CURRENT_DATETIME

CURRENT_DATETIME([timezone])

Descrição

Retorna a hora atual como um objeto DATETIME.

Essa função é compatível com o parâmetro timezone opcional. Para informações sobre como especificar um fuso horário, consulte Definições de fuso horário.

Tipo de dados retornados

DATETIME

Exemplo

SELECT CURRENT_DATETIME() as now;

+----------------------------+
| now                        |
+----------------------------+
| 2016-05-19 10:38:47.046465 |
+----------------------------+

DATETIME

1. DATETIME(year, month, day, hour, minute, second)
2. DATETIME(date_expression, time_expression)
3. DATETIME(timestamp_expression [, timezone])

Descrição

  1. Cria um objeto DATETIME usando valores INT64 que representam o ano, mês, dia, hora, minuto e segundo.
  2. Cria um objeto DATETIME usando um objetos DATE e TIME.
  3. Cria um objeto DATETIME usando um objeto TIMESTAMP. Aceita um parâmetro opcional para especificar um fuso horário. Se nenhum fuso horário for especificado, será usado o padrão UTC.

Tipo de dados retornados

DATETIME

Exemplo

SELECT
  DATETIME(2008, 12, 25, 05, 30, 00) as datetime_ymdhms,
  DATETIME(TIMESTAMP "2008-12-25 05:30:00+00", "America/Los_Angeles") as datetime_tstz;

+---------------------+---------------------+
| datetime_ymdhms     | datetime_tstz       |
+---------------------+---------------------+
| 2008-12-25 05:30:00 | 2008-12-24 21:30:00 |
+---------------------+---------------------+

DATETIME_ADD

DATETIME_ADD(datetime_expression, INTERVAL INT64_expr part)

Descrição

Adiciona unidades INT64_expr de part ao objeto DATETIME.

DATETIME_ADD aceita os seguintes valores de part:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK. Equivalente a 7 DAYs.
  • MONTH
  • QUARTER
  • YEAR

Será necessário um tratamento especial para as partes MONTH, QUARTER e YEAR quando a data for o último dia do mês ou estiver perto dele. Se o mês resultante tiver menos dias do que DATETIME original, então o dia resultante será o último dia do novo mês.

Tipo de dados retornados

DATETIME

Exemplo

SELECT
  DATETIME "2008-12-25 15:30:00" as original_date,
  DATETIME_ADD(DATETIME "2008-12-25 15:30:00", INTERVAL 10 MINUTE) as later;

+-----------------------------+------------------------+
| original_date               | later                  |
+-----------------------------+------------------------+
| 2008-12-25 15:30:00         | 2008-12-25 15:40:00    |
+-----------------------------+------------------------+

DATETIME_SUB

DATETIME_SUB(datetime_expression, INTERVAL INT64_expr part)

Descrição

Subtrai unidades INT64_expr de part do DATETIME.

DATETIME_SUB aceita os seguintes valores de part:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK. Equivalente a 7 DAYs.
  • MONTH
  • QUARTER
  • YEAR

Será necessário um tratamento especial para as partes MONTH, QUARTER e YEAR quando a data for o último dia do mês ou estiver perto dele. Se o mês resultante tiver menos dias do que DATETIME original, então o dia resultante será o último dia do novo mês.

Tipo de dados retornados

DATETIME

Exemplo

SELECT
  DATETIME "2008-12-25 15:30:00" as original_date,
  DATETIME_SUB(DATETIME "2008-12-25 15:30:00", INTERVAL 10 MINUTE) as earlier;

+-----------------------------+------------------------+
| original_date               | earlier                |
+-----------------------------+------------------------+
| 2008-12-25 15:30:00         | 2008-12-25 15:20:00    |
+-----------------------------+------------------------+

DATETIME_DIFF

DATETIME_DIFF(datetime_expression, datetime_expression, part)

Descrição

Retorna o número de limites de part entre as duas datetime_expressions. Se a primeira DATETIME ocorrer antes da segunda DATETIME, o resultado não será positivo. Lança um erro se o cálculo estoura o tipo de resultado, como se a diferença em microssegundos entre os dois objetos DATETIME estourasse um valor INT64.

DATETIME_DIFF aceita os seguintes valores de part:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK: esta parte da data começa no domingo.
  • WEEK(<WEEKDAY>): essa parte da data começa em WEEKDAY. Os valores válidos de WEEKDAY são SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY e SATURDAY.
  • ISOWEEK: usa limites da semana ISO 8601. As semanas ISO começam na segunda-feira.
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: usa o limite de ano de numeração da semana ISO 8601. O limite do ano ISO é a segunda-feira da primeira semana que contém a quinta-feira pertencente ao ano do calendário gregoriano correspondente.

Tipo de dados retornados

INT64

Exemplo

SELECT
  DATETIME "2010-07-07 10:20:00" as first_datetime,
  DATETIME "2008-12-25 15:30:00" as second_datetime,
  DATETIME_DIFF(DATETIME "2010-07-07 10:20:00",
    DATETIME "2008-12-25 15:30:00", DAY) as difference;

+----------------------------+------------------------+------------------------+
| first_datetime             | second_datetime        | difference             |
+----------------------------+------------------------+------------------------+
| 2010-07-07 10:20:00        | 2008-12-25 15:30:00    | 559                    |
+----------------------------+------------------------+------------------------+

SELECT
  DATETIME_DIFF(DATETIME '2017-10-15 00:00:00',
    DATETIME '2017-10-14 00:00:00', DAY) as days_diff,
  DATETIME_DIFF(DATETIME '2017-10-15 00:00:00',
    DATETIME '2017-10-14 00:00:00', WEEK) as weeks_diff;

+-----------+------------+
| days_diff | weeks_diff |
+-----------+------------+
| 1         | 1          |
+-----------+------------+

O exemplo acima mostra o resultado de DATETIME_DIFF para dois DATETIMEs que estão separados por 24 horas. DATETIME_DIFF com a parte WEEK retorna 1 porque DATETIME_DIFF conta o número de limites das partes nesse intervalo de DATETIMEs. Cada WEEK começa no domingo, então há um limite de parte entre sábado, 2017-10-14 00:00:00 e domingo, 2017-10-15 00:00:00.

No exemplo a seguir, mostraremos o resultado de DATETIME_DIFF para duas datas em anos diferentes. DATETIME_DIFF com a parte da data YEAR retorna 3 porque conta o número de limites do ano do calendário gregoriano entre as duas DATETIME. DATETIME_DIFF com a parte da data ISOYEAR retorna 2 porque a segunda DATETIME pertence ao ano ISO 2015. A primeira quinta-feira do ano calendário de 2015 foi 2015-01-01, então o ano ISO 2015 começa na segunda-feira anterior, 2014-12-29.

SELECT
  DATETIME_DIFF('2017-12-30 00:00:00',
    '2014-12-30 00:00:00', YEAR) AS year_diff,
  DATETIME_DIFF('2017-12-30 00:00:00',
    '2014-12-30 00:00:00', ISOYEAR) AS isoyear_diff;

+-----------+--------------+
| year_diff | isoyear_diff |
+-----------+--------------+
| 3         | 2            |
+-----------+--------------+

O exemplo a seguir mostra o resultado de DATETIME_DIFF para dois dias consecutivos. A primeira data cai em uma segunda-feira e a segunda data cai em um domingo. DATETIME_DIFF com a parte da data WEEK retorna 0 porque esta parte do tempo usa semanas que começam no domingo. DATETIME_DIFF com a parte da data WEEK(MONDAY) retorna 1. DATETIME_DIFF com a parte da data ISOWEEK também retorna 1 porque as semanas ISO começam na segunda-feira.

SELECT
  DATETIME_DIFF('2017-12-18', '2017-12-17', WEEK) AS week_diff,
  DATETIME_DIFF('2017-12-18', '2017-12-17', WEEK(MONDAY)) AS week_weekday_diff,
  DATETIME_DIFF('2017-12-18', '2017-12-17', ISOWEEK) AS isoweek_diff;

+-----------+-------------------+--------------+
| week_diff | week_weekday_diff | isoweek_diff |
+-----------+-------------------+--------------+
| 0         | 1                 | 1            |
+-----------+-------------------+--------------+

DATETIME_TRUNC

DATETIME_TRUNC(datetime_expression, part)

Descrição

Trunca um objeto DATETIME para a granularidade de part.

DATETIME_TRUNC aceita os seguintes valores de part:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • WEEK(<WEEKDAY>): trunca datetime_expression para o limite da semana anterior, em que as semanas começam em WEEKDAY. Os valores válidos para WEEKDAY são SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY e SATURDAY.
  • ISOWEEK: trunca datetime_expression no limite da semana ISO 8601 anterior. ISOWEEKs começam na segunda-feira. A primeira ISOWEEK de cada ano ISO contém a primeira quinta-feira do ano do calendário gregoriano correspondente. Qualquer date_expression anterior a será truncada na segunda-feira anterior.
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: trunca datetime_expression no limite de ano de numeração da semana ISO 8601 anterior. O limite do ano ISO é a segunda-feira da primeira semana que contém a quinta-feira pertencente ao ano do calendário gregoriano correspondente.

Tipo de dados retornados

DATETIME

Exemplos

SELECT
  DATETIME "2008-12-25 15:30:00" as original,
  DATETIME_TRUNC(DATETIME "2008-12-25 15:30:00", DAY) as truncated;

+----------------------------+------------------------+
| original                   | truncated              |
+----------------------------+------------------------+
| 2008-12-25 15:30:00        | 2008-12-25 00:00:00    |
+----------------------------+------------------------+

No exemplo a seguir, o DATETIME original cai em um domingo. Como part é WEEK(MONDAY), DATE_TRUNC retorna o DATETIME para a segunda-feira anterior.

SELECT
 datetime AS original,
 DATETIME_TRUNC(datetime, WEEK(MONDAY)) AS truncated
FROM (SELECT DATETIME(TIMESTAMP '2017-11-05 00:00:00') AS datetime);

+---------------------+---------------------+
| original            | truncated           |
+---------------------+---------------------+
| 2017-11-05 00:00:00 | 2017-10-30 00:00:00 |
+---------------------+---------------------+

No exemplo a seguir, a datetime_expression original está no ano 2015 do calendário gregoriano. No entanto, DATETIME_TRUNC com a parte da data ISOYEAR trunca a datetime_expression para o início do ano ISO, não o ano do calendário gregoriano. A primeira quinta-feira do ano calendário de 2015 foi 2015-01-01, então o ano ISO 2015 começa na segunda-feira anterior, 2014-12-29. Portanto, o limite do ano ISO anterior à datetime_expression 2015-06-15 00:00:00 é 2014-12-29.

SELECT
  DATETIME_TRUNC('2015-06-15 00:00:00', ISOYEAR) AS isoyear_boundary,
  EXTRACT(ISOYEAR FROM DATETIME '2015-06-15 00:00:00') AS isoyear_number;

+---------------------+----------------+
| isoyear_boundary    | isoyear_number |
+---------------------+----------------+
| 2014-12-29 00:00:00 | 2015           |
+---------------------+----------------+

FORMAT_DATETIME

FORMAT_DATETIME(format_string, datetime_expression)

Descrição

Formata um objeto DATETIME de acordo com o format_string especificado. Consulte Elementos de formatos compatíveis de DATETIME para ver uma lista de elementos de formato aceitos por essa função.

Tipo de dados retornados

STRING

Exemplo

SELECT
  FORMAT_DATETIME("%c", DATETIME "2008-12-25 15:30:00")
  AS formatted;

PARSE_DATETIME

PARSE_DATETIME(format_string, string)

Descrição

Usa um format_string e uma representação STRING de um DATETIME para retornar um DATETIME. Consulte Elementos de formatos compatíveis com DATETIME para ver uma lista de elementos de formato aceitos por essa função.

PARSE_DATETIME analisa string de acordo com as regras a seguir:

  • Campos não especificados. Qualquer campo não especificado é inicializado em 1970-01-01 00:00:00.0. Por exemplo, se o ano não for especificado, o padrão será 1970.
  • Nomes que não diferenciam maiúsculas e minúsculas. Nomes, como Monday e February, não diferenciam maiúsculas de minúsculas.
  • Espaço em branco. Um ou mais espaços em branco consecutivos na string de formato correspondem a zero ou mais espaços em branco consecutivos na string DATETIME. Espaços em branco à esquerda e à direita na string DATETIME sempre são permitidos, mesmo que não estejam na string de formato.
  • Precedência de formato. Quando dois ou mais elementos de formato têm informações de sobreposição, o último geralmente modifica os anteriores. Por exemplo, %F e %Y afetam o ano, de modo que o elemento anterior substitui o mais recente. Consulte as descrições de %s, %C e %y em Elementos de formatos compatíveis com DATETIME.

Exemplos

O exemplo a seguir analisa um literal STRING como DATETIME.

SELECT PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '1998-10-18 13:45:55') AS datetime;

A consulta acima retorna o resultado a seguir:

+---------------------+
| datetime            |
+---------------------+
| 1998-10-18 13:45:55 |
+---------------------+

O exemplo a seguir analisa um literal STRING que contém uma data em um formato de linguagem natural como DATETIME.

SELECT PARSE_DATETIME('%A, %B %e, %Y','Wednesday, December 19, 2018')
  AS datetime;

A consulta acima retorna o resultado a seguir:

+---------------------+
| datetime            |
+---------------------+
| 2018-12-19 00:00:00 |
+---------------------+

Tipo de dados retornados

DATETIME

Elementos de formatos compatíveis de DATETIME

Salvo indicação em contrário, as funções DATETIME que usam strings de formato aceitam os seguintes elementos:

Elemento de formato Descrição
%A O nome completo do dia da semana.
%a O nome abreviado do dia da semana.
%B O nome completo do mês.
%b ou %h O nome abreviado do mês.
%C O século (um ano dividido por 100 e truncado para um inteiro) como um número decimal (00-99).
%c A representação de data e hora.
%D A data no formato %m/%d/%y.
%d O dia do mês como número decimal (01-31).
%e O dia do mês como número decimal (1-31). Dígitos únicos são precedidos por um espaço.
%F A data no formato %Y-%m-%d.
%G O ano ISO 8601 com o século como número decimal. Cada ano ISO começa na segunda-feira antes da primeira quinta-feira do ano calendário gregoriano. Observe que %G e %Y podem produzir resultados diferentes próximos aos limites do ano gregoriano, em que o ano gregoriano e o ano ISO podem divergir.
%g O ano ISO 8601 sem o século como número decimal (00-99). Cada ano ISO começa na segunda-feira antes da primeira quinta-feira do ano calendário gregoriano. Observe que %g e %y podem produzir resultados diferentes próximos aos limites do ano gregoriano, em que o ano gregoriano e o ano ISO podem divergir.
%H A hora em um relógio de 24 horas como número decimal (00-23).
%I A hora em um relógio de 12 horas como número decimal (01-12).
%j O dia do ano como número decimal (001-366).
%k A hora em um relógio de 24 horas como número decimal (0-23). Dígitos únicos são precedidos por um espaço.
%l A hora em um relógio de 12 horas como número decimal (1-12). Dígitos únicos são precedidos por um espaço.
%M O minuto como número decimal (00-59).
%m O mês como número decimal (01-12).
%n Um caractere de nova linha.
%P am ou pm
%p AM ou PM.
%R A hora no formato %H:%M.
%r A hora em um relógio de 12 horas usando a notação AM/PM.
%S O segundo como número decimal (00-60).
%s O número de segundos desde 1970-01-01 00:00:00. Sempre modifica todos os outros elementos de formato, seja qual for o local onde %s aparece na string. Se vários elementos %s aparecerem, o último terá precedência.
%T A hora no formato %H:%M:%S.
%t Um caractere de tabulação.
%U O número da semana do ano (domingo como o primeiro dia da semana) como número decimal (00-53).
%u O dia da semana (segunda-feira como o primeiro dia da semana) como número decimal (1-7).
%V O número da semana do ano (segunda-feira como o primeiro dia da semana) como número decimal (01-53). Se a semana que tem 1 de janeiro tiver quatro ou mais dias no ano novo, então será a semana 1. Caso contrário, será a semana 53 do ano anterior e a semana seguinte será a semana 1.
%W O número da semana do ano (segunda-feira como o primeiro dia da semana) como número decimal (00-53).
%w O dia da semana (domingo como o primeiro dia da semana) como número decimal (0-6).
%X A representação da hora no formato HH:MM:SS.
%x A representação de data no formato MM/DD/YY.
%Y O ano com o século como número decimal.
%y O ano sem o século como número decimal (00-99), com um zero opcional à esquerda. Pode ser combinado com %C. Se %C não for especificado, os anos 00-68 serão 2000s e os anos 69-99 serão 1900s.
%% Um único caractere %.
%E#S Segundos com # dígitos de precisão fracionária.
%E*S Segundos com precisão fracionária total (um literal '*').
%E4Y Anos com quatro caracteres (0001 ... 9999). Observe que %Y produz quantos caracteres forem necessários para renderizar totalmente o ano.
Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

Precisa de ajuda? Acesse nossa página de suporte.