Trabajos y Funciones con Fechas en MySQL utilizando la Base de Datos «Comisaria»

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.