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.

Esta 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.

Tipos 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 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.

Tipos 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 de 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])
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.

Tipos 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_expr 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, o dia resultante será o último dia do mês novo.

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

Tipos 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 seguintes valores 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 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.

Tipos 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 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 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. s 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.

Tipos 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.

Tipos 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.

Tipos 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    |
+-------------+

PARSE_DATE

PARSE_DATE(format_string, date_string)

Descrição

Usa uma format_string e a 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 sobrepostas (por exemplo, %F e %Y afetam o ano), o último geralmente substitui os anteriores.

Tipos 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.

Tipos 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.
%Q O trimestre como um número decimal (1–4).
%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 ISO 8601 (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 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.
%E4Y Anos com quatro caracteres (0001 ... 9999). %Y produz quantos caracteres forem necessários para processar totalmente o ano.