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.

person encoding in laptop
Photo by Lukas on Pexels.com
error: Content is protected !!

Descubre más desde InfoGonzalez - Blog de formador e informático

Suscríbete ahora para seguir leyendo y obtener acceso al archivo completo.

Seguir leyendo

Este sitio web utiliza cookies, si necesitas más información puedes visitar nuestra política de privacidad    Ver
Privacidad
Creative Commons License
Except where otherwise noted, the content on this site is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.