Guía de fechas y horas en MySQL: tipos de datos, funciones, formato y consultas de rango

1. Introducción

MySQL es un sistema de gestión de bases de datos ampliamente utilizado para crear aplicaciones web y bases de datos, y el manejo correcto de los datos de fecha es especialmente importante. Por ejemplo, los datos relacionados con fechas aparecen en muchos escenarios: gestión de publicaciones de blogs, seguimiento del historial de ventas de productos y almacenamiento del historial de inicio de sesión de usuarios. Al gestionar los datos de fecha de forma adecuada, puedes procesar la información de manera eficiente y proporcionar datos precisos a los usuarios.

En esta guía explicaremos de forma exhaustiva todo, desde los tipos de datos básicos de fecha/hora y cómo usar las funciones de fecha en MySQL, hasta los cálculos basados en fechas y las consultas por rangos. Este contenido está diseñado para usuarios principiantes e intermedios, e incluirá ejemplos prácticos de consultas basados en casos de uso reales, lo que lo hace útil para el trabajo diario.

Al leer este artículo, podrás hacer lo siguiente:

• Comprender las características de los tipos de datos de fecha/hora de MySQL y elegir el tipo adecuado para tus datos.
• Utilizar funciones de fecha para obtener y manipular fácilmente la fecha actual, fechas pasadas y fechas futuras.
• Extraer datos de periodos específicos mediante búsquedas y comparaciones de rangos de fechas.

Ahora, veamos los fundamentos de las fechas en MySQL a partir de la siguiente sección.

2. Visión general de los tipos de datos de fecha/hora de MySQL

Al gestionar fechas en MySQL, es importante elegir el tipo de dato de fecha/hora correcto según la información y el caso de uso. MySQL ofrece varios tipos de datos para manejar fechas y horas, cada uno con características y usos diferentes. En esta sección explicaremos en detalle los principales tipos de fecha/hora y sus casos de uso típicos.

DATE

El tipo DATE almacena solo la fecha (año, mes, día) y no incluye un componente de hora. El rango admitido es de “1000-01-01” a “9999-12-31”, por lo que puede manejar fechas desde el año 1000 hasta el 9999. Es adecuado para información que solo requiere la fecha, donde la hora no es relevante, como cumpleaños o aniversarios.

CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    birth_date DATE
);

TIME

El tipo TIME almacena la hora (horas, minutos, segundos). El rango admitido es de “-838:59:59” a “838:59:59”. Como puede representar tiempos negativos, también puede usarse para expresar diferencias de tiempo. Es útil para registrar horas de trabajo o duraciones de tareas.

CREATE TABLE work_log (
    id INT,
    task_name VARCHAR(50),
    duration TIME
);

DATETIME

El tipo DATETIME almacena tanto la fecha como la hora. El rango admitido es de “1000-01-01 00:00:00” a “9999-12-31 23:59:59”. No depende de zonas horarias, y puedes recuperar el valor almacenado exactamente como se guardó. Es adecuado para registrar marcas de tiempo de eventos pasados o programaciones futuras.

CREATE TABLE appointments (
    id INT,
    description VARCHAR(50),
    appointment_datetime DATETIME
);

TIMESTAMP

El tipo TIMESTAMP almacena fecha y hora y convierte automáticamente los valores según la zona horaria del servidor. El rango admitido es de “1970-01-01 00:00:01 UTC” a “2038-01-19 03:14:07 UTC”. Es compatible con el tiempo de época Unix y es apropiado para registrar marcas de tiempo e historial de cambios. Además, como TIMESTAMP puede establecer la hora actual por defecto, resulta conveniente para registrar automáticamente los tiempos de creación y actualización.

CREATE TABLE posts (
    id INT,
    title VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

YEAR

El tipo YEAR almacena solo el año. El rango admitido es de “1901” a “2155”. Es útil cuando se desea representar un año específico, como el año de fabricación o el año de fundación.

CREATE TABLE products (
    id INT,
    name VARCHAR(50),
    manufactured_year YEAR
);

Comparación y casos de uso de cada tipo de fecha/hora

Resumen

Los tipos de datos de fecha/hora de MySQL deben elegirse según las características de tus datos y cómo planeas utilizarlos. En la siguiente sección, analizaremos más de cerca las diferencias entre DATETIME y TIMESTAMP, que son especialmente fáciles de confundir.

3. Diferencias entre DATETIME y TIMESTAMP

Al trabajar con fechas y horas en MySQL, DATETIME y TIMESTAMP se usan con frecuencia, pero existen diferencias importantes entre ellos. Ambos almacenan año, mes, día, hora, minuto y segundo, pero difieren en cómo manejan las zonas horarias y el rango de tiempo que pueden almacenar. Debes elegir según tu caso de uso. En esta sección, explicaremos en detalle las diferencias y características de DATETIME y TIMESTAMP.

Características de DATETIME

• No depende de zonas horarias : El tipo DATETIME no se ve afectado por la configuración de zona horaria del servidor. Puedes recuperar el valor almacenado exactamente como se guardó.
• Rango : Soporta desde 1000-01-01 00:00:00 hasta 9999-12-31 23:59:59.
• Casos de uso : Adecuado para datos que deseas almacenar sin depender de una zona horaria específica, como eventos históricos o programaciones futuras.

Ejemplo: Almacenar la fecha/hora de un evento

Por ejemplo, si deseas mantener un valor de fecha/hora “universal” tal cual, DATETIME es una buena opción. El siguiente ejemplo registra un evento programado en una fecha y hora específicas.

CREATE TABLE events (
    id INT,
    event_name VARCHAR(50),
    event_datetime DATETIME
);

En esta tabla, la fecha/hora almacenada en event_datetime no se ve afectada por zonas horarias, y el valor se recupera exactamente como se guardó.

Características de TIMESTAMP

• Depende de zonas horarias : El tipo TIMESTAMP se almacena y recupera según la zona horaria del servidor. Al mover datos entre servidores en diferentes zonas horarias, se producirá la conversión correspondiente.
• Rango : Soporta desde 1970-01-01 00:00:01 UTC hasta 2038-01-19 03:14:07 UTC (basado en el rango de tiempo Unix).
• Soporte de auto‑actualización : Usando DEFAULT CURRENT_TIMESTAMP y ON UPDATE CURRENT_TIMESTAMP, MySQL puede registrar automáticamente la marca de tiempo actual al insertar o actualizar datos.
• Casos de uso : Ideal para rastrear automáticamente la hora de creación o actualización, por ejemplo, cuando deseas que una marca de tiempo cambie cada vez que se modifica un registro.

Ejemplo: Almacenar fechas de creación y actualización de publicaciones

Este ejemplo utiliza la función de auto‑actualización de TIMESTAMP para registrar las fechas de creación y actualización de entradas de blog.

CREATE TABLE blog_posts (
    id INT,
    title VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Con esta configuración, created_at se registra cuando la publicación se crea por primera vez, y updated_at se actualiza automáticamente cada vez que la publicación se edita.

Tabla comparativa: DATETIME vs TIMESTAMP

Cómo elegir

• Elige DATETIME si no deseas efectos de zona horaria : Por ejemplo, si quieres almacenar la hora de un evento como una hora local fija para un país/región específica, DATETIME puede ser apropiado.
• Elige TIMESTAMP si necesitas auto‑actualización o manejo de zona horaria : Por ejemplo, si deseas registrar automáticamente cuándo se actualizan las filas de la base de datos, TIMESTAMP es conveniente.

Resumen

DATETIME y TIMESTAMP tienen características diferentes. Al elegir el adecuado para tu propósito, puedes gestionar los datos de fecha/hora de manera más eficiente. En la siguiente sección, veremos las funciones de fecha esenciales en MySQL.

4. Conceptos básicos de funciones de fecha/hora de MySQL

MySQL ofrece muchas funciones para trabajar con fechas y horas, desde obtener la fecha/hora actual hasta añadir o restar intervalos. Estas funciones son útiles para la administración y el análisis de bases de datos. En esta sección, explicaremos los conceptos básicos de las funciones de fecha/hora de MySQL y sus casos de uso típicos.

Funciones para obtener la fecha/hora actual

Tres funciones comunes para obtener la fecha y hora actuales en MySQL son NOW(), CURDATE() y CURTIME().

NOW()

La función NOW() devuelve la fecha y hora actuales en el formato YYYY-MM-DD HH:MM:SS. Es útil para registrar y poner marcas de tiempo en los registros.

SELECT NOW(); -- Get the current date and time

CURDATE()

La función CURDATE() devuelve la fecha actual en el formato YYYY-MM-DD. No incluye la hora, por lo que es adecuada cuando solo necesitas la fecha.

SELECT CURDATE(); -- Get the current date

CURTIME()

La función CURTIME() devuelve la hora actual en el formato HH:MM:SS. Úsala cuando solo necesites la hora sin la fecha.

SELECT CURTIME(); -- Get the current time

Funciones para agregar/restar fechas

Para agregar o restar intervalos a una fecha, usa DATE_ADD() y DATE_SUB(). Estas funciones facilitan el cálculo de fechas futuras o pasadas.

DATE_ADD()

La función DATE_ADD() agrega un intervalo especificado a una fecha. Por ejemplo, es útil para obtener la fecha 7 días después o 1 mes después.

SELECT DATE_ADD('2023-01-01', INTERVAL 7 DAY); -- Returns 2023-01-08

DATE_SUB()

La función DATE_SUB() resta un intervalo especificado de una fecha. Úsala para calcular fechas pasadas.

SELECT DATE_SUB('2023-01-01', INTERVAL 1 MONTH); -- Returns 2022-12-01

Función para calcular diferencias de fechas

Para calcular la diferencia entre dos fechas, DATEDIFF() es conveniente. Por ejemplo, puedes calcular cuántos días han pasado desde una fecha específica, o el número de días entre dos fechas.

DATEDIFF()

La función DATEDIFF() devuelve la diferencia entre dos fechas en días. Es útil cuando quieres confirmar el número de días desde una fecha de inicio hasta una fecha de fin.

SELECT DATEDIFF('2023-01-10', '2023-01-01'); -- Returns 9

Otras funciones útiles para fechas

MySQL proporciona funciones adicionales útiles para fechas.

EXTRACT()

La función EXTRACT() extrae una parte específica (año, mes, día, etc.) de una fecha. Es útil cuando solo necesitas una porción de una fecha.

SELECT EXTRACT(YEAR FROM '2023-01-01'); -- Extract year (2023)
SELECT EXTRACT(MONTH FROM '2023-01-01'); -- Extract month (1)
SELECT EXTRACT(DAY FROM '2023-01-01'); -- Extract day (1)

DATE_FORMAT()

La función DATE_FORMAT() muestra una fecha en un formato especificado. Es útil cuando quieres mostrar fechas en un formato específico (por ejemplo, formato estilo japonés).

SELECT DATE_FORMAT('2023-01-01', '%Y-%m-%d'); -- Returns 2023-01-01

5. Métodos de formateo y conversión de fechas

En MySQL, puedes cambiar la forma en que se muestran las fechas a un formato más legible, o convertir cadenas de fechas en tipos de fecha. Esto te permite controlar de manera flexible los formatos de visualización y gestionar los datos de forma consistente incluso cuando las entradas llegan en formatos diferentes. En esta sección, explicaremos las funciones principales utilizadas para el formateo y conversión de fechas.

Formateo de fechas con la función DATE_FORMAT()

Usando DATE_FORMAT(), puedes mostrar valores de fecha en un formato especificado. Esto es especialmente útil cuando necesitas un formato de visualización diferente al estándar, como el formato japonés “YYYY年MM月DD日”.

Opciones de formato

Con DATE_FORMAT(), puedes usar opciones de formato como las siguientes:

• %Y : año de 4 dígitos
• %y : año de 2 dígitos
• %m : mes de 2 dígitos (01–12)
• %d : día de 2 dígitos (01–31)
• %H : hora de 2 dígitos (00–23)
• %i : minuto de 2 dígitos (00–59)
• %s : segundo de 2 dígitos (00–59)

Uso de ejemplo

Por ejemplo, para mostrar la fecha 2023-01-01 como “2023年01月01日”, puedes escribir lo siguiente:

SELECT DATE_FORMAT('2023-01-01', '%Y年%m月%d日'); -- Returns 2023年01月01日

También puedes mostrar una fecha y hora con barras, como “2023/01/01 12:30:45”.

SELECT DATE_FORMAT('2023-01-01 12:30:45', '%Y/%m/%d %H:%i:%s'); -- Returns 2023/01/01 12:30:45

Convertir cadenas a fechas con STR_TO_DATE()

La función STR_TO_DATE() convierte una cadena de fecha en un tipo de dato fecha de MySQL. Por ejemplo, es útil cuando deseas tratar una cadena como “2023年01月01日” como un valor DATE.

Ejemplo de uso

Para convertir la cadena “2023-01-01” en un valor DATE, escribe lo siguiente:

SELECT STR_TO_DATE('2023-01-01', '%Y-%m-%d'); -- Returns 2023-01-01 as a DATE

Para convertir una cadena al estilo japonés como “2023年01月01日” en una fecha, especifica el formato correspondiente:

SELECT STR_TO_DATE('2023年01月01日', '%Y年%m月%d日'); -- Returns 2023-01-01 as a DATE

Ejemplos de conversión de fechas desde diferentes formatos

En el uso real, los formatos de entrada de fechas pueden variar, por lo que puede ser necesario convertir desde diferentes formatos. A continuación se presentan algunos ejemplos.

1. Convertir “YYYY/MM/DD” a un valor DATE

   SELECT STR_TO_DATE('2023/01/01', '%Y/%m/%d'); -- Returns 2023-01-01 as a DATE
   

2. Convertir “MM-DD-YYYY” a un valor DATE

   SELECT STR_TO_DATE('01-01-2023', '%m-%d-%Y'); -- Returns 2023-01-01 as a DATE
   

De esta manera, incluso si los datos se ingresan en diferentes formatos, puedes almacenarlos y gestionarlos de forma coherente en un formato de fecha unificado.

Diferencias entre DATE_FORMAT y STR_TO_DATE, y cuándo usar cada una

• DATE_FORMAT() : Se utiliza para cambiar el formato de visualización de valores de fecha existentes. Como el cambio solo afecta la visualización, los datos almacenados no se modifican.
• STR_TO_DATE() : Se utiliza para convertir una cadena de fecha en un valor DATE o DATETIME de MySQL. Esto cambia el tipo de dato almacenado y facilita el procesamiento de fechas con otras funciones y consultas de MySQL.

Resumen

Al usar DATE_FORMAT() y STR_TO_DATE(), puedes gestionar de forma flexible cómo se muestra y almacena la información de fechas. Esto permite manejar la entrada de usuarios o del sistema con mayor flexibilidad mientras se mantiene una gestión de fechas coherente. En la siguiente sección, explicaremos los cálculos y comparaciones de fechas, y analizaremos más a fondo cómo calcular y comparar periodos usando datos de fechas.

6. Cálculos y comparaciones de fechas

MySQL ofrece varias funciones convenientes para cálculos y comparaciones de fechas. Estas permiten extraer datos de periodos específicos o calcular diferencias de fechas para análisis. En esta sección, presentaremos las funciones principales usadas para cálculos y comparaciones de fechas, junto con su uso.

Función para calcular diferencias de fechas: DATEDIFF()

La función DATEDIFF() devuelve la diferencia entre dos fechas en “días”. Es útil cuando deseas verificar los días transcurridos entre fechas o determinar cuántos días han pasado desde un evento pasado hasta hoy.

Ejemplo de uso

Por ejemplo, para calcular el número de días desde el 1 de enero de 2023 hasta el 10 de enero de 2023, escribe lo siguiente:

SELECT DATEDIFF('2023-01-10', '2023-01-01'); -- Returns 9

En este ejemplo, la diferencia entre la fecha de inicio y la fecha de fin es de 9 días, por lo que el resultado es 9.

Calcular diferencias por unidad con TIMESTAMPDIFF()

La función TIMESTAMPDIFF() calcula la diferencia entre dos fechas o datetimes en una unidad especificada (año, mes, día, hora, minuto, segundo). Por ejemplo, puedes calcular diferencias en “meses” o “horas” según sea necesario.

Ejemplo de uso

1. Calcular una diferencia en meses

   SELECT TIMESTAMPDIFF(MONTH, '2022-01-01', '2023-01-01'); -- Returns 12
   

2. Calcular una diferencia en horas

   SELECT TIMESTAMPDIFF(HOUR, '2023-01-01 00:00:00', '2023-01-02 12:00:00'); -- Returns 36
   

Comparar fechas

Las comparaciones de fechas se realizan usando los operadores de comparación estándar de MySQL (<, >, <=, >=, =). Esto permite extraer datos dentro de un periodo específico o incluir fechas pasadas/futuras como condiciones.

Ejemplo de uso

Por ejemplo, para extraer datos “el 1 de enero de 2023 o posteriores”, escribe lo siguiente:

SELECT * FROM events WHERE event_date >= '2023-01-01';

Especificar un rango con BETWEEN

El operador BETWEEN le permite especificar un rango de fechas para recuperar datos. Es útil cuando desea extraer registros que se encuentren dentro de un período específico.

Example Usage

Por ejemplo, para recuperar datos del 1 de enero de 2023 al 31 de enero de 2023, escriba lo siguiente:

SELECT * FROM events WHERE event_date BETWEEN '2023-01-01' AND '2023-01-31';

Cálculos de fechas con ADDDATE() y SUBDATE()

Puede usar ADDDATE() y SUBDATE() para añadir o restar un número de días a una fecha. Son útiles para calcular fechas futuras o pasadas.

Example Usage

1. Agregar 10 días a una fecha

   SELECT ADDDATE('2023-01-01', 10); -- Returns 2023-01-11
   

2. Restar 10 días a una fecha

   SELECT SUBDATE('2023-01-01', 10); -- Returns 2022-12-22
   

Resumen

Al utilizar las funciones de cálculo y comparación de fechas de MySQL, puede extraer datos de manera eficiente para períodos específicos y analizar los datos por rangos de tiempo. En la siguiente sección, examinaremos más de cerca el tema avanzado de “búsquedas por rango de fechas”.