Funciones de fecha y hora de SQL en 5 dialectos populares de SQL

¿Está confundido por todas las funciones de fecha y hora utilizadas en los diferentes dialectos de SQL? En este artículo, resumo los tipos de datos de fecha y hora utilizados en PostgreSQL, Oracle, SQLite, MySQL y T-SQL. También proporciono ejemplos con las funciones clave de fecha y hora de SQL utilizadas en estos dialectos. ¡Es hora de convertirse en gurús de la fecha y la hora!

¿Quiere calcular la frecuencia con la que los empleados llegan tarde al trabajo? ¿O cuánto tiempo se tarda en completar un pedido? Hay muchos casos en los que los analistas de datos necesitan realizar cálculos sobre valores relacionados con la fecha y la hora en SQL. Sin embargo, los tipos de datos de fecha y hora y las funciones difieren significativamente entre los dialectos de SQL. Las consultas tendrán un aspecto muy diferente dependiendo de si las escribes en, por ejemplo, PostgreSQL o SQL Server.

En este artículo, cubro los aspectos básicos del manejo de fechas y horas en diferentes dialectos de SQL. También proporciono ejemplos de funciones clave de fecha y hora.

Si quieres saltar a una parte determinada, aquí están los dialectos SQL cubiertos en este artículo:

• PostgreSQL
• Oracle
• SQLite
• MySQL
• T-SQL (MS SQL Server)

También puede empezar a aprender los tipos de datos de fecha y hora hoy mismo con el curso Tipos de datos en SQL. Este curso interactivo cubre los tipos de datos que funcionan en todos los sistemas de gestión de bases de datos relacionales populares, incluyendo SQL Server, MySQL, Oracle y PostgreSQL.

Funciones de fecha y hora de SQL en diferentes dialectos

Siempre es más fácil obtener nueva información a través de casos de uso del mundo real. Para entender cómo se pueden procesar las fechas y las horas a través de diferentes dialectos de SQL, sugiero utilizar la siguiente tabla que muestra los viajes previstos para 2022. Aquí tenemos tanto fechas como marcas de tiempo con un desplazamiento de zona horaria.

Ahora veamos cómo podemos tratar estos datos en diferentes dialectos de SQL.

Funciones de fecha y hora de PostgreSQL

Hay cuatro tipos de datos que procesan fechas y horas en PostgreSQL:

• El tipo de datos date se utiliza para almacenar fechas sin la hora exacta (por ejemplo, '2022-21-03').
• El tipo de datos time nos permite almacenar la hora sin una fecha (por ejemplo, '8:34:59'). Por defecto, el tipo de datos de la hora no incluye información sobre la zona horaria. Si necesita especificar la zona horaria, tendrá que use time with time zone. Sin embargo, se recomienda utilizar el siguiente tipo de datos cuando se trate de zonas horarias.
• El tipo de datos timestamp es muy útil en la práctica, ya que permite almacenar la marca de tiempo completa, es decir, las fechas con la hora exacta (por ejemplo, '2022-07-30 06:55:34'). La precisión puede ser de hasta 1 microsegundo. Al igual que con el tipo de datos time, si desea incluir información sobre la zona horaria, deberá utilizar timestamp con zona horaria.
• El tipo de datos interval se utiliza para almacenar información sobre intervalos de tiempo (es decir, la duración). Puede restringir el conjunto de campos almacenados añadiendo la frase correspondiente (por ejemplo, YEAR, DAY, YEAR TO MONTH, HOUR TO SECOND); puede obtener más detalles en la documentación de PostgreSQL. Estos intervalos pueden añadirse o restarse a los tipos de datos definidos anteriormente.

Puede leer más sobre estos y otros tipos de datos de PostgreSQL en este artículo; pasemos a un caso de uso práctico.

Para crear nuestra tabla trips en PostgreSQL, utilizamos el siguiente código:

CREATE TABLE trips(
    id int,
    destination varchar(32),
    departure_date date,
    return_date date,
    departure_timestamp timestamp with time zone,
    arrival_timestamp timestamp with time zone
);

INSERT INTO trips
VALUES (1, 'San Francisco', '2022-03-21', '2022-03-26', '2022-03-21 8:00 -04:00', '2022-03-21 11:14 -07:00');

INSERT INTO trips
VALUES (2, 'London', '2022-06-03', '2022-06-10', '2022-06-03 19:00 -04:00', '2022-06-04 07:10 +01:00');

INSERT INTO trips
VALUES (3, 'Sydney', '2022-07-28', '2022-08-10', '2022-07-28 15:00 -04:00', '2022-07-30 06:55 +10:00');

Observe que utilizamos el tipo de datos date para almacenar información sobre las fechas de salida y retorno. También utilizamos timestamp con zona horaria para almacenar las horas de salida y llegada. Este tipo de datos nos permite almacenar información de fecha, hora y zona horaria; como todos nuestros viajes cruzan varias zonas horarias, esto es importante.

Para practicar las funciones de fecha y hora de PostgreSQL, digamos que queremos averiguar

1. La duración de nuestro viaje en días.
2. La duración de nuestro vuelo desde casa en Nueva York hasta la ciudad de destino.
3. La fecha en la que tenemos que empezar a preparar el viaje, que podemos imaginar que es de unos 14 días.

Queremos esta información para cada viaje en nuestra tabla. He aquí cómo encontrarla utilizando PostgreSQL:

SELECT 
  destination, 
  return_date - departure_date + 1 AS trip_duration, 
  arrival_timestamp - departure_timestamp AS flight_duration,
  departure_date - INTERVAL '14 DAYS' AS prep_date
FROM trips;

Obsérvese que hemos añadido +1 para incluir tanto los días de salida como los de llegada a la duración de nuestro viaje. Aquí están los resultados:

Hay muchas más cosas que se pueden hacer con la fecha y la hora en PostgreSQL. En la siguiente tabla, he resumido cómo hacerlo:

• Obtener la fecha actual y la hora actual.
• Restar fechas/horas.
• Sumar/restar intervalos.
• Extraer ciertas partes de la fecha/hora.
• Crear una fecha/hora a partir de sus partes.
• Truncar fechas.
• Convertir cadenas en objetos de fecha/hora.
• Convertir objetos de fecha/hora en cadenas con un formato específico.

Para más detalles sobre cada una de las funciones anteriores, consulte la documentación de PostgreSQL sobre tipos de fecha/hora, funciones y operadores de fecha/hora y funciones de formato de fecha/hora.

Funciones de fecha y hora de Oracle

Estos son los tipos de datos para procesar fechas y horas en la base de datos Oracle:

• DATE. Este tipo de datos almacena información de fecha y hora, incluyendo siglo, año, mes, fecha, hora, minuto y segundo. Si no se especifica un componente de tiempo, la hora por defecto es la medianoche.
• El tipo de datos TIMESTAMP es una extensión del tipo de datos DATE, ya que también almacena segundos fraccionarios.
  • TIMESTAMP WITH TIME ZONE es una variante del tipo de datos TIMESTAMP. Almacena un desplazamiento de zona horaria o un nombre de región de zona horaria.
  • TIMESTAMP WITH LOCAL TIME ZONE es otra variante de TIMESTAMP. En lugar de almacenar un desplazamiento de zona horaria como parte de los datos de la columna, la información de zona horaria simplemente se normaliza a la zona horaria de la base de datos, es decir, Oracle la devuelve en la zona horaria local de la sesión del usuario.
• El tipo de datos INTERVAL YEAR TO MONTH almacena un período de tiempo utilizando los parámetros YEAR y MONTH
• El tipo de datos INTERVAL DAY TO SECOND almacena un período de tiempo en términos de días, horas, minutos y segundos.

Puedes leer más sobre los tipos de datos datetime de Oracle en la documentación de Oracle; vamos a nuestro caso de uso práctico. Ahora vamos a crear la tabla trips en la base de datos Oracle.

CREATE TABLE trips (
    id int,
    destination varchar(32),
    departure_date date,
    return_date date,
    departure_timestamp timestamp with time zone,
    arrival_timestamp timestamp with time zone
);
   
INSERT INTO trips
VALUES (1, 'San Francisco', DATE'2022-03-21', DATE'2022-03-26', TIMESTAMP'2022-03-21 8:00:00 -04:00', TIMESTAMP'2022-03-21 11:14:00 -07:00');

INSERT INTO trips
VALUES (2, 'London', DATE'2022-06-03', DATE'2022-06-10', TIMESTAMP'2022-06-03 19:00:00 -04:00', TIMESTAMP'2022-06-04 07:10:00 +01:00');

INSERT INTO trips
VALUES (3, 'Sydney', DATE'2022-07-28', DATE'2022-08-10', TIMESTAMP'2022-07-28 15:00:00 -04:00', TIMESTAMP'2022-07-30 06:55:00 +10:00');

Como puede ver, estamos utilizando el tipo de datos DATE para nuestras fechas de salida y regreso. (Aquí no necesitamos la información de la zona horaria ni la precisión hasta los segundos fraccionarios). Estamos utilizando TIME WITH TIME ZONE para nuestras horas de salida y llegada, ya que tenemos datos en diferentes zonas horarias.

Tenga en cuenta que cuando estamos insertando valores, especificamos el tipo de datos para cada valor. También podemos utilizar el formato por defecto de Oracle sin especificar el tipo de datos (por ejemplo, '21-MAR-2022' y '21-MAR-2022 8.00.00 AM -04.00'). Puede cambiar los formatos por defecto utilizando los parámetros de inicialización NLS_DATE_FORMAT, NLS_DATE_LANGUAGE, y NLS_TIMESTAMP_TZ_FORMAT. Puede encontrar más información en la documentación de Oracle.

Para cada destino, de nuevo queremos averiguar la duración del viaje en días (incluyendo los días de salida y llegada), la duración del vuelo a la ciudad de destino, y la fecha en la que tenemos que empezar a preparar el viaje, suponiendo que queremos empezar con 14 días de antelación.

A continuación se explica cómo encontrar esta información en Oracle:

SELECT
  destination, 
  return_date - departure_date + 1 AS trip_duration, 
  arrival_timestamp - departure_timestamp AS flight_duration,
  departure_date - 14 AS prep_date
FROM trips;

Hay muchas más cosas que se pueden hacer con las fechas y horas en Oracle. La siguiente tabla resume cómo hacerlo:

• Obtener la fecha actual y la hora actual.
• Restar fechas y restar horas.
• Sumar/restar intervalos.
• Extraer ciertas partes de la fecha/hora.
• Truncar fechas.
• Convertir cadenas en objetos de fecha/hora.
• Convertir objetos de fecha/hora en cadenas con un formato específico.

Para más detalles sobre cada una de las funciones anteriores - y algunas otras funciones de fecha y hora útiles en Oracle - consulte este artículo y la documentación de Oracle.

Funciones de fecha y hora de SQLite

SQLite no tiene tipos de datos particulares para almacenar fechas y horas. Sin embargo, las funciones de fecha y hora de SQLite pueden ayudarte a almacenar fechas y horas como valores TEXT, REAL, o INTEGER:

• TEXT como cadenas ISO 8601 ('YYYY-MM-DD HH:MM:SS.SSS').
• REAL como el número de días desde el mediodía de Greenwich del 24 de noviembre de 4714 a.C.
• INTEGER como el número de segundos desde el 1970-01-01 00:00:00 UTC.

Aquí están las funciones de fecha y hora de SQLite:

• La función date() devuelve la fecha en el formato AAAA-MM-DD.
• La función time() devuelve la hora en el formato HH:MM:SS.
• La función datetime() devuelve la marca de tiempo en el formato AAAA-MM-DD HH:MM:SS.
• La función julianday() devuelve el día juliano, es decir, el número de días transcurridos desde el mediodía en Greenwich, Inglaterra, del 24 de noviembre de 4714 a.C.
• La función strftime() devuelve la fecha formateada según la cadena de formato especificada como primer argumento.

Para crear nuestra tabla de viajes en SQLite, podemos utilizar el siguiente código:

CREATE TABLE trips (
    id int,
    destination text,
    departure_date text,
    return_date text,
    departure_timestamp text,
    arrival_timestamp text
);

INSERT INTO trips
VALUES (1, 'San Francisco', '2022-03-21', '2022-03-26', '2022-03-21 08:00 -04:00', '2022-03-21 11:14 -07:00');

INSERT INTO trips
VALUES (2, 'London', '2022-06-03', '2022-06-10', '2022-06-03 19:00 -04:00', '2022-06-04 07:10 +01:00');

INSERT INTO trips
VALUES (3, 'Sydney', '2022-07-28', '2022-08-10', '2022-07-28 15:00 -04:00', '2022-07-30 06:55 +10:00');

Observa que utilizamos el tipo de datos TEXT para almacenar todas nuestras fechas y horas. A continuación, insertamos los valores especificando las fechas y horas en el formato habitual, incluyendo incluso las zonas horarias cuando sea necesario.

Ahora queremos practicar las funciones de fecha y hora de SQLite calculando la duración de nuestro viaje en días (incluyendo los días de salida y llegada), la duración del vuelo en horas y la fecha en la que tenemos que empezar a preparar el viaje, que es la habitual de 14 días.

A continuación se explica cómo calcular estos datos en SQLite:

SELECT
  destination,
  julianday(return_date) - julianday(departure_date) + 1 AS trip_duration,
  (julianday(arrival_timestamp) - julianday(departure_timestamp)) * 24 AS flight_duration,
  date(departure_date, '-14 days') AS prep_date
FROM trips;

En esta consulta, utilizamos la función julianday() para calcular la diferencia entre dos fechas/fechas. Esta función devuelve el número de días, que es lo que esperamos para la duración del viaje. La duración del vuelo se presenta mejor en horas, por lo que multiplicamos el resultado por 24 para obtener la duración del vuelo en horas. Observe también la elegancia con la que la función date() nos permite sumar/restar días a un valor de fecha. Del mismo modo, se pueden sumar/restar años, meses, horas, minutos y segundos.

Veamos qué más podemos hacer con fechas y horas en SQLite. En la siguiente tabla, he resumido cómo hacerlo:

• Obtener la fecha actual y la hora actual.
• Restar fechas.
• Restar horas.
• Sumar/restar intervalos.
• Extraer ciertas partes de la fecha/hora.
• Truncar fechas.
• Convertir objetos de fecha/hora en cadenas con un formato específico.

A diferencia de las tablas que tenemos para otros dialectos de SQL, aquí las columnas no corresponden a tipos de datos específicos. Simplemente incluyen ejemplos para procesar (1) fechas, (2) horas, y fechas y horas juntas (es decir, marcas de tiempo).

Para más detalles sobre las funciones de fecha y hora de SQLite, consulte la documentación de SQLite.

Funciones de fecha y hora de MySQL

Hay cinco tipos de datos para procesar fechas y horas en MySQL:

• El tipo de datos DATE se utiliza para valores con una parte de fecha pero sin parte de tiempo.
• El tipo de datos DATETIME se utiliza para valores que contienen partes de fecha y hora. También puede tener segundos fraccionarios al insertar los valores de DATETIME en la tabla. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
• El tipo de datos TIMESTAMP también se utiliza para valores que contienen partes de fecha y hora. También soporta segundos fraccionarios. Además, a partir de MySQL 8.0.19, este tipo de datos proporciona soporte completo para la información de la zona horaria. El rango soportado es de '1970-01-01 00:00:01' UTC a '2038-01-19 03:14:07' UTC.
• El tipo de datos TIME se utiliza para representar una hora del día y también el tiempo transcurrido o un intervalo de tiempo entre dos eventos. Los valores de TIME pueden ir de '-838:59:59' a '838:59:59'. Se admiten los segundos fraccionados.
• El tipo de datos YEAR se utiliza para representar los valores del año. El rango admitido es de 1901 a 2155. Obsérvese también que YEAR acepta valores en diversos formatos, por ejemplo '2021', 2021, '21', 21.

Puede leer más sobre los tipos de datos de fecha y hora en MySQL aquí.

Ahora vamos a repetir nuestro ejemplo con los viajes, pero esta vez en MySQL. Comenzamos creando la tabla trips tabla:

CREATE TABLE trips (
    id int,
    destination varchar(32),
    departure_date date,
    return_date date,
    departure_timestamp timestamp,
    arrival_timestamp timestamp
);

INSERT INTO trips
VALUES (1, 'San Francisco', '2022-03-21', '2022-03-26', '2022-03-21 08:00:00-04:00', '2022-03-21 11:14:00-07:00');

INSERT INTO trips
VALUES (2, 'London', '2022-06-03', '2022-06-10', '2022-06-03 19:00:00-04:00', '2022-06-04 07:10:00+01:00');

INSERT INTO trips
VALUES (3, 'Sydney', '2022-07-28', '2022-08-10', '2022-07-28 15:00:00-04:00', '2022-07-30 06:55:00+10:00');

Como puede ver, utilizamos el tipo de datos DATE para almacenar las fechas de salida y de llegada. Para las horas de salida y llegada, hemos elegido el tipo de datos TIMESTAMP, ya que queremos conservar la información sobre la zona horaria.

Para calcular la duración del viaje (incluyendo los días de salida y llegada), la duración del vuelo y la fecha de inicio de los preparativos (con 14 días de antelación), podemos utilizar la siguiente consulta MySQL:

SELECT
    destination, 
    DATEDIFF(return_date, departure_date) + 1 AS trip_duration, 
    TIMEDIFF(arrival_timestamp, departure_timestamp) AS flight_duration,
    DATE_SUB(departure_timestamp, INTERVAL 14 DAY) AS prep_date
FROM trips;

Como puede ver en esta consulta, hay algunas funciones de MySQL muy útiles que procesan fechas y horas. De hecho, hay muchas funciones de este tipo. Para tener una idea de lo que se puede hacer con las fechas y horas en MySQL, revise la siguiente tabla y aprenda cómo:

• Obtener la fecha actual y la hora actual.
• Restar fechas y horas.
• Sumar/restar intervalos.
• Extraer ciertas partes de la fecha/hora.
• Crear una fecha/hora a partir de sus partes.
• Convertir cadenas en objetos de fecha/hora.
• Cambiar el formato de los objetos de fecha/hora.

Para más detalles sobre cada una de las funciones anteriores, consulte la documentación de MySQL sobre las funciones de fecha y hora.

Funciones T-SQL de fecha y hora

Finalmente, vamos a cubrir las funciones de fecha y hora que debe conocer si trabaja en MS SQL Server y utiliza Transact-SQL (T-SQL).

En primer lugar, aquí están los tipos de datos de fecha y hora soportados por T-SQL:

• El tipo de datos time se utiliza para almacenar valores de tiempo, incluidos los segundos fraccionarios.
• El tipo de datos date se utiliza para almacenar valores de fecha sin partes de tiempo.
• El tipo de datos smalldatetime se utiliza en T-SQL para almacenar fechas y horas en el rango de 1900-01-01 a 2079-06-06, con una precisión de hasta 1 minuto.
• El tipo de datos datetime puede almacenar fechas y horas en el rango de 1753-01-01 a 9999-12-31, con una precisión de hasta 0,00333 segundos.
• T-SQL también tiene el tipo de datos datetime2 tipo de datos. Almacena valores en un rango aún más amplio (0001-01-01 00:00:00.0000000 hasta 9999-12-31 23:59:59.9999999) y define segundos fraccionarios hasta 100 nanosegundos.
• Por último, el tipo de datos datetimeoffset almacena fechas y horas con el desplazamiento de la zona horaria. Tiene el mismo rango y precisión que el tipo de datos datetime2.

Puedes leer más sobre los tipos de datos de fecha y hora en Transact-SQL aquí.

Y ahora es el momento de repetir nuestro ejemplo de viajes con T-SQL. Como siempre, empezamos creando la tabla trips tabla:

CREATE TABLE trips (
    id int,
    destination varchar(32),
    departure_date date,
    return_date date,
    departure_timestamp datetimeoffset,
    arrival_timestamp datetimeoffset
);

INSERT INTO trips
VALUES (1, 'San Francisco', '2022-03-21', '2022-03-26', '2022-03-21 08:00 -04:00', '2022-03-21 11:14 -07:00');

INSERT INTO trips
VALUES (2, 'London', '2022-06-03', '2022-06-10', '2022-06-03 19:00 -04:00', '2022-06-04 07:10 +01:00');

INSERT INTO trips
VALUES (3, 'Sydney', '2022-07-28', '2022-08-10', '2022-07-28 15:00 -04:00', '2022-07-30 06:55 +10:00');

Observe que utilizamos (1) el tipo de datos de fecha para almacenar las fechas de salida y de regreso, y (2) el tipo de datos datetimeoffset para almacenar las horas de salida y de llegada (para conservar la información de la zona horaria).

Ahora vamos a calcular la duración del viaje (incluyendo los días de salida y llegada), la duración del vuelo y la fecha de inicio de la preparación (con 14 días de antelación) utilizando T-SQL:

SELECT
    destination, 
    DATEDIFF(day, departure_date, return_date) + 1 AS trip_duration, 
    DATEDIFF(hour, departure_timestamp, arrival_timestamp) AS flight_duration,
    DATEADD(day, -14, departure_timestamp) AS prep_date
FROM trips;

Como puede ver en este ejemplo, T-SQL tiene la función DATEDIFF() que procesa no sólo las fechas sino también los valores de tiempo y fecha. Sin embargo, al calcular la duración del vuelo, sólo pudimos obtener el número entero de horas en lugar del intervalo exacto con minutos. Si necesita información más precisa, siempre puede seleccionar otra parte de la fecha para esta función (por ejemplo, minuto para obtener la duración del vuelo en minutos). Lea este artículo para ver cómo puede procesar la salida de esta función para obtener el intervalo en el formato requerido.

La siguiente tabla resume algunas otras operaciones que puede realizar con fechas y horas en T-SQL:

• Obtener la fecha actual y la hora actual.
• Restar fechas y horas.
• Sumar/restar intervalos.
• Extraerciertas partes de la fecha /hora (por ejemplo, mes, minuto).
• Crear fecha/hora a partir de sus partes.
• Convertir cadenas en objetos de fecha/hora.
• Cambiar los formatos de fecha y hora.

Como se puede ver en este ejemplo, T-SQL tiene la función DATEDIFF() que procesa no sólo fechas sino también valores de fecha y hora. Sin embargo, al calcular la duración del vuelo, sólo pudimos obtener el número entero de horas en lugar del intervalo exacto con minutos. Si necesita información más precisa, siempre puede seleccionar otra parte de la fecha para esta función (por ejemplo, minuto para obtener la duración del vuelo en minutos). Lea este artículo para ver cómo puede procesar la salida de esta función para obtener el intervalo en el formato requerido.

La siguiente tabla resume algunas otras operaciones que puede realizar con fechas y horas en T-SQL:

• Obtener la fecha actual y la hora actual.
• Restar fechas y horas.
• Sumar/restar intervalos.
• Extraerciertas partes de la fecha /hora (por ejemplo, mes, minuto).
• Crear fecha/hora a partir de sus partes.
• Convertir cadenas en objetos de fecha/hora.
• Cambiar los formatos de fecha y hora.

Para más detalles sobre las funciones de fecha y hora de SQL Server, consulte la documentación de T-SQL.

¡Practiquemos las Funciones de Fecha y Hora de SQL!

Espero que este artículo le haya proporcionado una comprensión general de cómo se pueden procesar las fechas y las horas en diferentes dialectos de SQL. ¡Ahora es tiempo de practicar!

Recomiendo comenzar con un curso interactivo. Podrás obtener un conocimiento exhaustivo sobre las funciones de datos y tiempo de SQL, practicar estas funciones con ejemplos del mundo real y obtener orientación cuando estés atascado:

• Funciones estándar de SQL incluye 211 retos de codificación. Practicarás las funciones SQL estándar utilizadas en el procesamiento de datos de texto, datos numéricos, fechas y horas, y mucho más.
• Tipos de datos en SQL incluye 89 ejercicios interactivos. Le presenta los tipos de datos más comunes en SQL Server, MySQL, Oracle y PostgreSQL.

Si quieres obtener un conocimiento completo de SQL partiendo de cero experiencia en programación, te recomiendo el Curso completo de SQL pista de aprendizaje. Incluye 7 cursos interactivos que cubren la escritura de consultas sencillas, la combinación de datos de varias tablas, la escritura de consultas SQL complejas con subconsultas y el uso de expresiones de tabla comunes, funciones de ventana y mucho más.

Gracias por leer, y feliz aprendizaje.