Funções de data

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

CURRENT_DATE

CURRENT_DATE([time_zone])

Descrição

Retorna a data atual a partir do fuso horário especificado ou padrão. Os parênteses são opcionais quando a função é chamada sem argumentos.

Essa função aceita um parâmetro time_zone opcional. 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, essa função retornará NULL.

Tipo de dados retornados

DATE

Exemplo

SELECT CURRENT_DATE() AS the_date;

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

Quando uma coluna chamada current_date está presente, o nome da coluna e a chamada de função sem parênteses são ambíguos. Para garantir a chamada da função, adicione parênteses. Para garantir o nome da coluna, qualifique-o com a variável de intervalo dele. Por exemplo, a consulta a seguir selecionará a função na coluna the_date e a coluna da tabela na coluna current_date.

WITH t AS (SELECT 'column value' AS `current_date`)
SELECT current_date() AS the_date, t.current_date FROM t;

+------------+--------------+
| the_date   | current_date |
+------------+--------------+
| 2016-12-25 | column value |
+------------+--------------+

EXTRACT

EXTRACT(part FROM date_expression)

Descrição

Retorna o valor correspondente à parte da data especificada. O 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 de date_expression. As 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 na faixa [1,4].
  • YEAR
  • ISOYEAR: retorna o ano da numeração da semana ISO 8601, que é o ano do calendário gregoriano que contém a quinta-feira da semana à qual date_expression pertence.

Tipo de dados retornados

INT64

Exemplos

No exemplo a seguir, EXTRACT retorna um valor correspondente à parte da data 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 de data 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])
3. DATE(datetime_expression)

Descrição

  1. Cria uma DATE com os valores INT64 que representam o ano, o mês e o dia.
  2. Extrai a DATE de uma expressão 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.
  3. Extrai a DATE de uma expressão DATETIME.

Tipo de dados retornados

DATE

Exemplo

SELECT
  DATE(2016, 12, 25) AS date_ymd,
  DATE(DATETIME "2016-12-25 23:59:59") AS date_dt,
  DATE(TIMESTAMP "2016-12-25 05:30:00+07", "America/Los_Angeles") AS date_tstz;

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

DATE_ADD

DATE_ADD(date_expression, INTERVAL int64_expression date_part)

Descrição

Adiciona um intervalo de tempo especificado a uma DATE.

DATE_ADD aceita os seguintes valores 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, a data resultante será a última data desse mês.

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_expression date_part)

Descrição

Subtrai um intervalo de tempo especificado de uma DATE.

DATE_SUB aceita os seguintes valores 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, a data resultante será a última data desse mês.

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_a, date_expression_b, date_part)

Descrição

Retorna o número de intervalos date_part inteiros especificados entre dois objetos DATE (date_expression_a - date_expression_b). Se o primeiro DATE for anterior ao segundo, a saída será negativa.

DATE_DIFF aceita os seguintes valores date_part:

  • DAY
  • WEEK: esta parte da data começa no domingo.
  • WEEK(<WEEKDAY>): esta parte da data começa em WEEKDAY. Os valores válidos para 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, mostraremos 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 da data 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 para 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 para 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 vai 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 date_part é WEEK(MONDAY), DATE_TRUNC retorna a DATE da 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 do calendário gregoriano de 2015. No entanto, DATE_TRUNC com a parte da data ISOYEAR trunca a date_expression no início do ano ISO, não no 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 a 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 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

Exemplos

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

+------------+
| US_format  |
+------------+
| 12/25/08   |
+------------+
SELECT FORMAT_DATE("%b-%d-%Y", DATE "2008-12-25") AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec-25-2008 |
+-------------+
SELECT FORMAT_DATE("%b %Y", DATE "2008-12-25") AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec 2008    |
+-------------+

LAST_DAY

LAST_DAY(date_expression[, date_part])

Descrição

Retorna o último dia de uma expressão de data. Ele é normalmente usado para retornar o último dia do mês.

Também é possível especificar a parte da data para a qual o último dia é retornado. Se este parâmetro não for usado, o valor padrão será MONTH. LAST_DAY aceita os seguintes valores para date_part:

  • YEAR
  • QUARTER
  • MONTH
  • WEEK. Equivalente a 7 DAYs.
  • WEEK(<WEEKDAY>). <WEEKDAY> representa o dia da semana. Os valores válidos 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.
  • 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

DATE

Exemplo

Ambos retornam o último dia do mês:

SELECT LAST_DAY(DATE '2008-11-25', MONTH) AS last_day

+------------+
| last_day   |
+------------+
| 2008-11-30 |
+------------+
SELECT LAST_DAY(DATE '2008-11-25') AS last_day

+------------+
| last_day   |
+------------+
| 2008-11-30 |
+------------+

Retorna o último dia do ano:

SELECT LAST_DAY(DATE '2008-11-25', YEAR) AS last_day

+------------+
| last_day   |
+------------+
| 2008-12-31 |
+------------+

Retorna o último dia da semana de uma semana que começa no domingo:

SELECT LAST_DAY(DATE '2008-11-10', WEEK(SUNDAY)) AS last_day

+------------+
| last_day   |
+------------+
| 2008-11-15 |
+------------+

Retorna o último dia da semana de uma semana que começa na segunda-feira:

SELECT LAST_DAY(DATE '2008-11-10', WEEK(MONDAY)) AS last_day

+------------+
| last_day   |
+------------+
| 2008-11-16 |
+------------+

PARSE_DATE

PARSE_DATE(format_string, date_string)

Descrição

Converte uma representação de string de data em um objeto DATE.

format_string contém os elementos de formato que definem como date_string é formatado. Cada elemento em date_string precisa ter um elemento correspondente em format_string. O local de cada elemento em format_string precisa corresponder à localização de cada elemento em date_string.

-- This works because elements on both sides match.
SELECT PARSE_DATE("%A %b %e %Y", "Thursday Dec 25 2008")

-- This doesn't work because the year element is in different locations.
SELECT PARSE_DATE("%Y %A %b %e", "Thursday Dec 25 2008")

-- This doesn't work because one of the year elements is missing.
SELECT PARSE_DATE("%A %b %e", "Thursday Dec 25 2008")

-- This works because %F can find all matching elements in date_string.
SELECT PARSE_DATE("%F", "2000-12-30")

A string de formato é totalmente compatível com a maioria dos elementos de formato, exceto %a, %A, %g, %G, %j, %u, %U, %V, %w e %W.

Ao usar PARSE_DATE, tenha em mente os seguintes itens:

  • Campos não especificados. Qualquer campo não especificado é inicializado em 1970-01-01.
  • Não diferencia maiúsculas de 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 sobrepostas (por exemplo, %F e %Y afetam o ano), o último geralmente substitui os anteriores.

Tipo de dados retornados

DATE

Exemplos

Neste exemplo, uma string formatada em MM/DD/YY é convertida em um objeto DATE:

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

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

Neste exemplo, uma string formatada em YYYYMMDD é convertida em um objeto DATE:

SELECT PARSE_DATE("%Y%m%d", "20081225") 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 Exemplo
%A O nome completo do dia da semana. Quarta-feira
%a O nome abreviado do dia da semana. Qua
%B O nome completo do mês. January
%b ou %h O nome abreviado do mês. Jan
%C O século (um ano dividido por 100 e truncado como um inteiro) como um número decimal (00-99). 20
%D A data no formato %m/%d/%y. 01/20/21
%d O dia do mês como número decimal (01-31). 20
%e O dia do mês como número decimal (1-31). Dígitos únicos são precedidos por um espaço. 20
%F A data no formato %Y-%m-%d. 2021-01-20
%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. 2021
%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. 21
%j O dia do ano como número decimal (001-366). 020
%m O mês como número decimal (01-12). 01
%n Um caractere de nova linha.
%Q O trimestre como um número decimal (de 1–4). 1
%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). 03
%u O dia da semana (segunda-feira como o primeiro dia da semana) como número decimal (1-7). 3
%V O número da semana ISO 8601 do ano (segunda-feira como o primeiro dia da semana) como um 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. 03
%W O número da semana do ano (segunda-feira como o primeiro dia da semana) como número decimal (00-53). 03
%w O dia da semana (domingo como o primeiro dia da semana) como número decimal (0-6). 3
%x A representação de data no formato MM/DD/YY. 01/20/21
%Y O ano com o século como número decimal. 2021
%y O ano sem o século como número decimal (00-99), com um zero opcional à esquerda. Pode ser misturado com %C. Se %C não for especificado, os anos 00-68 são os 2000, enquanto os anos 69-99 são os 1900. 21
%E4Y Anos com quatro caracteres (0001 ... 9999). %Y produz quantos caracteres forem necessários para processar totalmente o ano. 2021