Funciones de fecha y hora en SQL estándar

BigQuery admite las siguientes funciones DATETIME.

Todas las salidas se formatean automáticamente según la norma ISO 8601, separando la fecha y la hora con unT.

CURRENT_DATETIME

CURRENT_DATETIME()

Descripción

Devuelve la hora actual como un objeto DATETIME.

Esta función admite un parámetro opcional timezone.Consulte las definiciones de zona horaria para obtener información sobre cómo especificar una zona horaria.

Devuelve el tipo de datos

DATETIME

Ejemplo

SELECT CURRENT_DATETIME() as now;+----------------------------+| now |+----------------------------+| 2016-05-19T10:38:47.046465 |+----------------------------+

DATETIME

Descripción

1. Construye un objeto DATETIME utilizando valores INT64 que representan el año, el mes, el día, la hora, los minutos y los segundos.
2. Construye un objeto DATETIME utilizando un objeto DATE y un objeto TIME opcional.
3. Construye un objeto DATETIME utilizando un objeto TIMESTAMP. Admite un parámetro opcional para especificar una zona horaria. Si no se especifica una zona horaria, se utiliza la zona horaria por defecto, UTC.

Devuelve el tipo de datos

DATETIME

Ejemplo

EXTRACT

EXTRACT(part FROM datetime_expression)

Descripción

Devuelve un valor que corresponde al partespecificado de un datetime_expression suministrado.

Los valores part permitidos son:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAYOFWEEK
• DAY
• DAYOFYEAR
• WEEK: Devuelve el número de semana de la fecha en el rango . Las semanas comienzan con el domingo, y las fechas anteriores al primer domingo del año están en la semana 0.
• WEEK(<WEEKDAY>): Devuelve el número de semana de datetime_expression en el rango . Las semanas comienzan en WEEKDAY.datetimes anteriores al primer WEEKDAY del año están en la semana 0. Los valores válidos para WEEKDAY son SUNDAY, MONDAY, TUESDAY, WEDNESDAY,THURSDAY, FRIDAY y SATURDAY.
• ISOWEEK: Devuelve el número de semana ISO 8601 del datetime_expression. Los ISOWEEK comienzan en lunes. Los valores devueltos están en el rango . El primer ISOWEEK de cada año ISO comienza el lunes anterior al primer jueves del año del calendario gregoriano.
• MONTH
• QUARTER
• YEAR
• ISOYEAR: Devuelve el año de numeración ISO 8601, que es el año del calendario gregoriano que contiene elJueves de la semana a la que pertenece date_expression.
• DATE
• TIME

Los valores devueltos truncan los períodos de tiempo de orden inferior. Por ejemplo, al extraersegundos, EXTRACT trunca los valores de milisegundos y microsegundos.

Devuelve el tipo de datos

INT64, excepto en los siguientes casos:

• Si part es DATE, devuelve un objeto DATE.
• Si part es TIME, devuelve un objeto TIME.

Ejemplos

En el siguiente ejemplo, EXTRACT devuelve un valor correspondiente a la parte de tiempo HOUR.

SELECT EXTRACT(HOUR FROM DATETIME(2008, 12, 25, 15, 30, 00)) as hour;+------------------+| hour |+------------------+| 15 |+------------------+

En el siguiente ejemplo, EXTRACT devuelve valores correspondientes a diferentes partes de tiempo de una columna de datetimes.

En el siguiente ejemplo, datetime_expression cae en domingo. EXTRACTcalcula la primera columna utilizando las semanas que comienzan en domingo, y calcula la segunda columna utilizando las semanas que comienzan en lunes.

DATETIME_ADD

DATETIME_ADD(datetime_expression, INTERVAL int64_expression part)

Descripción

Agrega int64_expression unidades de part al objeto DATETIME.

DATETIME_ADD admite los siguientes valores para part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK. Equivalente a 7 DAYs.
• MONTH
• QUARTER
• YEAR

Se requiere un manejo especial para las partes de MES, TRIMESTRE y AÑO cuando la fecha está en (o cerca de) el último día del mes. Si el mes resultante tiene menos días que el día de la FECHA original, entonces el día resultante es el último día del nuevo mes.

Tipo de datos de retorno

DATETIME

Ejemplo

DATETIME_SUB

DATETIME_SUB(datetime_expression, INTERVAL int64_expression part)

Descripción

Resta int64_expression unidades de part del DATETIME.

DATETIME_SUB admite los siguientes valores para part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK. Equivalente a 7 DAYs.
• MONTH
• QUARTER
• YEAR

Se requiere un manejo especial para las partes MONTH, QUARTER y YEAR cuando la fecha está en (o cerca de) el último día del mes. Si el mes resultante tiene menos días que el día original de DATETIME, entonces el día resultante es el último día del nuevo mes.

Devuelve el tipo de datos

DATETIME

Ejemplo

DATETIME_DIFF

DATETIME_DIFF(datetime_expression_a, datetime_expression_b, part)

Descripción

Devuelve el número de intervalos enteros especificados partentre dos objetosDATETIME (datetime_expression_a – datetime_expression_b).Si el primer DATETIME es anterior al segundo, la salida es negativa. Lanza un error si el cálculo desborda el tipo de resultado, por ejemplo, si la diferencia de microsegundos entre los dos objetos DATETIME desborda un valorINT64.

DATETIME_DIFF admite los siguientes valores para part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK: Esta parte de la fecha comienza en domingo.
• WEEK(<WEEKDAY>): Esta parte de la fecha comienza en WEEKDAY. Los valores válidos para WEEKDAY son SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY,FRIDAY y SATURDAY.
• ISOWEEK: Utiliza los límites de la semana ISO 8601. Las semanas ISO comienzan en lunes.
• MONTH
• QUARTER
• YEAR
• ISOYEAR: Utiliza el límite del año de numeración de la semana ISO 8601. El límite del año ISO es el lunes de la primera semana cuyo jueves pertenece al año del calendario gregoriano correspondiente.

Tipo de datos de retorno

INT64

Ejemplo

El ejemplo anterior muestra el resultado de DATETIME_DIFF para dos DATETIME que están separados por 24 horas. DATETIME_DIFF con la parte WEEK devuelve 1 porqueDATETIME_DIFF cuenta el número de límites de la parte en este rango deDATETIMEs. Cada WEEK comienza en domingo, así que hay un límite de parte entre el sábado, 2017-10-14 00:00:00 y el domingo, 2017-10-15 00:00:00.

El siguiente ejemplo muestra el resultado de DATETIME_DIFF para dos fechas de años indiferentes. DATETIME_DIFF con la parte de fecha YEAR devuelve 3 porque cuenta el número de límites del año del calendario gregoriano entre los dosDATETIME. DATETIME_DIFF con la parte de fecha ISOYEAR devuelve 2 porque este segundo DATETIME pertenece al año ISO 2015. El primer jueves del año calendario 2015 fue el 2015-01-01, por lo que el año ISO 2015 comienza el lunes anterior, el 2014-12-29.

El siguiente ejemplo muestra el resultado de DATETIME_DIFF para dos días de insucesión. La primera fecha cae en un lunes y la segunda en un domingo. DATETIME_DIFF con la parte de fecha WEEK devuelve 0 porque este timepart utiliza semanas que comienzan en domingo. DATETIME_DIFF con la parte de fechaWEEK(MONDAY) devuelve 1. DATETIME_DIFF con la parte de fechaISOWEEK también devuelve 1 porque las semanas ISO comienzan en lunes.

DATETIME_TRUNC

DATETIME_TRUNC(datetime_expression, part)

Descripción

Trunca un objeto DATETIME a la granularidad de part.

DATETIME_TRUNC admite los siguientes valores para part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• WEEK(<WEEKDAY>): Trunca datetime_expression hasta el límite de la semana anterior, donde las semanas comienzan en WEEKDAY. Los valores válidos para WEEKDAY sonSUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY ySATURDAY.
• ISOWEEK: Trunca datetime_expression al límite de la semana ISO 8601 anterior. ISOWEEKcomienza el lunes. El primer ISOWEEK de cada año ISO contiene el primer jueves del año del calendario gregoriano correspondiente. Cualquier date_expressionanterior a éste se truncará al lunes anterior.
• MONTH
• QUARTER
• YEAR
• ISOYEAR: Trunca datetime_expression al límite del año de numeración de la semana ISO 8601 anterior. El límite del año ISO es el lunes de la primera semana cuyo jueves pertenece al año del calendario gregoriano correspondiente.

Retorno del tipo de datos

DATETIME

Ejemplos

En el siguiente ejemplo, el DATETIME original cae en domingo. Como el part es WEEK(MONDAY), DATE_TRUNC devuelve el DATETIME para el lunes anterior.

En el siguiente ejemplo, el datetime_expression original está en el año del calendario gregoriano 2015. Sin embargo, DATETIME_TRUNC con la fecha ISOYEAR trunca el datetime_expression al principio del año ISO, no del año del calendario gregoriano. El primer jueves del año civil 2015 fue2015-01-01, por lo que el año ISO 2015 comienza el lunes anterior, 2014-12-29.Por lo tanto, el límite del año ISO que precede al datetime_expression2015-06-15 00:00:00 es 2014-12-29.

FORMAT_DATETIME

FORMAT_DATETIME(format_string, datetime_expression)

Descripción

Formatea un objeto DATETIME de acuerdo con el format_string especificado. VerElementos de formato soportados para DATETIMEpara una lista de elementos de formato que soporta esta función.

Devuelve el tipo de datos

STRING

Ejemplos

SELECT FORMAT_DATETIME("%c", DATETIME "2008-12-25 15:30:00") AS formatted;+--------------------------+| formatted |+--------------------------+| Thu Dec 25 15:30:00 2008 |+--------------------------+

SELECT FORMAT_DATETIME("%b-%d-%Y", DATETIME "2008-12-25 15:30:00") AS formatted;+-------------+| formatted |+-------------+| Dec-25-2008 |+-------------+

SELECT FORMAT_DATETIME("%b %Y", DATETIME "2008-12-25 15:30:00") AS formatted;+-------------+| formatted |+-------------+| Dec 2008 |+-------------+

Último día

LAST_DAY(datetime_expression)

Descripción

Devuelve el último día de una expresión datetime que contiene la fecha.Esto se utiliza comúnmente para devolver el último día del mes.

Puede especificar opcionalmente la parte de la fecha para la que se devuelve el último día.Si no se utiliza este parámetro, el valor por defecto es MONTH.LAST_DAY admite los siguientes valores para date_part:

• YEAR
• QUARTER
• MONTH
• WEEK. Equivalente a 7 DAYs.
• WEEK(<WEEKDAY>). <WEEKDAY> representa el día de inicio de la semana.Los valores válidos son SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY,FRIDAY, y SATURDAY.
• ISOWEEK. Utiliza los límites de la semana ISO 8601. Las semanas ISO comienzan el lunes.
• ISOYEAR. El límite del año ISO es el lunes de la primera semana cuyo jueves pertenece al año del calendario gregoriano correspondiente.

Devuelve el tipo de datos

DATE

Ejemplo

Estos dos devuelven el último día del mes:

SELECT LAST_DAY(DATETIME '2008-11-25', MONTH) AS last_day+------------+| last_day |+------------+| 2008-11-30 |+------------+

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

Este devuelve el último día del año:

SELECT LAST_DAY(DATETIME '2008-11-25 15:30:00', YEAR) AS last_day+------------+| last_day |+------------+| 2008-12-31 |+------------+

Este devuelve el último día de la semana para una semana que comienza en domingo:

SELECT LAST_DAY(DATETIME '2008-11-10 15:30:00', WEEK(SUNDAY)) AS last_day+------------+| last_day |+------------+| 2008-11-15 |+------------+

Devuelve el último día de la semana para una semana que comienza en lunes:

SELECT LAST_DAY(DATETIME '2008-11-10 15:30:00', WEEK(MONDAY)) AS last_day+------------+| last_day |+------------+| 2008-11-16 |+------------+

PARSE_DATETIME

PARSE_DATETIME(format_string, datetime_string)

Descripción

Convierte una representación de cadena de un datetime a un objetoDATETIME.

format_stringContiene los elementos de formato que definen cómo se formatea datetime_string. Cada elemento de datetime_string debe tener un elemento correspondiente en format_string. La ubicación de cada elemento en format_string debe coincidir con la ubicación de cada elemento en datetime_string.

La cadena de formato es totalmente compatible con la mayoría de los elementos de formato, excepto con%Q, %a, %A,%g, %G, %j, %P, %u, %U, %V, %w y %W.

PARSE_DATETIME analiza string según las siguientes reglas:

• Campos no especificados. Cualquier campo no especificado se inicializa a partir de1970-01-01 00:00:00.0. Por ejemplo, si el año no está especificado, se inicializa a partir de 1970.
• Nombres sin distinción de mayúsculas y minúsculas. Los nombres, como Monday y February, no distinguen entre mayúsculas y minúsculas.
• Espacios en blanco. Uno o más espacios en blanco consecutivos en la cadena de formato coincide con cero o más espacios en blanco consecutivos en la cadena DATETIME. Los espacios en blanco iniciales y finales en la cadena DATETIME siempre se permiten, incluso si no están en la cadena de formato.
• Precedencia del formato. Cuando dos o más elementos de formato tienen información superpuesta, el último generalmente anula cualquier otro anterior, con algunas excepciones. Por ejemplo, tanto %F como %Y afectan al año, por lo que el elemento anterior prevalece sobre el posterior. Véase la descripción de %s, %C y %y en Elementos de formato admitidos para DATETIME.

Tipo de datos de retorno

DATETIME

Ejemplos

Los siguientes ejemplos analizan un literal STRING como unDATETIME.

SELECT PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '1998-10-18 13:45:55') AS datetime;+---------------------+| datetime |+---------------------+| 1998-10-18T13:45:55 |+---------------------+

SELECT PARSE_DATETIME('%m/%d/%Y %I:%M:%S %p', '8/30/2018 2:23:38 PM') AS datetime+---------------------+| datetime |+---------------------+| 2018-08-30T14:23:38 |+---------------------+

El siguiente ejemplo analiza un literal STRING que contiene una fecha en un formato de lenguaje natural como unDATETIME.

SELECT PARSE_DATETIME('%A, %B %e, %Y','Wednesday, December 19, 2018') AS datetime;+---------------------+| datetime |+---------------------+| 2018-12-19T00:00:00 |+---------------------+

Elementos de formato admitidos para DATETIME

A menos que se indique lo contrario, las funciones DATETIME que utilizan cadenas de formato admiten los siguientes elementos: