Fecha y hora en SQL

De todos los tipos de datos de SQL, los temporales son los más complicados 🤯. La complejidad tiene varias causas; estas son algunas de ellas:

• las múltiples formas de indicar una fecha y una hora
• la existencia de zonas horarias
• lo poco evidente que resulta calcular algunos valores a partir de datos temporales. Por ejemplo, lo difícil que es calcular la edad.

Generación de datos temporales

Los datos temporales se pueden obtener de una de estas formas:

• copiar los datos de una columna existente con un tipo de dato temporal
• indicar la fecha y la hora mediante una representación en forma de cadena
• obtener datos temporales llamando a funciones integradas que devuelven un tipo de dato temporal

Representación de datos temporales como cadena

Para indicar la fecha y la hora se usan estos formatos:

Ejemplos válidos de valores temporales indicados como cadena:

MySQL 8.4
SELECT  CAST("2022-06-16 16:37:23" AS DATETIME) AS datetime_1,
        CAST("2014/02/22 16*37*22" AS DATETIME) AS datetime_2,
        CAST("20220616163723" AS DATETIME) AS datetime_3,
        CAST("2021-02-12" AS DATE) AS date_1,
        CAST("160:23:13" AS TIME) AS time_1,
        CAST("89" AS YEAR) AS year

MySQL 8.4
SELECT  CAST('2022-06-16 16:37:23' AS TIMESTAMP) AS timestamp_1,
        CAST('2014/02/22 16:37:22' AS TIMESTAMP) AS timestamp_2,
        CAST('20220616T163723' AS TIMESTAMP) AS timestamp_3,
        CAST('2021-02-12' AS DATE) AS date_1,
        CAST('16:23:13' AS TIME) AS time_1

En la consulta anterior se ha usado la función CAST para convertir de forma explícita la cadena en fecha y hora. Es necesaria cuando el servidor no espera un valor temporal y, por tanto, no convierte automáticamente la cadena al tipo necesario. Veremos la conversión de tipos con más detalle en el artículo «Funciones de conversión de tipos, CAST».

Funciones para generar fechas

Si necesitas obtener datos temporales de una cadena que no encaja en ninguno de los formatos que acepta la función CAST, puedes usar funciones especiales para analizar fechas.

MySQL 8.4
SELECT STR_TO_DATE('November 13, 1998', '%M %d, %Y') AS date;

MySQL 8.4
SELECT TO_DATE('November 13, 1998', 'Month DD, YYYY') AS date;

En cambio, para generar la fecha o la hora actual no hace falta crear una cadena y convertirla después en fecha, porque hay funciones integradas que devuelven estos valores.

MySQL 8.4
SELECT CURDATE(), CURTIME(), NOW();

MySQL 8.4
SELECT CURRENT_DATE, CURRENT_TIME, NOW();

Funciones para extraer datos temporales

A veces no necesitamos la fecha entera, sino solo una parte concreta, por ejemplo, el mes o el año.

Zonas horarias

Como en todo el mundo la gente quiere que el mediodía coincida más o menos con el momento en que el Sol está más alto, nunca se planteó usar una hora universal, y el mundo se dividió en 24 zonas horarias.

Como punto de referencia de la hora se usa UTC (Coordinated Universal Time, tiempo universal coordinado). Todas las demás zonas horarias se pueden describir por el número de horas de desfase respecto a UTC. Por ejemplo, la zona horaria de Moscú se puede describir como UTC+3.

La zona horaria es uno de los ajustes del servidor de bases de datos y se puede establecer:

MySQL 8.4
SET GLOBAL time_zone = '+03:00'; -- de forma global
SET time_zone = '+03:00';        -- para la sesión actual

MySQL 8.4
ALTER DATABASE mydb SET timezone = 'Europe/Moscow'; -- para la base de datos
ALTER USER myuser SET timezone = 'Europe/Moscow';   -- para un usuario concreto
SET TIME ZONE 'Europe/Moscow';                      -- para la sesión actual, por nombre de zona
SET TIME ZONE INTERVAL '+03:00' HOUR TO MINUTE;     -- para la sesión actual, por desfase respecto a UTC

Ejemplos de tareas con fechas y horas

Queremos detenernos en las tareas más habituales con datos temporales, en las que se cometen errores a menudo.

Calcular la edad

Cuando la tarea es calcular la edad de una persona a partir de su fecha de nacimiento, es muy tentador 😈 calcular la diferencia entre el año actual y el año de nacimiento:

MySQL 8.4
SELECT YEAR(NOW()) - YEAR('2003-07-03 14:10:26');

MySQL 8.4
SELECT EXTRACT(YEAR FROM NOW()) - EXTRACT(YEAR FROM TIMESTAMP '2003-07-03 14:10:26');

El problema de este enfoque es que no tiene en cuenta si la persona ya ha cumplido años este año o todavía no. Es decir, si en el momento de la consulta ya ha llegado el 3 de julio (07-03), la persona ha celebrado su cumpleaños y ya tiene 20 años; si no, todavía tiene 19. Esta resta no sirve de nada: en los dos casos da 20 años.

Si calcular la edad con la diferencia de años no funciona, quizá te apetezca calcularla con la diferencia de días entre las dos fechas, dividir esa diferencia entre el número de días del año y redondear hacia abajo:

MySQL 8.4
SELECT FLOOR(DATEDIFF(NOW(), '2003-07-03 14:10:26') / 365);

MySQL 8.4
SELECT FLOOR(EXTRACT(DAY FROM NOW() - TIMESTAMP '2003-07-03 14:10:26') / 365);

Esta solución es mucho más precisa que la anterior. Pero no es del todo exacta, porque existen los años bisiestos, que tienen 366 días. Aunque el error al calcular la edad de una sola persona por culpa de los años bisiestos es bastante pequeño, en cálculos como, por ejemplo, la edad media de una lista de personas, el error puede acumularse y distorsionar los valores reales.

Entonces, ¿cómo se calcula la edad correctamente?

MySQL 8.4
SELECT TIMESTAMPDIFF(YEAR, '2003-07-03 14:10:26', NOW());

MySQL 8.4
SELECT EXTRACT(YEAR FROM AGE(NOW(), TIMESTAMP '2003-07-03 14:10:26'));