Guía de fechas en MySQL: tipos, funciones y búsquedas

1. Introducción

• Comprender las características de los tipos de datos de fecha manejados por MySQL y poder seleccionar el tipo adecuado según los datos.
• Utilizar funciones de fecha para obtener y manipular fácilmente la fecha actual, fechas pasadas y fechas futuras.
• Realizar búsquedas y comparaciones por rango de fechas, lo que permite extraer datos basados en un período específico.

2. Resumen de los tipos de fecha de MySQL

Tipo DATE

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

Tipo TIME

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

Tipo DATETIME

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

Tipo TIMESTAMP

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

Tipo YEAR

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

Comparación y usos de los tipos de fecha

Conclusión

3. Diferencias entre los tipos DATETIME y TIMESTAMP

Características del tipo DATETIME

• No depende de la zona horaria: el tipo DATETIME no se ve afectado por la configuración de zona horaria del servidor. Por lo tanto, se puede obtener la hora guardada tal cual.
• Rango: puede manejar desde 1000-01-01 00:00:00 hasta 9999-12-31 23:59:59.
• Uso: es adecuado para datos que se desean guardar sin depender de una zona horaria específica, como eventos pasados o futuros.

Ejemplo de uso: guardar la fecha y hora de un evento

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

Características del tipo TIMESTAMP

• Depende de la zona horaria: el tipo TIMESTAMP se guarda y recupera según la zona horaria del servidor. Por lo tanto, al mover datos entre servidores con diferentes zonas horarias, se realiza la conversión correspondiente.
• Rango: puede manejar desde 1970-01-01 00:00:01 UTC hasta 2038-01-19 03:14:07 UTC (basado en el rango del tiempo Unix).
• Función de actualización automática: el tipo TIMESTAMP permite, mediante DEFAULT CURRENT_TIMESTAMP o ON UPDATE CURRENT_TIMESTAMP, registrar automáticamente la hora actual al insertar o actualizar datos.
• Uso: es adecuado cuando se desea registrar la fecha y hora de creación o actualización de datos, es decir, registrar la hora actual cada vez que cambia.

Ejemplo de uso: guardar la fecha y hora de creación y actualización de una publicación

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
);

Tabla comparativa de los tipos DATETIME y TIMESTAMP

Puntos clave para la selección

• Si no se desea que la zona horaria influya, elija el tipo DATETIME: por ejemplo, al guardar la fecha y hora de un evento con una zona horaria específica de un país o región, el tipo DATETIME es apropiado.
• Si se desea actualización automática y soporte de zona horaria, elija el tipo TIMESTAMP: por ejemplo, cuando se quiere registrar automáticamente cuándo se actualizan los datos de la base de datos, el tipo TIMESTAMP es útil.

Resumen

4. Conceptos básicos de funciones de fecha en MySQL

Funciones para obtener la fecha y hora actuales

NOW()

SELECT NOW(); -- Obtener la fecha y hora actuales

CURDATE()

SELECT CURDATE(); -- Obtener la fecha actual

CURTIME()

SELECT CURTIME(); -- Obtener la hora actual

Funciones para sumar o restar fechas

DATE_ADD()

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

DATE_SUB()

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

Funciones para calcular la diferencia entre fechas

DATEDIFF()

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

Otras funciones útiles para manipular fechas

EXTRACT()

SELECT EXTRACT(YEAR FROM '2023-01-01'); -- Extrae el año (2023)
SELECT EXTRACT(MONTH FROM '2023-01-01'); -- Extrae el mes (1)
SELECT EXTRACT(DAY FROM '2023-01-01'); -- Extrae el día (1)

DATE_FORMAT()

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

Resumen

5. Formato y método de conversión de fechas

Formato de fecha con la función DATE_FORMAT()

iones de formato

• %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: minutos de 2 dígitos (00‑59)
• %s: segundos de 2 dígitos (00‑59)

Ejemplo de uso

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

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

Conversión de cadena a fecha con la función STR_TO_DATE()

Ejemplo de uso

SELECT STR_TO_DATE('2023-01-01', '%Y-%m-%d'); -- devuelve 2023-01-01 como tipo DATE

SELECT STR_TO_DATE('2023-01-01', '%Y-%m-%d'); -- devuelve 2023-01-01 como tipo DATE

Ejemplos de conversión de fechas con diferentes formatos

1. Conversión del formato “YYYY/MM/DD” a tipo DATE

   SELECT STR_TO_DATE('2023/01/01', '%Y/%m/%d'); -- devuelve 2023-01-01 como tipo DATE

2. Conversión del formato “MM-DD-YYYY” a tipo DATE

   SELECT STR_TO_DATE('01-01-2023', '%m-%d-%Y'); -- devuelve 2023-01-01 como tipo DATE

Diferencias y usos de DATE_FORMAT y STR_TO_DATE

• DATE_FORMAT(): se usa para cambiar el formato de visualización de los datos de fecha existentes. El cambio de formato solo afecta la presentación, no modifica los datos almacenados.
• STR_TO_DATE(): se usa para convertir datos de fecha en formato de cadena a los tipos DATE o DATETIME de MySQL. El tipo de datos guardado cambia, facilitando el manejo de fechas en otras funciones y consultas de MySQL.

Resumen

6. Cálculo y comparación de fechas

Funciones para calcular la diferencia de fechas: DATEDIFF()

Ejemplo de uso

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

Cálculo de diferencias por unidad de período con TIMESTAMPDIFF()

Ejemplo de uso

1. Calcular la diferencia en meses

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

2. Calcular la diferencia en horas

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

Comparación de fechas

Ejemplo de uso

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

Selección de rango usando BETWEEN

Ejemplo de uso

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

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

Ejemplo de uso

1. Sumar 10 días a una fecha

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

2. Restar 10 días a una fecha

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

Resumen

7. Búsqueda de rango de fechas

Búsqueda de rango básica: cláusula BETWEEN

Ejemplo de uso

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

Búsqueda de rango usando operadores de comparación

Ejemplo de uso

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

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

Especificación de rangos de fechas dinámicos

Ejemplo de uso: obtener datos de los últimos 30 días

SELECT * FROM events WHERE event_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

Ejemplo de uso: obtener datos de un período futuro específico

SELECT * FROM events WHERE event_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 1 MONTH);

Obtención de datos periódicos

Ejemplo de uso: obtener datos de un día específico de cada mes

SELECT * FROM events WHERE DAY(event_date) = 1;

Ejemplo de uso: obtener datos de un día de la semana específico

SELECT * FROM events WHERE WEEKDAY(event_date) = 0; -- Lunes es 0, martes es 1 y así sucesivamente

Mejora del rendimiento de la búsqueda de rango

Ejemplo de uso: crear un índice

CREATE INDEX idx_event_date ON events(event_date);

Resumen

8. Actualización automática de fechas y configuración de valores predeterminados

Establecer automáticamente la hora actual

Ejemplo de uso: configuración automática de la fecha de creación

CREATE TABLE blog_posts (
    id INT PRIMARY KEY,
    title VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Configuración de actualización automática: ON UPDATE CURRENT_TIMESTAMP

Ejemplo de uso: configuración automática de la fecha de actualización

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

Configuración de actualización automática para el tipo DATETIME

Ejemplo de uso: configuración automática para el tipo DATETIME

CREATE TABLE blog_entries (
    id INT PRIMARY KEY,
    title VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Ejemplos de uso de actualización automática y valores predeterminados

Ejemplo de uso: última fecha de inicio de sesión del usuario

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Consideraciones

• Campos de actualización automática múltiples: En MySQL, si se desea tener varios campos de actualización automática en una tabla, solo es posible con columnas de tipo TIMESTAMP. En el tipo DATETIME no se puede aplicar la configuración de actualización automática a varias columnas.
• Dependencia de versión: La actualización automática del tipo DATETIME está soportada a partir de MySQL 5.6, por lo que depende de la versión. Al usarla, verifique la versión de MySQL.

Resumen

9. Ejemplo práctico: Cómo utilizar fechas en MySQL

Obtener datos de los últimos 30 días

Ejemplo de uso: extracción de registros de acceso

SELECT * FROM access_logs WHERE log_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

Agregar datos de ventas de un mes específico

Ejemplo de uso: agregación de ventas de enero de 2023

SELECT SUM(amount) AS total_sales
FROM sales
WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31';

Obtener datos de un día de la semana específico

Ejemplo de uso: obtener eventos de cada lunes

SELECT * FROM events WHERE WEEKDAY(event_date) = 0; -- Lunes es 0

Análisis de tendencias de datos usando fechas

Ejemplo de uso: agregación de accesos diarios

SELECT DATE(log_date) AS date, COUNT(*) AS access_count
FROM access_logs
WHERE log_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AND CURDATE()
GROUP BY DATE(log_date);

Mostrar la evolución de ventas por mes

Ejemplo de uso: agregación de ventas mensuales

SELECT YEAR(sale_date) AS year, MONTH(sale_date) AS month, SUM(amount) AS total_sales
FROM sales
GROUP BY YEAR(sale_date), MONTH(sale_date)
ORDER BY year, month;

Obtener datos de un rango horario específico

Ejemplo de uso: obtener datos de accesos de la mañana

SELECT * FROM access_logs WHERE HOUR(log_time) BETWEEN 9 AND 12;

Conclusión

10. FAQ

Preguntas frecuentes y respuestas

Q1. ¿Cómo obtener la fecha actual en MySQL?

SELECT NOW(); -- fecha y hora actuales
SELECT CURDATE(); -- solo la fecha actual

Q2. ¿Cómo formatear una fecha a un formato específico en MySQL?

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

Q3. ¿Cuál es la diferencia entre TIMESTAMP y DATETIME?

Q4. ¿Cómo sumar o restar fechas en MySQL?

SELECT DATE_ADD('2023-01-01', INTERVAL 7 DAY); -- 2023-01-08
SELECT DATE_SUB('2023-01-01', INTERVAL 7 DAY); -- 2022-12-25

Q5. ¿Cómo obtener la diferencia en días entre dos fechas en MySQL?

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

Q6. ¿Cómo configurar la actualización automática de una columna de fecha?

CREATE TABLE posts (
    id INT PRIMARY KEY,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Q7. ¿Cómo obtener los datos de los últimos 30 días?

SELECT * FROM events WHERE event_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

Q8. ¿Cómo obtener datos de MySQL especificando el día de la semana?

SELECT * FROM events WHERE WEEKDAY(event_date) = 0; -- El lunes es 0

Resumen

11. Resumen

Puntos clave del artículo

1. Selección del tipo de fecha: Es importante comprender las diferencias entre los tipos de fecha de MySQL como DATE、TIME、DATETIME、TIMESTAMP、YEAR, y elegir el tipo adecuado según el uso.
2. Diferencias entre los tipos DATETIME y TIMESTAMP: El tipo DATETIME no depende de la zona horaria y es adecuado para almacenar una fecha y hora fijas, mientras que el tipo TIMESTAMP depende de la zona horaria y es apropiado para el registro automático de fechas de creación y actualización.
3. Uso de funciones de fecha: Con funciones de fecha como NOW()、CURDATE()、DATE_ADD()、DATE_SUB(), es fácil obtener y calcular fechas.
4. Formato y conversión de fechas: Es posible cambiar el formato de visualización con DATE_FORMAT() y convertir cadenas a fechas con STR_TO_DATE(), lo que permite una gestión de datos flexible.
5. Búsqueda de rangos de fechas: Utilizando la cláusula BETWEEN y operadores de comparación, se puede extraer datos de manera eficiente dentro de un período específico.
6. Configuración de actualización automática y valores predeterminados: Al establecer actualizaciones automáticas y valores predeterminados en los tipos TIMESTAMP o DATETIME, la gestión de fechas de creación y actualización se simplifica.
7. Ejemplos de uso práctico: Se presentaron ejemplos de consultas avanzadas útiles en el trabajo, como la extracción de datos de períodos específicos y la agregación de la evolución de ventas mensuales mediante la manipulación de fechas.

Cómo aprovechar la manipulación de fechas en MySQL