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 emWEEKDAY
. As datas anteriores ao primeiroWEEKDAY
do ano estão na semana 0. Os valores válidos paraWEEKDAY
sãoSUNDAY
,MONDAY
,TUESDAY
,WEDNESDAY
,THURSDAY
,FRIDAY
eSATURDAY
.ISOWEEK
: retorna o número da semana ISO 8601 (em inglês) dadate_expression
.ISOWEEK
s começam na segunda-feira. Os valores de retorno estão no intervalo [1, 53]. O primeiroISOWEEK
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 quedate_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
- Cria DATE com os valores INT64 que representam o ano, o mês e o dia.
- 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 7DAY
s.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 7DAY
s.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_expression
s.
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 emWEEKDAY
. Os valores válidos deWEEKDAY
sãoSUNDAY
,MONDAY
,TUESDAY
,WEDNESDAY
,THURSDAY
,FRIDAY
eSATURDAY
.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>)
: truncadate_expression
no limite da semana anterior, em que as semanas começam emWEEKDAY
. Os valores válidos deWEEKDAY
sãoSUNDAY
,MONDAY
,TUESDAY
,WEDNESDAY
,THURSDAY
,FRIDAY
eSATURDAY
.ISOWEEK
: truncadate_expression
no limite da semana ISO 8601 anterior.ISOWEEK
s começam na segunda-feira. A primeiraISOWEEK
de cada ano ISO contém a primeira quinta-feira do ano do calendário gregoriano correspondente. Qualquerdate_expression
anterior a esta truncará na segunda-feira anterior.MONTH
QUARTER
YEAR
ISOYEAR
: truncadate_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. |