Funciones de fecha y hora en SQL estándar

BigQuery admite las siguientes funciones DATETIME.

CURRENT_DATETIME

CURRENT_DATETIME([timezone])

Descripción

Muestra la hora actual como un objeto DATETIME.

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

Tipo de datos mostrados

DATETIME

Ejemplo

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

Descripción

  1. Construye un objeto DATETIME con valores INT64 que representan el año, mes, día, hora, minuto y segundo.
  2. Construye un objeto DATETIME con un objeto DATE y uno TIME.
  3. Construye un objeto DATETIME con un objeto TIMESTAMP. Admite un parámetro opcional para especificar una zona horaria. Si no se especifica una zona horaria, se usa la predeterminada, UTC.

Tipo de datos mostrados

DATETIME

Ejemplo

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)

Descripción

Agrega INT64_expr unidades de part al objeto DATETIME.

DATETIME_ADD admite los siguientes valores para part:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • 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 día de DATETIME original, entonces el día del resultado será el último día del nuevo mes.

Tipo de datos mostrados

DATETIME

Ejemplo

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)

Descripción

Resta INT64_expr unidades de part al DATETIME.

DATETIME_SUB admite los siguientes valores para part:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • 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 día de DATETIME original, entonces el día del resultado será el último día del nuevo mes.

Tipo de datos mostrados

DATETIME

Ejemplo

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)

Descripción

Muestra el número de límites de part entre las dos datetime_expression. Si el primer DATETIME ocurre antes del segundo DATETIME, el resultado no es positivo. Genera un error si el cálculo desborda el tipo de resultado, por ejemplo, si la diferencia en microsegundos entre los dos objetos DATETIME desbordaría un valor INT64.

DATETIME_DIFF admite los siguientes valores para part:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK: esta parte de la fecha comienza el día 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
  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          |
+-----------+------------+

En el ejemplo anterior, se muestra el resultado de DATETIME_DIFF para dos DATETIME con 24 horas de diferencia. DATETIME_DIFF con la parte WEEK muestra 1 porque DATETIME_DIFF cuenta el número de límites de parte en este rango de DATETIME. Cada WEEK comienza el domingo, por lo 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.

En el siguiente ejemplo, se muestra el resultado de DATETIME_DIFF para dos fechas en años diferentes. DATETIME_DIFF con la parte de fecha YEAR muestra 3 porque cuenta el número de límites de año del calendario gregoriano entre los dos DATETIME. DATETIME_DIFF con la parte de fecha ISOYEAR muestra 2 porque el segundo DATETIME pertenece al año 2015 según la ISO. 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
  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            |
+-----------+--------------+

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

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)

Descripción

Trunca un objeto DATETIME al nivel de detalle de part.

DATETIME_TRUNC admite los siguientes valores para part:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • WEEK(<WEEKDAY>): Trunca datetime_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 datetime_expression al límite de la semana ISO 8601 anterior. Los valores 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 datetime_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

DATETIME

Ejemplos

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

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

En el siguiente ejemplo, el valor datetime_expression original está en el año calendario gregoriano 2015. Sin embargo, DATETIME_TRUNC con la parte de fecha ISOYEAR trunca el valor datetime_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 datetime_expression 2015-06-15 00:00:00 es 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)

Descripción

Formatea un objeto DATETIME según el valor format_string especificado. Consulta los elementos de formato admitidos para DATETIME a fin de obtener una lista de los elementos de formato que admite esta función.

Tipo de datos mostrados

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

PARSE_DATETIME

PARSE_DATETIME(format_string, string)

Descripción

Usa una format_string y una representación de STRING de una DATETIME para mostrar una DATETIME. Consulta Elementos de formato admitidos para DATETIME a fin de obtener una lista de elementos de formato que admitan esta función.

PARSE_DATETIME analiza la string según las siguientes reglas:

  • Campos sin especificar. Todo campo sin especificar se inicializa desde 1970-01-01 00:00:00.0. Por ejemplo, si el año no se especifica, su valor predeterminado es 1970.
  • Nombres sin distinción entre mayúsculas y minúsculas. Los nombres, como Monday y February, no distinguen entre mayúsculas y minúsculas.
  • Espacio en blanco. Uno o más espacios en blanco consecutivos en la string de formato coinciden con cero o más espacios en blanco consecutivos en la string DATETIME. Los espacios en blanco iniciales y finales siempre se permiten en la string DATETIME, 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 lo general, el último anula los anteriores, con algunas excepciones. Por ejemplo, %F y %Y afectan el año, por lo que el elemento anterior anula al posterior. Consulta las descripciones de %s, %C y %y en los elementos de formato admitidos para DATETIME.

Ejemplos

En el siguiente ejemplo, se analiza un literal de STRING como DATETIME.

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

La consulta anterior muestra el siguiente resultado:

+---------------------+
| datetime            |
+---------------------+
| 1998-10-18 13:45:55 |
+---------------------+

En el ejemplo siguiente, se analiza un literal de STRING que contiene una fecha en un formato de lenguaje natural como DATETIME.

SELECT PARSE_DATETIME('%A, %B %e, %Y','Wednesday, December 19, 2018')
  AS datetime;

La consulta anterior muestra el siguiente resultado:

+---------------------+
| datetime            |
+---------------------+
| 2018-12-19 00:00:00 |
+---------------------+

Tipo de datos mostrados

DATETIME

Elementos de formato admitidos para DATETIME

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

Elemento de formato Descripción
%A El nombre completo del día de la semana
%a El nombre abreviado del día de la semana
%B El nombre completo del mes
%b o %h El nombre del mes abreviado
%C El siglo (un año dividido por 100 y truncado a un número entero) como un número decimal (00-99).
%c La representación de la fecha y la hora.
%D La fecha en el formato %m/%d/%y
%d El día del mes como número decimal (01-31)
%e El día del mes como número decimal (1-31); los dígitos individuales están precedidos por un espacio
%F La fecha en el formato %Y-%m-%d
%G El año ISO 8601 con el 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.
%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.
%H La hora (formato de 24 horas) como número decimal (00-23).
%I La hora (formato de 12 horas) como número decimal (01-12).
%j El día del año como número decimal (001-366).
%k La hora (formato de 24 horas) como número decimal (0-23); los dígitos individuales están precedidos por un espacio.
%l La hora (formato de 12 horas) como número decimal (1-12); los dígitos individuales están precedidos por un espacio.
%M El minuto como número decimal (00-59).
%m El mes como número decimal (01-12)
%n Un carácter de salto de línea.
%P Ya sea a.m. o p.m.
%p Puede ser A.M. o P.M.
%Q El trimestre como un número decimal (1-4).
%R La hora en el formato %H:%M.
%r La hora en el formato de 12 horas con la notación A.M./P.M.
%S El segundo como número decimal (00-60).
%s El número de segundos desde 1970-01-01 00:00:00. Siempre anula todos los demás elementos de formato, sin importar dónde aparezca %s en la string. Si aparecen varios elementos %s, el último tiene prioridad.
%T La hora en el formato %H:%M:%S.
%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)
%u El día de la semana (lunes como primer día de la semana) como número decimal (1-7)
%V El número de semana ISO 8601 del año (en el que el lunes es 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.
%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)
%w El día de la semana (domingo como primer día de la semana) como número decimal (0-6).
%X La representación de la hora en formato HH:MM:SS.
%x La representación de la fecha en formato MM/DD/YY
%Y El año con siglo como número decimal
%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.
%% Un solo carácter %
%E#S Segundos con # dígitos de precisión fraccional.
%E*S Segundos con precisión fraccional completa (un literal '*').
%E4Y Los años de cuatro caracteres (0001 … 9999). Ten en cuenta que %Y produce tantos caracteres como sea necesario para procesar el año por completo.