Una función agregada para MySQL 5 para el calculo de fechas:

Me encontraba observando ve.planetalinux.org cuando me di cuenta de dos cosas:
1.- alguien habia sindicalizado mi blog sin preguntarme; que bien, es bueno saber que hay gente que te lee.
2.- hay alguien que escribio una funcion en php para un sistema de noticias, la funcion debia retornar algo como “esto fue escrito hace: 1 año, 4 meses, 2 dias, 7 horas, 4 minutos y 30 segundos”.
La funcion sonaba interesante para mi, pero no para usarla en PHP, sino por mi fiel idea que el procesamiento en bruto de los datos debe ser ejecutado por el RDBMS y no por el cliente, decidi enfrentar ese reto de lograr que mySQL me emitiera la misma informacion.

Paso 1: Crear una sentencia SQL que retorne la informacion solicitada:

SELECT CONCAT(year(curdate()) – YEAR(‘2005-07-21 08:15:00’), ‘ año, ‘, month(curdate()) – month(‘2005-07-21 08:15:00’), ‘ meses, ‘, day(curdate()) – day(‘2005-07-21 08:15:00’), ‘ dias, ‘, abs(time_format(‘2005-07-21 08:15:00’, ‘%h’) – hour(curtime())), ‘ horas, ‘, abs(time_format(‘2005-07-21 08:15:00’, ‘%i’) – minute(curtime())), ‘ minutos y ‘, abs(time_format(‘2005-07-21 08:15:00’, ‘%s’) – second(curtime())), ‘ segundos’) AS tiempo_transcurrido

Es una sentencia interesante, larga, pero interesante, ahora bien, si a eso lo llevamos a una funcion agregada de mySQL 5 nos queda:

Paso 2: Crear una funcion agregada para mySQL 5 llamada tiempo_transcurrido:

DELIMITER $$
DROP FUNCTION IF EXISTS `mydb`.`tiempo_transcurrido`$$
CREATE FUNCTION `mydb`.`tiempo_transcurrido` (fecha_usuario DATETIME) RETURNS VARCHAR(75)
BEGIN
DECLARE fecha_t VARCHAR(75);
DECLARE fecha DATE;
DECLARE tiempo TIME;
DECLARE anno TINYINT(2);
DECLARE mes TINYINT(2);
DECLARE dia TINYINT(2);
DECLARE hora TINYINT(2);
DECLARE minuto TINYINT(2);
DECLARE segundo TINYINT(2);
SELECT curdate() INTO fecha;
SELECT curtime() INTO tiempo;
SELECT YEAR(fecha) – YEAR(fecha_usuario) INTO anno;
SELECT MONTH(fecha) – MONTH(fecha_usuario) INTO mes;
SELECT DAY(fecha) – DAY(fecha_usuario) INTO dia;
SELECT abs(HOUR(fecha_usuario) – HOUR(tiempo)) INTO hora;
SELECT abs(MINUTE(fecha_usuario) – MINUTE(tiempo)) INTO minuto;
SELECT abs(SECOND(fecha_usuario) – SECOND(tiempo)) INTO segundo;
IF anno > 0 THEN
BEGIN
IF anno > 1 THEN
BEGIN
SELECT CONCAT(anno, ‘ años, ‘) INTO fecha_t;
END;
ELSE
BEGIN
SELECT CONCAT(anno, ‘ año, ‘) INTO fecha_t;
END;
END IF;
END;
END IF;
IF mes > 1 THEN
BEGIN
SELECT CONCAT(fecha_t, mes , ‘ meses, ‘) INTO fecha_t;
END;
ELSE
BEGIN
SELECT CONCAT(fecha_t, mes, ‘ mes, ‘) INTO fecha_t;
END;
END IF;
IF dia > 1 THEN
BEGIN
SELECT CONCAT(fecha_t, dia , ‘ dias, ‘) INTO fecha_t;
END;
ELSE
BEGIN
SELECT CONCAT(fecha_t, dia, ‘ dia, ‘) INTO fecha_t;
END;
END IF;
IF hora > 1 THEN
BEGIN
SELECT CONCAT(fecha_t, hora , ‘ horas, ‘) INTO fecha_t;
END;
ELSE
BEGIN
SELECT CONCAT(fecha_t, hora, ‘ hora, ‘) INTO fecha_t;
END;
END IF;
IF minuto > 1 THEN
BEGIN
SELECT CONCAT(fecha_t, minuto , ‘ minutos’) INTO fecha_t;
END;
ELSE
BEGIN
SELECT CONCAT(fecha_t, minuto, ‘ minuto’) INTO fecha_t;
END;
END IF;
IF segundo 1 THEN
BEGIN
SELECT CONCAT(fecha_t, ‘ y ‘, segundo , ‘ segundos.’) INTO fecha_t;
END;
ELSE
BEGIN
SELECT CONCAT(fecha_t, ‘ y ‘, segundo, ‘ segundo.’) INTO fecha_t;
END;
END IF;
RETURN fecha_t;
END$$
DELIMITER ;

Sip, es mas larga, pero a la final mas util, su convocatoria devuelve algo como esto:
SELECT `mydb`.`tiempo_transcurrido`(‘2004-07-21 09:15:00’)
Retorna:
2 años, 2 meses, 6 dias, 7 horas, 7 minutos y 43 segundos.
(al momento que la ejecuté pues usa CURRENT_TIME (la fecha actual) como valor para el computo).

Espero esta funcion sea de utilidad no solo para lo cual fue diseñada sino para demostrar lo útil y versatil de mySQL 5 y sus procedimientos almacenados y funciones agregadas.

Acerca de phenobarbital

http://about.me/phenobarbital

Publicado el 27 septiembre 2006 en Otros lenguajes, Programacion. Añade a favoritos el enlace permanente. 8 comentarios.

  1. ¿Y ya te registraste en el nuevo directorio de blogs venezolanos? http://www.to2blogs.com

  2. como hago para exportar mis procedures creados!!

  3. hvgo, si tenes mySQL Query Browser o Administrator, podes exportarlos por ahi (en el Administrator, como si exportaras cualquier otra tabla)

  4. Hola!
    Acabo de darme cuenta que me agregaste a tu listado de blogs gracias a una funcionalidad de Technorati, gracias, es un detalle! veo que alguien me lee aparte de mis amigos😀

    Interesantes tus posts de programación, a mi se me da muy mal programar, lo estudié, trabajé con Power Builder pero al final me fui por otro lado de la informática.

    Un saludo!!!

  5. cuando remplazo la fecha ‘2005-07-21 08:15:00’ por ‘2007-12-01 08:15:00’ el resultado que devuelve es este: “1 año, -11 meses, 22 dias, 4 horas, 6 minutos y 10 segundos” cuando lo correcto seria “0 año, 1 meses, 22 dias, 4 horas, 6 minutos y 10 segundos”, alguien ya corrigio esto? yo use esta funcion hace meses y ahora me encuentro con este problema cuando cambio el año, claro el motivo se deve a que se realiza una resta de año, mes, dia, para hallar dicha solucion pero no es la correcta por lo que se puede ver, esto lo probe con la funcion para MYSQL 4, el procedimiento almacenado para MYSQL5 no lo loghe hacer funcionar. si alquien tiene una respueste les ruego la posteen, mil gracias…

  6. Si a alguien todabia le interesa el tema, Elabore esta funcion basandome en una realizada en T-SQL, lo que hace es retornar los dias, meses y años que ha vivido una persona, la uso para fines de salud, pero el que guste la puede adaptar a sus necesidades, ademas la implementacion del calculo de tiempo en horas, minutos y segundo ya no es complicada

    saludos

    DELIMITER $$

    DROP FUNCTION IF EXISTS `DB_CensoAD_dbo`.`ce`$$
    CREATE DEFINER=`root`@`localhost` FUNCTION `ce`(fnacim DATETIME, hasta DATETIME) RETURNS char(25) CHARSET latin1
    READS SQL DATA
    BEGIN
    #dateformat= yyyymmdd
    DECLARE desde DATETIME;
    DECLARE ano INT;
    DECLARE mes INT;
    DECLARE dia INT;
    DECLARE rndate DATETIME;
    DECLARE fecha CHAR(25);
    SET desde = fnacim;
    SET ano=0;
    SET mes=0;
    SET dia=0;
    SET fecha=”;
    SET rndate=desde; #rndate = runningdate
    #calcula el año
    IF YEAR(hasta) > YEAR(desde) THEN
    BEGIN
    SET ano=(YEAR(hasta) – YEAR(desde));
    SET rndate = DATE_ADD(desde, INTERVAL ano YEAR);
    IF rndate>hasta THEN
    BEGIN
    SET ano = YEAR(DATE_ADD(hasta, INTERVAL -1 YEAR)) – YEAR(desde);
    SET rndate = ADDDATE(desde, INTERVAL ano YEAR);
    END;
    END IF;
    END;
    END IF;
    WHILE rndate <= hasta DO
    SET rndate = DATE_ADD(rndate, INTERVAL 1 MONTH);
    IF rndate<= hasta THEN
    BEGIN
    SET mes=mes+1;
    END;
    END IF;
    END WHILE;
    SET rndate=DATE_ADD(rndate, INTERVAL -1 MONTH);
    WHILE rndate< hasta DO
    SET rndate=DATE_ADD(rndate, INTERVAL 1 DAY);
    SET dia=dia+1;
    END WHILE;
    IF LENGTH(CAST(ano AS CHAR))=1 THEN
    SET fecha= CONCAT(‘0’, CAST(ano AS CHAR) , ‘a-‘);
    ELSE
    SET fecha= CONCAT(CAST(ano AS CHAR), ‘a-‘);
    END IF;
    IF LENGTH(CAST(mes AS CHAR))=1 THEN
    SET fecha=CONCAT(fecha, ‘0’, CAST(mes AS CHAR), ‘m-‘);
    ELSE
    SET fecha=CONCAT(fecha, CAST(mes AS CHAR), ‘m-‘);
    END IF;
    IF LENGTH(CAST(dia AS CHAR))=1 THEN
    BEGIN
    IF CAST(dia AS CHAR) ‘0’ THEN
    SET dia=dia-1;
    END IF;
    SET fecha=CONCAT(fecha, ‘0’, CAST(dia AS CHAR), ‘d’);
    END;
    ELSE
    BEGIN
    IF CAST(dia AS CHAR)= ’10’ THEN
    SET dia=dia-1;
    SET fecha=CONCAT(fecha, ‘0’, CAST(dia AS CHAR), ‘d’);
    ELSE
    SET dia=dia-1;
    SET fecha=CONCAT(fecha, CAST(dia AS CHAR), ‘d’);
    END IF;
    END;
    END IF;
    RETURN fecha;
    END$$

    DELIMITER ;

  7. Yo no se por que pero los simbolos de mayor y menor que no figuran aca cuando hice el paste desde el query browser
    sin embaro en la seccion
    BEGIN
    IF CAST(dia AS CHAR) ‘0′ THEN
    SET dia=dia-1;

    deberia haber los simbolos de mayor y menor que (diferente) a loque me refiero es que se deberia preguntar si la cadena dia es diferente de ‘0’, espero no existan mas problemas

  8. gracias por tu aporte Taigrus!, buena función …!

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: