Funciones de fecha en SQL estándar

BigQuery admite las siguientes funciones DATE.

CURRENT_DATE

CURRENT_DATE([time_zone])

Descripción

Muestra la fecha actual de la zona horaria especificada o predeterminada. Los paréntesis son opcionales cuando se los llama sin argumentos.

Esta función admite un parámetro opcional time_zone. Este parámetro es una string que representa la zona horaria que se debe usar. Si no se especifica una zona horaria, se usa la predeterminada, UTC. Consulta las Definiciones de la zona horaria para obtener información sobre cómo especificar una zona horaria.

Si el parámetro time_zone se evalúa como NULL, esta función muestra NULL.

Tipo de datos mostrados

DATE

Ejemplo

SELECT CURRENT_DATE() as the_date;

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

Cuando una columna llamada current_date está presente, el nombre de la columna y la llamada a la función sin paréntesis son ambiguos. Para garantizar la llamada a función, agrega paréntesis a fin de garantizar el nombre de la columna y califícala con su variable de rango. Por ejemplo, la siguiente consulta seleccionará la función en la columna the_date y la columna de la tabla en la columna 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)

Descripción

Muestra el valor correspondiente a la parte de fecha especificada. El valor part debe ser uno de los siguientes:

  • DAYOFWEEK: muestra valores en el rango [1,7] con el domingo como primer día de la semana.
  • DAY
  • DAYOFYEAR
  • WEEK: muestra el número de semana de la fecha en el rango [0, 53]. Las semanas comienzan con el domingo y las fechas anteriores al primer domingo del año están en la semana 0.
  • WEEK(<WEEKDAY>): muestra el número de semana de la fecha en el rango [0, 53]. Las semanas comienzan el WEEKDAY. Las fechas 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: muestra el número de semana ISO 8601 de date_expression. Los valores ISOWEEK comienzan el lunes. Los valores de muestra están en el rango [1, 53]. El primer valor ISOWEEK de cada año ISO comienza el lunes anterior al primer jueves del año del calendario gregoriano.
  • MONTH
  • QUARTER: muestra valores en el rango [1,4].
  • YEAR
  • ISOYEAR: muestra el año de numeración de la semana ISO 8601, que es el año calendario gregoriano que contiene el jueves de la semana a la que pertenece date_expression.

Tipo de datos mostrados

INT64

Ejemplos

En el siguiente ejemplo, EXTRACT muestra un valor que corresponde a la parte DAY de la fecha.

SELECT EXTRACT(DAY FROM DATE '2013-12-25') as the_day;

+---------+
| the_day |
+---------+
| 25      |
+---------+

En el siguiente ejemplo, EXTRACT muestra valores que corresponden a diferentes partes de la fecha de una columna de fechas cerca del final del año.

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

En el siguiente ejemplo, date_expression es un domingo. EXTRACT calcula la primera columna mediante las semanas que comienzan los días domingo y calcula la segunda columna con las semanas que comienzan los días lunes.

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)

Descripción

  1. Construye un valor DATE a partir de los valores INT64 que representan el año, mes y día.
  2. Extrae la DATE de una expresión de TIMESTAMP. Admite un parámetro opcional para especificar una zona horaria. Si no se especifica una zona horaria, se usa la predeterminada, UTC.
  3. Extrae el valor de DATE de una expresión DATETIME.

Tipo de datos mostrados

DATE

Ejemplo

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)

Descripción

Agrega un intervalo de tiempo especificado a un valor DATE.

DATE_ADD admite los siguientes valores date_part:

  • DAY
  • WEEK, que equivale a 7 DAY
  • MONTH
  • QUARTER
  • YEAR

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

Tipo de datos mostrados

DATE

Ejemplo

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)

Descripción

Resta un intervalo de tiempo especificado a un valor DATE.

DATE_SUB admite los siguientes valores date_part:

  • DAY
  • WEEK, que equivale a 7 DAY
  • MONTH
  • QUARTER
  • YEAR

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

Tipo de datos mostrados

DATE

Ejemplo

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)

Descripción

Muestra la cantidad de intervalos date_part completos especificados entre dos objetos DATE (date_expression_a y date_expression_b). Si el primer DATE es anterior al segundo, el resultado es negativo.

DATE_DIFF admite los siguientes valores date_part:

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

Tipo de datos mostrados

INT64

Ejemplo

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

En el ejemplo anterior, se muestra el resultado de DATE_DIFF durante dos días seguidos. DATE_DIFF con la parte de fecha WEEK muestra 1 porque DATE_DIFF cuenta el número de límites de parte de fecha en este rango de fechas. Cada WEEK comienza el domingo, por lo que hay un límite de parte de fecha entre el sábado, 2017-10-14, y el domingo, 2017-10-15.

En el siguiente ejemplo, se muestra el resultado de DATE_DIFF para dos fechas en años diferentes. DATE_DIFF con la parte de fecha YEAR muestra 3 porque cuenta el número de límites del año calendario gregoriano entre las dos fechas. DATE_DIFF con la parte de fecha ISOYEAR muestra 2 porque la segunda fecha pertenece al año ISO 2015. El primer jueves del año calendario 2015 fue 2015-01-01, por lo que el año ISO 2015 comienza el lunes 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            |
+-----------+--------------+

En el siguiente ejemplo, se muestra el resultado de DATE_DIFF para dos días seguidos. La primera fecha es un lunes y la segunda es un domingo. DATE_DIFF con la parte WEEK de la fecha muestra 0 porque esta parte de la fecha usa semanas que comienzan el domingo. DATE_DIFF con la parte de fecha WEEK(MONDAY) muestra 1. DATE_DIFF con la parte de fecha ISOWEEK también muestra 1 porque las semanas ISO comienzan el lunes.

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)

Descripción

Trunca la fecha al nivel de detalle especificado.

DATE_TRUNC admite los siguientes valores para date_part:

  • DAY
  • WEEK
  • WEEK(<WEEKDAY>): Trunca date_expression al límite de la semana anterior, en el que las semanas comienzan en WEEKDAY. Los valores válidos para WEEKDAY son SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY y SATURDAY.
  • ISOWEEK: trunca date_expression al límite de la semana ISO 8601 anterior. Los valores ISOWEEK comienzan el lunes. El primer ISOWEEK de cada año ISO contiene el primer jueves del año calendario gregoriano correspondiente. Cualquier valor date_expression anterior a esto se truncará al lunes anterior.
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: trunca date_expression al límite de 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 calendario gregoriano correspondiente.

Tipo de datos mostrados

DATE

Ejemplos

SELECT DATE_TRUNC(DATE '2008-12-25', MONTH) as month;

+------------+
| month      |
+------------+
| 2008-12-01 |
+------------+

En el siguiente ejemplo, la fecha original es un domingo. Debido a que date_part es WEEK(MONDAY), DATE_TRUNC muestra DATE para el lunes 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 |
+------------+------------+

En el siguiente ejemplo, el valor date_expression original está en el año calendario gregoriano 2015. Sin embargo, DATE_TRUNC con la parte de fecha ISOYEAR trunca el valor date_expression al comienzo del año ISO, no el año calendario gregoriano. El primer jueves del año calendario 2015 fue 2015-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 valor date_expression 2015-06-15 es 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)

Descripción

Interpreta int64_expression como el número de días desde 1970-01-01.

Tipo de datos mostrados

DATE

Ejemplo

SELECT DATE_FROM_UNIX_DATE(14238) as date_from_epoch;

+-----------------+
| date_from_epoch |
+-----------------+
| 2008-12-25      |
+-----------------+

FORMAT_DATE

FORMAT_DATE(format_string, date_expr)

Descripción

Da formato a date_expr según el format_string especificado.

Consulta Elementos de formato admitidos para DATE a fin de obtener una lista de los elementos de formato que admite esta función.

Tipo de datos mostrados

STRING

Ejemplos

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])

Descripción

Muestra el último día de una expresión de fecha. Por lo general, se usa para mostrar el último día del mes.

De forma opcional, puedes especificar la parte de fecha para la que se muestra el último día. Si no se usa este parámetro, el valor predeterminado es MONTH. LAST_DAY admite los siguientes valores para date_part:

  • YEAR
  • QUARTER
  • MONTH
  • WEEK, que equivale a 7 DAY.
  • 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. Usa los límites de la semana ISO 8601. Las semanas ISO comienzan el lunes.
  • ISOYEAR. Usa el límite de año de numeración de semanas ISO 8601. El límite del año ISO es el lunes de la primera semana cuyo jueves pertenece al año calendario gregoriano correspondiente.

Tipo de datos mostrados

DATE

Ejemplo

Ambas opciones muestran el último día del mes:

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

Esto muestra el último día del año:

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

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

Esto muestra el último día de la semana de una semana que comienza el domingo:

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

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

Esto muestra el último día de la semana de una semana que comienza el lunes:

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)

Descripción

Convierte una representación de string de fecha en un objeto DATE.

format_string contiene los elementos de formato que definen cómo se da formato a date_string. Cada elemento en date_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 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")

La string de formato admite por completo la mayoría de los elementos de formato, excepto %Q, %a, %A, %g, %G, %j, %u, %U, %V, %w y %W.

Cuando uses PARSE_DATE, ten en cuenta lo siguiente:

  • Campos sin especificar. Todo campo sin especificar se inicializa desde 1970-01-01.
  • Nombres sin distinción entre mayúsculas y minúsculas. Los nombres, como Monday, February, etc., no distinguen entre mayúsculas y minúsculas.
  • Espacio en blanco. Uno o más espacios en blanco consecutivos en la string que coinciden con cero o más espacios en blanco consecutivos en la string de fecha. Además, los espacios en blanco iniciales y finales en la string de fecha siempre están permitidos, incluso si no están en la string de formato.
  • Precedencia del formato. Cuando dos (o más) elementos de formato tienen información superpuesta (por ejemplo, si %F y %Y afectan el año), por lo general, el último anula los anteriores.

Tipo de datos mostrados

DATE

Ejemplos

En este ejemplo, se convierte una string con formato MM/DD/YY en un objeto DATE:

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

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

En este ejemplo, se convierte una string con formato YYYYMMDD en un objeto DATE:

SELECT PARSE_DATE("%Y%m%d", "20081225") as parsed;

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

UNIX_DATE

UNIX_DATE(date_expression)

Descripción

Muestra el número de días desde 1970-01-01.

Tipo de datos mostrados

INT64

Ejemplo

SELECT UNIX_DATE(DATE "2008-12-25") as days_from_epoch;

+-----------------+
| days_from_epoch |
+-----------------+
| 14238           |
+-----------------+

Elementos de formato admitidos para DATE

A menos que se indique lo contrario, las funciones de DATE que usan strings de formato admiten los siguientes elementos:

Elemento de formato Descripción Ejemplo
%A El nombre completo del día de la semana Miércoles
%a El nombre abreviado del día de la semana Mie
%B El nombre completo del mes Enero
%b o %h El nombre del mes abreviado Ene
%C El siglo (un año dividido por 100 y truncado a un número entero) como un número decimal (00-99) 20
%D La fecha en el formato %m/%d/%y 01/20/21
%d El día del mes como número decimal (01-31) 20
%e El día del mes como número decimal (1-31); los dígitos individuales están precedidos por un espacio 20
%F La fecha en el formato %Y-%m-%d 2021-01-20
%G El año ISO 8601 con siglo como número decimal. Cada año ISO comienza el lunes anterior al primer jueves del año calendario gregoriano. Ten en cuenta que %G y %Y pueden producir resultados diferentes cerca de los límites del año gregoriano, en donde el año gregoriano y el año ISO pueden divergir. 2021
%g El año ISO 8601 sin el siglo como número decimal (00-99). Cada año ISO comienza el lunes anterior al primer jueves del año calendario gregoriano. Ten en cuenta que %g y %y pueden producir resultados diferentes cerca de los límites del año gregoriano, en donde el año gregoriano y el año ISO pueden divergir. 21
%j El día del año como número decimal (001-366) 020
%m El mes como número decimal (01-12) 01
%n Un carácter de salto de línea.
%Q El trimestre como un número decimal (1-4). 1
%t Un tabulador
%U El número de la semana del año (con el domingo como primer día de la semana) como número decimal (00-53) 03
%u El día de la semana (lunes como primer día de la semana) como número decimal (1-7) 3
%V El número de semana ISO 8601 del año (el lunes como el primer día de la semana) como un número decimal (01-53). Si la semana que contiene el 1 de enero tiene cuatro o más días en el año nuevo, entonces es la semana 1; de lo contrario, es la semana 53 del año anterior, y la próxima semana es la semana 1. 03
%W El número de la semana del año (con el lunes como primer día de la semana) como número decimal (00-53) 03
%w El día de la semana (domingo como primer día de la semana) como número decimal (0-6) 3
%x La representación de la fecha en formato MM/DD/YY 01/20/21
%Y El año con siglo como número decimal 2021
%y El año sin siglo como número decimal (00-99), con un cero inicial opcional. Se puede combinar con %C. Si no se especifica %C, los años 00-68 son los años 2000, mientras que los años 69-99 son los 1900. 21
%E4Y Los años de cuatro caracteres (0001 … 9999). Ten en cuenta que %Y genera tantos caracteres como sean necesarios para procesar el año completo. 2021