Cómo trabajar con fechas y horas en SQL

Introducción

Al trabajar con bases de datos relacionales y lenguaje de consulta estructurado (SQL), puede que necesite trabajar con valores que representan fechas u horas específicas. Por ejemplo, podría necesitar calcular el total de horas dedicadas a una actividad específica, o quizás agregar valores de fecha u hora mediante operadores y funciones matemáticas para calcular su suma o promedio.

En este tutorial, aprenderá a usar fechas y horas en SQL. Comenzará realizando cálculos y usando diversas funciones con fechas y horas usando solo la instrucción SELECT. Después, practicará ejecutando consultas con datos de ejemplo y aprenderá a implementar la función CAST para que la salida sea más legible.

Requisitos previos

Para completar este tutorial, necesitarás:

• Un servidor que ejecuta Ubuntu 20.04, con un usuario no root con privilegios administrativos de sudo y un firewall habilitado.
• MySQL está instalado y protegido en el servidor.

Nota: Tenga en cuenta que muchos sistemas de gestión de bases de datos relacionales utilizan sus propias implementaciones de SQL. Aunque los comandos mencionados en este tutorial funcionan en la mayoría de los RDBMS, la sintaxis o el resultado exactos pueden variar si los prueba en un sistema distinto de MySQL.

Para practicar el uso de fechas y horas en este tutorial, necesitará una base de datos y una tabla con datos de ejemplo. Si no tiene una lista para insertar, puede leer la sección "Conectarse a MySQL y Configurar una Base de Datos de Ejemplo" para aprender a crear una base de datos y una tabla. Este tutorial hará referencia 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 su base de datos SQL se ejecuta en un servidor remoto, acceda a su servidor mediante SSH desde su máquina local:

ssh sammy@your_server_ip

Luego, abra el símbolo del sistema de MySQL y reemplace Sami con la información de su cuenta MySQL:

mysql -u sammy -p

Crea una base de datos llamada datetimeDB:

CREATE DATABASE datetimeDB;

Si la base de datos se crea correctamente, recibirá el siguiente resultado:

Output
Query OK, 1 row affected (0.01 sec)

Para seleccionar la base de datos datetimeDB, ejecute la siguiente declaración USE:

USE datetimeDB;

Output
Database changed

Tras seleccionar la base de datos, cree una tabla dentro de ella. En este tutorial, crearemos una tabla que contenga los resultados de dos corredores en diferentes carreras que han corrido a lo largo de un año. Esta tabla tiene las siguientes siete columnas:

• race_id: Representa valores de tipo de datos int y actúa como clave principal de la tabla, lo que significa que cada valor de esta columna actúa como un identificador único para la fila correspondiente.
• runner_name: utiliza el tipo de datos varchar con un máximo de 30 caracteres para los nombres de los dos corredores Bolt y Felix.
• race_name: contiene tipos de carrera con tipo de datos varchar de hasta 20 caracteres.
• start_day: Utiliza el tipo de dato DATE para rastrear la fecha de una coincidencia específica según el año, mes y día. Este tipo de dato cumple con 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 del partido con el tipo de dato TIME en horas, minutos y segundos (HH:MM:SS). Este tipo de dato sigue el formato de 24 horas, por ejemplo, 15:00 para el equivalente a las 15:00.
• total_miles: Muestra el kilometraje total de cada carrera utilizando el tipo de dato decimal, ya que muchos de los kilómetros totales por carrera no son números enteros. En este caso, el decimal especifica una precisión de tres con una escala de uno, lo que significa que cada valor de esta columna puede tener tres dígitos, uno de los cuales debe estar a la derecha de la coma decimal.
• end_time: Utiliza el tipo de dato TIMESTAMP para registrar el tiempo de los corredores al final de la carrera. Este tipo de dato combina la fecha y la hora en una cadena, y su formato es una combinación de FECHA y HORA: (AAAA-MM-DD HH:MM:SS).

Cree 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, introduzca 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 haya ingresado los datos, estará listo para comenzar a practicar aritmética y funciones con fechas y horas en SQL.

Usando la cuenta con fecha y hora

En SQL, se pueden manipular valores de fecha y hora mediante expresiones matemáticas. Solo se necesita un operador matemático y los valores que se desean calcular.

Por ejemplo, supongamos que desea encontrar una fecha con un número específico de días de diferencia. La siguiente consulta toma un valor de fecha (2022-10-05) y le suma 17 para devolver un valor de fecha diecisiete días posterior a la fecha especificada en la consulta. Tenga en cuenta que este ejemplo especifica 2022-10-05 como valor de FECHA para garantizar que el SGBD 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 muestra este resultado, 17 días después del 5 de octubre de 2022 es el 22 de octubre de 2022, o el 22 de octubre de 2022.

Como otro ejemplo, supongamos que desea calcular el total de horas entre dos horas diferentes. Puede hacerlo restando dos horas entre sí. Para la siguiente consulta, 11:00 es el primer valor de hora y 3:00 es el segundo. Aquí debe especificar que ambos valores son de tipo HORA para obtener 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 le indica que la diferencia entre las 11:00 y las 3:00 es de 80.000, u 8 horas.

Ahora practique el uso de cálculos con la información de fecha y hora de los datos de ejemplo. Para la primera consulta, calcule el tiempo total que tardaron los corredores en completar 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)

Observará que la salida de la columna "tiempo_total" es bastante larga y difícil de leer. A continuación, le mostraremos cómo usar la función CAST para transformar estos valores de datos y que sean más fáciles de leer.

Ahora bien, si solo le interesa el rendimiento de cada corredor en carreras más largas, como medias maratones y maratones, podría consultar sus datos para obtener esa información. Para esta consulta, reste end_time de start_time y limite los resultados mediante una cláusula WHERE para recuperar los datos donde total_miles sea mayor 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, realizó cálculos de fecha y hora con la instrucción SELECT y, con fines prácticos, con datos de ejemplo. A continuación, practicará consultas con diversas funciones de fecha y hora.

Uso de funciones de fecha y hora y expresiones de rango

Existen varias funciones que permiten buscar y manipular valores de fecha y hora en SQL. Estas funciones se utilizan normalmente 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 permiten buscar la fecha y hora actuales consultando los valores current_date y current_time.

Para encontrar la fecha de hoy, por ejemplo, la sintaxis es corta y consta solo de la declaración SELECT y la función current_date, así:

SELECT current_date;

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

Usando 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 prefiere consultar la fecha y la hora en la salida, utilice 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 usar funciones de fecha y hora como estas en funciones aritméticas similares a las de la sección anterior. Por ejemplo, supongamos que quieres saber qué fecha era hace 11 días a partir de hoy. En este caso, puedes usar la misma sintaxis que usaste para consultar la función current_date y luego restarle 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 muestra este resultado, 11 días antes de current_date (al momento de escribir este artículo) era 2022-02-06, o 6 de febrero de 2022. Ahora intente la misma operación, pero reemplace current_date con la función current_time:

SELECT current_time - 11;

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

Este resultado muestra que al restar 11 del valor current_time, se restan 11 segundos. La operación realizada anteriormente con la función current_date interpretó 11 como días, no segundos. Esta inconsistencia en la interpretación de los números puede resultar confusa al trabajar con funciones de fecha y hora. En lugar de obligarle a manipular valores de fecha y hora con cálculos como este, muchos sistemas de gestión de bases de datos permiten ser más explícitos mediante el uso de sentencias INTERVAL.

Las expresiones INTERVAL permiten especificar una fecha u hora anterior o posterior a un intervalo de tiempo especificado a partir de una expresión de fecha u hora. Deben tener el siguiente formato:

INTERVAL value unit

Por ejemplo, para encontrar la fecha dentro de cinco días, puede ejecutar la siguiente consulta:

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

Este ejemplo encuentra el valor de current_date y le añade la expresión de intervalo INTERVAL '5' DAY. Esto devuelve la fecha 5 días después:

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 un resultado similar, aunque no idéntico:

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 puede restar intervalos de tiempo de fechas u horas para encontrar valores anteriores a un valor de fecha específico:

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 las instrucciones INTERVAL dependen de su elección de DBMS, aunque la mayoría tendrá opciones como HORA, MINUTO y SEGUNDO:

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 el trabajo con los datos de muestra que ingresó en el primer paso.

Uso de las funciones CAST y Agregado con fechas y horas

Recuerde el tercer ejemplo de la sección "Uso de aritmética con fechas y horas", donde ejecutó la siguiente consulta para restar end_time de start_time y calcular el total de horas que cada corredor corrió en cada carrera. Sin embargo, el resultado generó una columna con una salida muy larga que seguía el tipo de datos TIMESTAMP establecido 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)

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

Para facilitar la lectura e interpretación de estos datos, puede usar la función CAST para convertir estos valores enteros largos al tipo de dato TIME. Para ello, comience con CAST e inmediatamente después, añada un paréntesis de apertura, los valores que desea convertir y, a continuación, la palabra clave AS y el tipo de dato al que desea convertirlos.

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

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 en esta salida a TIEMPO, lo que la hace muy fácil de leer y comprender.

Ahora, usemos algunas funciones agregadas en combinación con la función CAST para encontrar el tiempo más corto, más largo y total de los resultados de cada corredor. Primero, busque el tiempo mínimo (o más corto) con la función MIN. Nuevamente, para mayor claridad, use CAST para convertir los valores de TIMESTAMP a TIME. Tenga en cuenta que al usar dos funciones, como en este ejemplo, se requieren dos pares de paréntesis, y el cálculo del total de horas (fin_hora - inicio_hora) debe colocarse en uno de ellos. Finalmente, agregue una cláusula GROUP BY para organizar estos valores por la columna runner_name y mostrar los resultados de la carrera del corredor:

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 del corredor, en este caso al menos seis minutos y 30 segundos para Bolt y siete minutos y 15 segundos para Felix.

A continuación, encuentre el tiempo de ejecución más largo para cada ejecutor. Puede usar la misma sintaxis que en la instrucción anterior, pero esta vez reemplace MIN por 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)

Este resultado indica que el tiempo de carrera más largo de Bolt fue de tres horas, 23 minutos y 10 segundos, y el de Felix, de cuatro horas, dos minutos y 10 segundos.

Ahora, busquemos información general sobre el total de horas que cada corredor dedicó a correr. Para esta consulta, combine la función SUM para calcular el total de horas según end_time - start_time y use CAST para convertir esos valores a TIME. No olvide agregar GROUP BY para organizar los resultados de cada corredor:

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, este resultado muestra que MySQL interpreta el tiempo total como un entero. Si leemos estos resultados como tiempos, el tiempo total de Bolt se dividiría en cinco horas, 28 minutos y 80 segundos. Y el tiempo de Felix se dividiría en siete horas, 61 minutos y 49 segundos. Como puede observar, esto no es un desglose lógico del tiempo, lo que indica que se calcula como un entero y no como tiempo. Por ejemplo, si intenta esto en un SGBD diferente, como PostgreSQL, 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 horas y los calcula como tales, de modo que los resultados de Felix se desglosan en un total de 10 horas, un minuto y 44 segundos. Y los de Bolt son seis horas, nueve minutos y 20 segundos. Este es un ejemplo de cómo diferentes implementaciones de DBMS pueden interpretar los valores de los datos de forma diferente, incluso utilizando la misma consulta y el mismo conjunto de datos.

Resultado

Comprender cómo usar fechas y horas en SQL es útil al buscar resultados específicos como minutos, segundos, horas, días, meses, años o una combinación de todos ellos. Además, existen numerosas funciones para fechas y horas que facilitan la búsqueda de valores específicos, como la fecha u hora actual. Si bien este tutorial solo cubre cálculos de suma y resta de fechas y horas en SQL, puede usar valores de fecha y hora con cualquier expresión matemática. Obtenga más información en nuestra guía de expresiones matemáticas y funciones de suma y pruébelas en sus búsquedas de fecha y hora.