El manejo de fechas en MySQL es fundamental para realizar análisis temporales y mantener la integridad de los datos. En este artículo, exploraremos diversas funciones y trabajos relacionados con fechas utilizando la base de datos de una comisaría.
Creación de la Base de Datos y Tablas
Comenzamos con la creación de la base de datos y las tablas necesarias para nuestro ejemplo:
CREATE DATABASE Comisaria;
USE Comisaria;
-- Tabla de agentes
CREATE TABLE Agentes (
id_agente INT AUTO_INCREMENT PRIMARY KEY,
nombre VARCHAR(50) NOT NULL,
rango VARCHAR(30),
salario DECIMAL(10, 2) NOT NULL,
fecha_ingreso DATE
);
-- Tabla de ciudadanos
CREATE TABLE Ciudadanos (
id_ciudadano INT AUTO_INCREMENT PRIMARY KEY,
nombre VARCHAR(50) NOT NULL,
direccion VARCHAR(100),
telefono VARCHAR(15)
);
-- Tabla de casos
CREATE TABLE Casos (
id_caso INT AUTO_INCREMENT PRIMARY KEY,
descripcion TEXT,
fecha_inicio DATE,
estado VARCHAR(20),
id_agente INT,
FOREIGN KEY (id_agente) REFERENCES Agentes(id_agente)
);
-- Tabla de reportes
CREATE TABLE Reportes (
id_reporte INT AUTO_INCREMENT PRIMARY KEY,
id_caso INT,
id_ciudadano INT,
descripcion TEXT,
fecha_reporte DATE,
FOREIGN KEY (id_caso) REFERENCES Casos(id_caso),
FOREIGN KEY (id_ciudadano) REFERENCES Ciudadanos(id_ciudadano)
);
-- Insertar datos en la tabla Agentes
INSERT INTO Agentes (nombre, rango, salario, fecha_ingreso) VALUES
('Juan Pérez', 'Sargento', 45000.00, '2015-06-23'),
('Ana Gómez', 'Teniente', 50000.00, '2018-09-15'),
('Carlos López', 'Cabo', 40000.00, '2020-01-10');
-- Insertar datos en la tabla Ciudadanos
INSERT INTO Ciudadanos (nombre, direccion, telefono) VALUES
('Pedro Martínez', 'Calle Falsa 123', '555-1234'),
('María Fernández', 'Avenida Siempre Viva 742', '555-5678'),
('Luis García', 'Boulevard Central 456', '555-8765');
-- Insertar datos en la tabla Casos
INSERT INTO Casos (descripcion, fecha_inicio, estado, id_agente) VALUES
('Robo en tienda', '2022-03-10', 'Abierto', 1),
('Asalto a mano armada', '2022-04-22', 'Cerrado', 2),
('Vandalismo en parque', '2022-05-15', 'En Proceso', 3);
-- Insertar datos en la tabla Reportes
INSERT INTO Reportes (id_caso, id_ciudadano, descripcion, fecha_reporte) VALUES
(1, 1, 'Robo de mercancía en tienda', '2022-03-10'),
(2, 2, 'Asalto a mano armada en banco', '2022-04-22'),
(3, 3, 'Daños en parque público', '2022-05-15');
Funciones y Procedimientos con Fechas
1. Función para Calcular la Antigüedad de un Agente
Esta función calcula la antigüedad de un agente en años desde su fecha de ingreso.
DELIMITER //
CREATE FUNCTION ObtenerAntiguedad(
p_fecha_ingreso DATE
) RETURNS INT
BEGIN
RETURN TIMESTAMPDIFF(YEAR, p_fecha_ingreso, CURDATE());
END //
DELIMITER ;
Para usar esta función en una consulta, haríamos lo siguiente:
SELECT nombre, ObtenerAntiguedad(fecha_ingreso) AS antiguedad
FROM Agentes;
2. Función para Obtener el Tiempo Transcurrido desde el Inicio de un Caso
Esta función calcula los días transcurridos desde el inicio de un caso hasta la fecha actual.
DELIMITER //
CREATE FUNCTION DiasDesdeInicioCaso(
p_fecha_inicio DATE
) RETURNS INT
BEGIN
RETURN DATEDIFF(CURDATE(), p_fecha_inicio);
END //
DELIMITER ;
Para usar esta función en una consulta, haríamos lo siguiente:
SELECT descripcion, DiasDesdeInicioCaso(fecha_inicio) AS dias_transcurridos
FROM Casos;
3. Procedimiento para Obtener Casos Abiertos por Más de N Días
Este procedimiento devuelve todos los casos que han estado abiertos por más de un número especificado de días.
DELIMITER //
CREATE PROCEDURE CasosAbiertosPorMasDe(
IN p_dias INT
)
BEGIN
SELECT id_caso, descripcion, fecha_inicio, estado
FROM Casos
WHERE estado = 'Abierto' AND DATEDIFF(CURDATE(), fecha_inicio) > p_dias;
END //
DELIMITER ;
Para llamar a este procedimiento y obtener casos abiertos por más de 30 días, haríamos lo siguiente:
CALL CasosAbiertosPorMasDe(30);
4. Procedimiento para Actualizar el Estado de Casos Cerrados Automáticamente
Este procedimiento cierra automáticamente los casos que han estado en proceso por más de 90 días.
DELIMITER //
CREATE PROCEDURE CerrarCasosAntiguos()
BEGIN
UPDATE Casos
SET estado = 'Cerrado'
WHERE estado = 'En Proceso' AND DATEDIFF(CURDATE(), fecha_inicio) > 90;
END //
DELIMITER ;
Para ejecutar este procedimiento y actualizar los estados de los casos, haríamos lo siguiente:
CALL CerrarCasosAntiguos();
Conclusión
El manejo de fechas en MySQL es esencial para realizar análisis temporales y asegurar la integridad de los datos. En este artículo, hemos creado varias funciones y procedimientos que permiten calcular la antigüedad de un agente, obtener el tiempo transcurrido desde el inicio de un caso, identificar casos abiertos por más de un período específico y cerrar automáticamente casos antiguos. Estas herramientas no solo mejoran la eficiencia de las consultas, sino que también aseguran que los datos se mantengan actualizados y precisos.