Cómo trabajar con fechas y horas en SQL

Introducción

Cuando trabajas con bases de datos relacionales y Structured Query Language (SQL), puede haber momentos en los que necesites manipular valores que representan fechas o tiempos específicos. Por ejemplo, es posible que necesites calcular las horas totales dedicadas a cierta actividad, o tal vez necesites manipular valores de fecha o tiempo utilizando operadores matemáticos y funciones de agregación para calcular su suma o promedio.

En este tutorial, aprenderás cómo utilizar fechas y tiempos en SQL. Comenzarás realizando cálculos aritméticos y utilizando varias funciones con fechas y tiempos utilizando solo la instrucción SELECT. Luego practicarás ejecutando consultas en datos de muestra, y aprenderás cómo implementar la función CAST para que la salida sea más fácil de leer.

Requisitos previos

Para completar este tutorial, necesitarás:

• A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
• MySQL instalado y asegurado en el servidor. Sigue nuestra guía Cómo Instalar MySQL en Ubuntu 20.04 para configurarlo. Esta guía asume que también has configurado un usuario MySQL no root, como se describe en el Paso 3 de esta guía.

Para practicar el uso de fechas y horas en este tutorial, necesitarás una base de datos y una tabla cargadas con datos de ejemplo. Si no tienes una lista para insertar, puedes leer la siguiente Conexión a MySQL y Configuración de una Base de Datos de Ejemplo sección para aprender cómo crear una base de datos y una tabla. Este tutorial se referirá a esta base de datos y tabla de ejemplo en todo momento.

Conexión a MySQL y Configuración de una Base de Datos de Ejemplo

Si tu base de datos SQL se ejecuta en un servidor remoto, accede por SSH a tu servidor desde tu máquina local:

ssh sammy@your_server_ip

A continuación, abre el prompt de MySQL, reemplazando sammy con la información de tu cuenta de usuario de MySQL:

mysql -u sammy -p

Crea una base de datos llamada datetimeDB:

CREATE DATABASE datetimeDB;

Si la base de datos se creó correctamente, recibirás la siguiente salida:

Output
Query OK, 1 row affected (0.01 sec)

Para seleccionar la base de datos datetimeDB, ejecuta la siguiente instrucción USE:

USE datetimeDB;

Output
Database changed

Después de seleccionar la base de datos, crea una tabla dentro de ella. Para el ejemplo de este tutorial, crearemos una tabla que contenga los resultados de dos corredores en varias carreras que han corrido en el transcurso de un año. Esta tabla tendrá las siguientes siete columnas:

• race_id: muestra valores del tipo de dato int y sirve como la clave primaria de la tabla, lo que significa que cada valor en esta columna funcionará como un identificador único para su fila respectiva.
• runner_name: utiliza el tipo de dato varchar con un máximo de 30 caracteres para los nombres de los dos corredores, Bolt y Felix.
• race_name: contiene los tipos de carreras con el tipo de dato varchar con un máximo de 20 caracteres.
• start_day: utiliza el tipo de dato DATE para hacer un seguimiento de la fecha de una carrera específica por año, mes y día. Este tipo de dato sigue los siguientes parámetros: cuatro dígitos para el año y un máximo de dos dígitos para el mes y el día (AAAA-MM-DD).
• start_time: representa la hora de inicio de la carrera con el tipo de dato TIME por horas, minutos y segundos (HH:MM:SS). Este tipo de dato sigue un formato de reloj de 24 horas, como 15:00 para el equivalente de las 3:00 p. m.
• total_miles: muestra el kilometraje total para cada carrera utilizando el tipo de datos decimal, ya que muchos de los kilómetros totales por carrera no son números enteros. En este caso, decimal especifica una precisión de tres con una escala de uno, lo que significa que cualquier valor en esta columna puede tener tres dígitos, con uno de esos dígitos estando a la derecha del punto decimal.
• end_time: utiliza el tipo de datos TIMESTAMP para rastrear los tiempos de los corredores al final de la carrera. Este tipo de datos combina tanto la fecha como la hora en una cadena, y su formato es una combinación de los formatos de DATE y TIME: (AAAA-MM-DD HH:MM:SS).

Crea la tabla ejecutando el comando CREATE TABLE:

CREATE TABLE race_results (
race_id int, 
runner_name varchar(30),
race_name varchar(20), 
start_day DATE,
start_time TIME, 
total_miles decimal(3, 1),
end_time TIMESTAMP,
PRIMARY KEY (race_id)
); 

A continuación, inserta algunos datos de muestra en la tabla vacía:

INSERT INTO race_results
(race_id, runner_name, race_name, start_day, start_time, total_miles, end_time)
VALUES
(1, 'bolt', '1600_meters', '2022-09-18', '7:00:00', 1.0, '2022-09-18 7:06:30'),
(2, 'bolt', '5K', '2022-10-19', '11:00:00', 3.1, '2022-10-19 11:22:31'),
(3, 'bolt', '10K', '2022-11-20', '10:00:00', 6.2, '2022-11-20 10:38:05'),
(4, 'bolt', 'half_marathon', '2022-12-21', '6:00:00', 13.1, '2022-12-21 07:39:04'),
(5, 'bolt', 'full_marathon', '2023-01-22', '8:00:00', 26.2, '2023-01-22 11:23:10'),
(6, 'felix', '1600_meters', '2022-09-18', '7:00:00', 1.0, '2022-09-18 7:07:15'),
(7, 'felix', '5K', '2022-10-19', '11:00:00', 3.1, '2022-10-19 11:30:50'),
(8, 'felix', '10K', '2022-11-20', '10:00:00', 6.2, '2022-11-20 11:10:17'),
(9, 'felix', 'half_marathon', '2022-12-21', '6:00:00', 13.1, '2022-12-21 08:11:57'),
(10, 'felix', 'full_marathon', '2023-01-22', '8:00:00', 26.2, '2023-01-22 12:02:10');

Output
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

Una vez que hayas insertado los datos, estás listo para comenzar a practicar algo de aritmética y funciones con fechas y horas en SQL.

Uso de Aritmética con Fechas y Horas

En SQL, puedes manipular valores de fecha y hora usando expresiones matemáticas. Todo lo que se requiere es el operador matemático y los valores que deseas calcular.

Como ejemplo, digamos que quieres encontrar una fecha que sea cierto número de días después de otra. La siguiente consulta toma un valor de fecha (2022-10-05) y le suma 17 para devolver el valor de la fecha diecisiete días después de la especificada en la consulta. Ten en cuenta que este ejemplo especifica 2022-10-05 como un valor de FECHA para asegurarse de que el DBMS no lo interprete como una cadena u otro tipo de dato:

SELECT DATE '2022-10-05' + 17 AS new_date;

Output
+----------+
| new_date |
+----------+
| 20221022 |
+----------+
1 row in set (0.01 sec)

Como indica esta salida, 17 días después de 2022-10-05 es 2022-10-22, o el 22 de octubre de 2022.

Como otro ejemplo, digamos que quieres calcular las horas totales entre dos momentos diferentes. Puedes hacer esto restando uno del otro. Para la siguiente consulta, 11:00 es el primer valor de tiempo y 3:00 es el segundo valor de tiempo. Aquí necesitarás especificar que ambos son valores de HORA para devolver la diferencia en horas:

SELECT TIME '11:00' - TIME '3:00' AS time_diff;

Output
+-----------+
| time_diff |
+-----------+
|     80000 |
+-----------+
1 row in set (0.00 sec)

Esta salida te indica que la diferencia entre las 11:00 y las 3:00 es 80000, o 8 horas.

Ahora practica usando aritmética en la información de fecha y hora de los datos de muestra. Para la primera consulta, calcula el tiempo total que tomaron los corredores para terminar cada carrera restando end_time de start_time:

SELECT runner_name, race_name, end_time - start_time 
AS total_time 
FROM race_results;

Output
+-------------+---------------+----------------+
| runner_name | race_name     | total_time     |
+-------------+---------------+----------------+
| bolt        | 1600_meters   | 20220918000630 |
| bolt        | 5K            | 20221019002231 |
| bolt        | 10K           | 20221120003805 |
| bolt        | half_marathon | 20221221013904 |
| bolt        | full_marathon | 20230122032310 |
| felix       | 1600_meters   | 20220918000715 |
| felix       | 5K            | 20221019003050 |
| felix       | 10K           | 20221120011017 |
| felix       | half_marathon | 20221221021157 |
| felix       | full_marathon | 20230122040210 |
+-------------+---------------+----------------+
10 rows in set (0.00 sec)

Notarás que esta salida en la columna total_time es bastante larga y difícil de leer. Más adelante, demostraremos cómo usar la función CAST para convertir estos valores de datos para que sean más claros de leer.

Ahora, si solo estás interesado en el rendimiento de cada corredor para carreras más largas, como medias y maratones completos, puedes consultar tus datos para recuperar esa información. Para esta consulta, resta end_time de start_time y reduce tus resultados utilizando la cláusula WHERE para recuperar datos donde total_miles fueron mayores que 12:

SELECT runner_name, race_name, end_time - start_time AS half_full_results
FROM race_results 
WHERE total_miles > 12;

Output
+-------------+---------------+-------------------+
| runner_name | race_name     | half_full_results |
+-------------+---------------+-------------------+
| bolt        | half_marathon |    20221221013904 |
| bolt        | full_marathon |    20230122032310 |
| felix       | half_marathon |    20221221021157 |
| felix       | full_marathon |    20230122040210 |
+-------------+---------------+-------------------+
4 rows in set (0.00 sec)

En esta sección, realizaste algunas operaciones aritméticas en fechas y horas con la instrucción SELECT y para propósitos prácticos sobre los datos de muestra. A continuación, practicarás consultas utilizando varias funciones de fecha y hora.

Utilizando Funciones de Fecha y Hora y Expresiones de Intervalo

Existen varias funciones que pueden ser utilizadas para encontrar y manipular valores de fecha y hora en SQL. Las funciones de SQL suelen ser utilizadas para procesar o manipular datos, y las funciones disponibles dependen de la implementación de SQL. Sin embargo, la mayoría de las implementaciones de SQL te permiten encontrar la fecha y hora actuales consultando los valores de current_date y current_time.

Para encontrar la fecha de hoy, por ejemplo, la sintaxis es corta y está compuesta únicamente por la instrucción SELECT y la función current_date de la siguiente manera:

SELECT current_date;

Output
+--------------+
| current_date |
+--------------+
| 2022-02-15   |
+--------------+
1 row in set (0.00 sec)

Utilizando la misma sintaxis, puedes encontrar la hora actual con la función current_time:

SELECT current_time;

Output
+--------------+
| current_time |
+--------------+
| 17:10:20     |
+--------------+
1 row in set (0.00 sec)

Si prefieres consultar tanto la fecha como la hora en la salida, usa la función current_timestamp

SELECT current_timestamp;

Output
+---------------------+
| current_timestamp   |
+---------------------+
| 2022-02-15 19:09:58 |
+---------------------+
1 row in set (0.00 sec)

Puedes utilizar funciones de fecha y hora como estas dentro de funciones aritméticas similares a la sección anterior. Por ejemplo, supongamos que quieres saber qué fecha era hace 11 días desde la fecha de hoy. En este caso, podrías usar la misma estructura de sintaxis que utilizaste anteriormente para consultar la función current_date y luego restar 11 para encontrar la fecha de hace once días:

SELECT current_date - 11;

Output
+-------------------+
| current_date - 11 |
+-------------------+
|          20220206 |
+-------------------+
1 row in set (0.01 sec)

Como indica esta salida, hace 11 días desde la current_date (en el momento de escribir esto) fue el 2022-02-06, o 6 de febrero de 2022. Ahora intenta ejecutar esta misma operación, pero reemplaza current_date con la función current_time:

SELECT current_time - 11;

Output
+-------------------+
| current_time - 11 |
+-------------------+
|        	233639 |
+-------------------+
1 row in set (0.00 sec)

Esta salida muestra que cuando restas 11 del valor de current_time, se restan 11 segundos. La operación que realizaste anteriormente usando la función current_date interpretó 11 como días, no segundos. Esta inconsistencia en cómo se interpretan los números al trabajar con funciones de fecha y hora puede ser confusa. En lugar de requerir que manipules valores de fecha y hora usando aritmética de esta manera, muchos sistemas de gestión de bases de datos te permiten ser más explícito a través del uso de expresiones INTERVAL

Las expresiones INTERVAL te permiten encontrar qué fecha o hora sería antes o después de un intervalo establecido desde una expresión de fecha o tiempo dada. Deben tomar la siguiente forma:

INTERVAL value unit

Por ejemplo, para encontrar la fecha de cinco días a partir de ahora, podrías ejecutar la siguiente consulta:

SELECT current_date + INTERVAL '5' DAY AS "5_days_from_today";

Este ejemplo encuentra el valor de current_date, y luego agrega la expresión de intervalo INTERVAL '5' DAY a él. Esto devuelve la fecha 5 días a partir de ahora:

Output
+-------------------+
| 5_days_from_today |
+-------------------+
| 2022-03-06    	|
+-------------------+
1 row in set (0.00 sec)

Esto es mucho menos ambiguo que la siguiente consulta, que produce una salida similar, aunque no idéntica:

SELECT current_date + 5 AS "5_days_from_today";

Output
+-------------------+
| 5_days_from_today |
+-------------------+
|      	20220306 |
+-------------------+
1 row in set (0.00 sec)

Tenga en cuenta que también puede restar intervalos de fechas o horas para encontrar valores de antes de la fecha especificada:

SELECT current_date - INTERVAL '7' MONTH AS "7_months_ago";

Output
+--------------+
| 7_months_ago |
+--------------+
| 2021-08-01   |
+--------------+
1 row in set (0.00 sec)

Las unidades disponibles para usar en expresiones de INTERVAL dependen de su elección de DBMS, aunque la mayoría tendrá opciones como HOUR, MINUTE y SECOND:

SELECT current_time + INTERVAL '6' HOUR AS "6_hours_from_now",
current_time - INTERVAL '5' MINUTE AS "5_minutes_ago",
current_time + INTERVAL '20' SECOND AS "20_seconds_from_now";

Output
+------------------+---------------+---------------------+
| 6_hours_from_now | 5_minutes_ago | 20_seconds_from_now |
+------------------+---------------+---------------------+
| 07:51:43     	| 01:46:43  	| 01:52:03.000000 	|
+------------------+---------------+---------------------+
1 row in set (0.00 sec)

Ahora que ha aprendido sobre expresiones de intervalo y algunas funciones de fecha y hora, continúe practicando con los datos de muestra que insertó en el primer paso.

Usando CAST y funciones de agregación con fecha y hora

Recuerde del tercer ejemplo en la sección Usando Aritmética con Fechas y Horas, cuando ejecutó la siguiente consulta para restar end_time de start_time para calcular las horas totales que cada corredor completó por carrera. Sin embargo, la salida resultó en una columna que contiene una salida muy larga, que sigue el tipo de datos TIMESTAMP que se configuró en la tabla:

SELECT runner_name, race_name, end_time - start_time 
AS total_time 
FROM race_results;

Output
+-------------+---------------+----------------+
| runner_name | race_name     | total_time     |
+-------------+---------------+----------------+
| bolt        | 1600_meters   | 20220918000630 |
| bolt        | 5K            | 20221019002231 |
| bolt        | 10K           | 20221120003805 |
| bolt        | half_marathon | 20221221013904 |
| bolt        | full_marathon | 20230122032310 |
| felix       | 1600_meters   | 20220918000715 |
| felix       | 5K            | 20221019003050 |
| felix       | 10K           | 20221120011017 |
| felix       | half_marathon | 20221221021157 |
| felix       | full_marathon | 20230122040210 |
+-------------+---------------+----------------+
10 rows in set (0.00 sec)

Debido a que estás realizando una operación con dos columnas que tienen diferentes tipos de datos (end_time que contiene valores de tipo TIMESTAMP y start_time que contiene valores de tipo TIME), la base de datos no sabe qué tipo de datos usar al imprimir el resultado de la operación. En su lugar, convierte ambos valores a enteros para poder realizar la operación, lo que resulta en los números largos en la columna total_time.

Para ayudar a que estos datos sean más claros de leer e interpretar, puedes usar la función CAST para convertir estos valores enteros largos al tipo de datos TIME. Para hacerlo, comienza con CAST y luego sigue inmediatamente con un paréntesis de apertura, los valores que deseas convertir, y luego la palabra clave AS y el tipo de datos al que deseas convertirlo.

La siguiente consulta es idéntica al ejemplo anterior, pero utiliza una función CAST para convertir la columna total_time al tipo de datos time:

SELECT runner_name, race_name, CAST(end_time - start_time AS time)
AS total_time 
FROM race_results;

Output
+-------------+---------------+------------+
| runner_name | race_name     | total_time |
+-------------+---------------+------------+
| bolt        | 1600_meters   | 00:06:30   |
| bolt        | 5K            | 00:22:31   |
| bolt        | 10K           | 00:38:05   |
| bolt        | half_marathon | 01:39:04   |
| bolt        | full_marathon | 03:23:10   |
| felix       | 1600_meters   | 00:07:15   |
| felix       | 5K            | 00:30:50   |
| felix       | 10K           | 01:10:17   |
| felix       | half_marathon | 02:11:57   |
| felix       | full_marathon | 04:02:10   |
+-------------+---------------+------------+
10 rows in set (0.00 sec)

CAST convirtió los valores de datos a TIME en esta salida, lo que la hace mucho más fácil de leer y entender.

Ahora, usemos algunas funciones de agregación en combinación con la función CAST para encontrar el tiempo más corto, más largo y total de cada corredor. Primero, consulte el tiempo mínimo (o más corto) con la función de agregación MIN. Nuevamente, querrá usar CAST para convertir los valores de datos TIMESTAMP a valores de datos TIME para mayor claridad. Tenga en cuenta que al usar dos funciones como en este ejemplo, se requieren dos pares de paréntesis y el cálculo de las horas totales (end_time - start_time) debe estar anidado dentro de uno de ellos. Por último, agregue una cláusula GROUP BY para organizar estos valores en función de la columna runner_name para que la salida presente los resultados de carrera de los dos corredores:

SELECT runner_name, MIN(CAST(end_time - start_time AS time)) AS min_time
FROM race_results GROUP BY runner_name;

Output
+-------------+----------+
| runner_name | min_time |
+-------------+----------+
| bolt        | 00:06:30 |
| felix       | 00:07:15 |
+-------------+----------+
2 rows in set (0.00 sec)

Esta salida muestra el tiempo más corto de carrera de cada corredor, en este caso un mínimo de seis minutos y 30 segundos para Bolt, y siete minutos y 15 segundos para Felix.

A continuación, encuentre el tiempo más largo de carrera de cada corredor. Puede usar la misma sintaxis que la consulta anterior, pero esta vez reemplace MIN con MAX:

SELECT runner_name, MAX(CAST(end_time - start_time AS time)) AS max_time
FROM race_results GROUP BY runner_name;

Output
+-------------+----------+
| runner_name | max_time |
+-------------+----------+
| bolt        | 03:23:10 |
| felix       | 04:02:10 |
+-------------+----------+
2 rows in set (0.00 sec)

Esta salida nos dice que el tiempo más largo de carrera de Bolt fue un total de tres horas, 23 minutos y 10 segundos; y Felix fue un total de cuatro horas, dos minutos y 10 segundos.

Ahora vamos a consultar información de alto nivel sobre el total de horas que cada corredor pasó corriendo. Para esta consulta, combine la función de agregación SUM para encontrar la suma total de horas basadas en end_time - start_time, y use CAST para convertir esos valores de datos a TIME. No olvide incluir GROUP BY para organizar los valores para los resultados de ambos corredores:

SELECT runner_name, SUM(CAST(end_time - start_time AS time))
AS total_hours FROM race_results GROUP BY runner_name;

Output
+-------------+-------------+
| runner_name | total_hours |
+-------------+-------------+
| bolt        |       52880 |
| felix       |       76149 |
+-------------+-------------+
2 rows in set (0.00 sec)

Curiosamente, esta salida muestra la interpretación para MySQL, que en realidad está calculando el tiempo total como enteros. Si leemos estos resultados como tiempo, el tiempo total de Bolt se desglosa en cinco horas, 28 minutos y 80 segundos; y el tiempo de Felix se desglosa en siete horas, 61 minutos y 49 segundos. Como puede ver, este desglose del tiempo no tiene sentido, lo que indica que se está calculando como un entero y no como tiempo. Si intentara esto en un DBMS diferente, como PostgreSQL, por ejemplo, la misma consulta se vería ligeramente diferente:

SELECT runner_name, SUM(CAST(end_time - start_time AS time))
AS total_hours FROM race_results GROUP BY runner_name;

Output
 runner_name | total_hours
-------------+-------------
 felix       | 10:01:44
 bolt        | 06:09:20
(2 rows)

En este caso, la consulta en PostgreSQL interpreta los valores como tiempo y los calcula como tal, de modo que los resultados de Felix se desglosan en un total de 10 horas, un minuto y 44 segundos; y los de Bolt como seis horas, nueve minutos y 20 segundos. Este es un ejemplo de cómo diversas implementaciones de DBMS pueden interpretar valores de datos de manera diferente incluso si se utiliza la misma consulta y conjunto de datos.

Conclusión

Entender cómo utilizar fecha y hora en SQL es útil al consultar resultados específicos como minutos, segundos, horas, días, meses, años; o una combinación de todos estos. Además, existen muchas funciones disponibles para fechas y horas que facilitan encontrar ciertos valores, como la fecha o hora actual. Si bien este tutorial solo utilizó operaciones de suma y resta en fechas y horas en SQL, puedes usar valores de fecha y hora con cualquier expresión matemática. Obtén más información de nuestra guía sobre expresiones matemáticas y funciones de agregado y pruébalas con tus consultas de fecha y hora.