Funções de data no SQL padrão

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

CURRENT_DATE

CURRENT_DATE([time_zone])

Descrição

Retorna a data atual a partir do fuso horário especificado ou padrão.

Essa função é compatível com o parâmetro opcional time_zone. Esse parâmetro é uma string que representa o fuso horário a ser usado. Se nenhum fuso horário for especificado, será usado o padrão UTC. Para informações sobre como especificar um fuso horário, consulte Definições de fuso horário.

Se o parâmetro time_zone for avaliado como NULL, a função retornará NULL.

Tipo de dados retornados

DATE

Exemplo

SELECT CURRENT_DATE() as the_date;

+--------------+
| the_date     |
+--------------+
| 2016-12-25   |
+--------------+

EXTRACT

EXTRACT(part FROM date_expression)

Descrição

Retorna o valor correspondente à parte da data especificada. O valor de part precisa ser um dos seguintes:

  • DAYOFWEEK: retorna valores no intervalo [1,7] com domingo como primeiro dia da semana.
  • DAY
  • DAYOFYEAR

  • WEEK: retorna o número da semana da data no intervalo [0, 53]. As semanas começam no domingo. Os dias anteriores ao primeiro domingo do ano estão na semana 0.

  • WEEK(<WEEKDAY>): retorna o número da semana da data no intervalo [0, 53]. As semanas começam em WEEKDAY. As datas anteriores ao primeiro WEEKDAY do ano estão na semana 0. Os valores válidos para WEEKDAY são SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY e SATURDAY.

  • ISOWEEK: retorna o número da semana ISO 8601 (em inglês) da date_expression. ISOWEEKs começam na segunda-feira. Os valores de retorno estão no intervalo [1, 53]. O primeiro ISOWEEK de cada ano ISO começa na segunda-feira anterior à primeira quinta-feira do ano do calendário gregoriano.

  • MONTH

  • QUARTER: retorna valores no intervalo [1,4].

  • YEAR

  • ISOYEAR: retorna o ano de numeração da semana ISO 8601, que é o ano do calendário gregoriano que contém a quinta-feira da semana a que date_expression pertence.

Tipo de dados retornados

INT64

Exemplos

No exemplo a seguir, EXTRACT retorna um valor correspondente à parte de hora de DAY.

SELECT EXTRACT(DAY FROM DATE '2013-12-25') as the_day;

+---------+
| the_day |
+---------+
| 25      |
+---------+

No exemplo a seguir, EXTRACT retorna valores correspondentes a diferentes partes da hora de uma coluna de datas próximas ao final do ano.

SELECT
  date,
  EXTRACT(ISOYEAR FROM date) AS isoyear,
  EXTRACT(ISOWEEK FROM date) AS isoweek,
  EXTRACT(YEAR FROM date) AS year,
  EXTRACT(WEEK FROM date) AS week
FROM UNNEST(GENERATE_DATE_ARRAY('2015-12-23', '2016-01-09')) AS date
ORDER BY date;
+------------+---------+---------+------+------+
| date       | isoyear | isoweek | year | week |
+------------+---------+---------+------+------+
| 2015-12-23 | 2015    | 52      | 2015 | 51   |
| 2015-12-24 | 2015    | 52      | 2015 | 51   |
| 2015-12-25 | 2015    | 52      | 2015 | 51   |
| 2015-12-26 | 2015    | 52      | 2015 | 51   |
| 2015-12-27 | 2015    | 52      | 2015 | 52   |
| 2015-12-28 | 2015    | 53      | 2015 | 52   |
| 2015-12-29 | 2015    | 53      | 2015 | 52   |
| 2015-12-30 | 2015    | 53      | 2015 | 52   |
| 2015-12-31 | 2015    | 53      | 2015 | 52   |
| 2016-01-01 | 2015    | 53      | 2016 | 0    |
| 2016-01-02 | 2015    | 53      | 2016 | 0    |
| 2016-01-03 | 2015    | 53      | 2016 | 1    |
| 2016-01-04 | 2016    | 1       | 2016 | 1    |
| 2016-01-05 | 2016    | 1       | 2016 | 1    |
| 2016-01-06 | 2016    | 1       | 2016 | 1    |
| 2016-01-07 | 2016    | 1       | 2016 | 1    |
| 2016-01-08 | 2016    | 1       | 2016 | 1    |
| 2016-01-09 | 2016    | 1       | 2016 | 1    |
+------------+---------+---------+------+------+

No exemplo a seguir, date_expression cai em um domingo. EXTRACT calcula a primeira coluna usando semanas que começam no domingo e calcula a segunda coluna usando semanas que começam na segunda-feira.

WITH table AS (SELECT DATE('2017-11-05') AS date)
SELECT
  date,
  EXTRACT(WEEK(SUNDAY) FROM date) AS week_sunday,
  EXTRACT(WEEK(MONDAY) FROM date) AS week_monday FROM table;

+------------+-------------+-------------+
| date       | week_sunday | week_monday |
+------------+-------------+-------------+
| 2017-11-05 | 45          | 44          |
+------------+-------------+-------------+

DATE

1. DATE(year, month, day)
2. DATE(timestamp_expression[, timezone])

Descrição

  1. Cria DATE com os valores INT64 que representam o ano, o mês e o dia.
  2. Converte timestamp_expression em um tipo de dados DATE. 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

DATE

Exemplo

SELECT
  DATE(2016, 12, 25) as date_ymd,
  DATE(TIMESTAMP "2016-12-25 05:30:00+07", "America/Los_Angeles") as date_tstz;

+------------+------------+
| date_ymd   | date_tstz  |
+------------+------------+
| 2016-12-25 | 2016-12-24 |
+------------+------------+

DATE_ADD

DATE_ADD(date_expression, INTERVAL INT64_expr date_part)

Descrição

Adiciona um intervalo de tempo especificado a DATE.

DATE_ADD aceita os valores a seguir date_part:

  • 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 a data original, o dia resultante será o último dia do mês novo.

Tipo de dados retornados

DATE

Exemplo

SELECT DATE_ADD(DATE "2008-12-25", INTERVAL 5 DAY) as five_days_later;

+--------------------+
| five_days_later    |
+--------------------+
| 2008-12-30         |
+--------------------+

DATE_SUB

DATE_SUB(date_expression, INTERVAL INT64_expr date_part)

Descrição

Subtrai um intervalo de tempo especificado de uma DATE.

DATE_SUB aceita os valores a seguir date_part:

  • 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 a data original, o dia resultante será o último dia do mês novo.

Tipo de dados retornados

DATE

Exemplo

SELECT DATE_SUB(DATE "2008-12-25", INTERVAL 5 DAY) as five_days_ago;

+---------------+
| five_days_ago |
+---------------+
| 2008-12-20    |
+---------------+

DATE_DIFF

DATE_DIFF(date_expression, date_expression, date_part)

Descrição

Retorna o número de limites de date_part entre as duas date_expressions. Se a primeira data ocorrer antes da segunda data, o resultado não será positivo.

DATE_DIFF aceita os valores a seguir date_part:

  • DAY
  • WEEK: essa 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 DATE_DIFF(DATE '2010-07-07', DATE '2008-12-25', DAY) as days_diff;

+-----------+
| days_diff |
+-----------+
| 559       |
+-----------+


SELECT
  DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', DAY) as days_diff,
  DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', WEEK) as weeks_diff;

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

O exemplo acima mostra o resultado de DATE_DIFF para dois dias consecutivos. DATE_DIFF com a parte da data WEEK retorna 1 porque DATE_DIFF conta o número de limites da parte da data nesse intervalo de datas. Cada WEEK começa no domingo, então há um limite da parte da data entre sábado, 2017-10-14 e domingo, 2017-10-15.

No exemplo a seguir, mostramos o resultado de DATE_DIFF para duas datas em anos diferentes. DATE_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 datas. DATE_DIFF com a parte da data ISOYEAR retorna 2 porque a segunda data 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
  DATE_DIFF('2017-12-30', '2014-12-30', YEAR) AS year_diff,
  DATE_DIFF('2017-12-30', '2014-12-30', ISOYEAR) AS isoyear_diff;

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

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

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

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

DATE_TRUNC

DATE_TRUNC(date_expression, date_part)

Descrição

Trunca a data com a granularidade especificada.

DATE_TRUNC aceita os seguintes valores de date_part:

  • DAY
  • WEEK
  • WEEK(<WEEKDAY>): trunca date_expression no limite da semana anterior, em que as semanas começam em WEEKDAY. Os valores válidos de WEEKDAY são SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY e SATURDAY.
  • ISOWEEK: trunca date_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 esta truncará na segunda-feira anterior.
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: trunca date_expression no limite do 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

DATE

Exemplos

SELECT DATE_TRUNC(DATE '2008-12-25', MONTH) as month;

+------------+
| month      |
+------------+
| 2008-12-01 |
+------------+

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

SELECT date AS original, DATE_TRUNC(date, WEEK(MONDAY)) AS truncated
FROM (SELECT DATE('2017-11-05') AS date);

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

No exemplo a seguir, a date_expression original está no ano 2015 do calendário gregoriano. No entanto, DATE_TRUNC com a parte da data ISOYEAR trunca a date_expression com 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 à date_expression 2015-06-15 é 2014-12-29.

SELECT
  DATE_TRUNC('2015-06-15', ISOYEAR) AS isoyear_boundary,
  EXTRACT(ISOYEAR FROM DATE '2015-06-15') AS isoyear_number;

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

DATE_FROM_UNIX_DATE

DATE_FROM_UNIX_DATE(INT64_expression)

Descrição

Interpreta INT64_expression como sendo o número de dias desde 01/01/1970.

Tipo de dados retornados

DATE

Exemplo

SELECT DATE_FROM_UNIX_DATE(14238) as date_from_epoch;

+-----------------+
| date_from_epoch |
+-----------------+
| 2008-12-25      |
+-----------------+

FORMAT_DATE

FORMAT_DATE(format_string, date_expr)

Descrição

Formata date_expr de acordo com a format_string especificada.

Consulte Elementos de formatos compatíveis com DATE para ver uma lista de elementos de formato aceitos por essa função.

Tipo de dados retornados

STRING

Exemplo

SELECT FORMAT_DATE("%x", DATE "2008-12-25") as US_format;

+------------+
| US_format  |
+------------+
| 12/25/08   |
+------------+

PARSE_DATE

PARSE_DATE(format_string, date_string)

Descrição

Usa format_string e uma representação de string de uma data para retornar um objeto DATE.

Ao usar PARSE_DATE, tenha em mente os seguintes itens:

  • Campos não especificados. Qualquer campo não especificado é inicializado em 1970-01-01.
  • Nomes que não diferenciam maiúsculas e minúsculas. Nomes, como Monday, February e outros, 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 de data. Além disso, espaços em branco à esquerda e à direita na string de data 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 (por exemplo, %F e %Y afetam o ano), o último geralmente modifica os anteriores.

Consulte Elementos de formatos compatíveis com DATE para ver uma lista de elementos de formato aceitos por essa função.

Tipo de dados retornados

DATE

Exemplo

SELECT PARSE_DATE("%x", "12/25/08") as parsed;

+------------+
| parsed     |
+------------+
| 2008-12-25 |
+------------+

UNIX_DATE

UNIX_DATE(date_expression)

Descrição

Retorna o número de dias desde 1970-01-01.

Tipo de dados retornados

INT64

Exemplo

SELECT UNIX_DATE(DATE "2008-12-25") as days_from_epoch;

+-----------------+
| days_from_epoch |
+-----------------+
| 14238           |
+-----------------+

Elementos de formatos compatíveis com DATE

Salvo indicação em contrário, as funções DATE 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 como um inteiro) como número decimal (00-99).
%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.
%j O dia do ano como número decimal (001-366).
%m O mês como número decimal (01-12).
%n Um caractere de nova linha.
%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 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.
%E4Y Anos com quatro caracteres (0001 ... 9999). %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…

Esta página
Comentários sobre a documentação
BigQuery
Comentários sobre o produto
Precisa de ajuda? Acesse nossa página de suporte.