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

É necessário um tratamento especial para as partes MONTH, QUARTER e YEAR quando a data é o último dia do mês ou está 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

É necessário um tratamento especial para as partes MONTH, QUARTER e YEAR quando a data é o último dia do mês ou está 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>): esta 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. A quinta-feira dessa mesma semana pertence 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 numa 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 esta truncará para a 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. A quinta-feira dessa mesma semana pertence 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 do calendário gregoriano 2015. 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 format_string e uma representação de string de timestamp para retornar um objeto TIMESTAMP. Consulte Elementos de formatos compatíveis de DATETIME para ver uma lista de elementos de formato aceitos por essa função.

Quando usar PARSE_DATETIME, tenha em mente os seguintes itens:

  • 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 e assim por diante.
  • 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 DATETIME. Além disso, 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 (por exemplo, %F e %Y afetam o ano), o último geralmente modifica os anteriores, com algumas exceções. Consulte as descrições de %s, %C e %y.

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 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.
%g O ano ISO 8601 sem o século como número decimal (00-99).
%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, independentemente de 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). %Y produz quantos caracteres forem necessários para processar 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.