Friday, November 9, 2012

MySQL Function to Calculate x time ago from a particular date

Couple of days ago i was searching for a function in MySQL that could calculate time difference of a particular time with current time and return string like "3 minutes ago" or "2 days ago" and etc. I came across many functions for PHP but no MySQL. So i picked a PHP function and converted it to MySQL function. I used the PHP script from
http://www.tom-elliott.net/php/php-seconds-minutes-hours-ago/ by Tom Elliot.

MySQL script to create this function is


DELIMITER $$

DROP FUNCTION IF EXISTS `fn_x_time_ago`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `fn_x_time_ago`(old_time DATETIME) RETURNS VARCHAR(100) CHARSET latin1
BEGIN
DECLARE the_result VARCHAR(100) DEFAULT '';
DECLARE time_difference INT(11);
SELECT UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(old_time) INTO time_difference;

IF (time_difference > (60*60*24))
THEN SELECT CONCAT(ROUND(time_difference/60/60/24)," days ago") INTO the_result;
ELSEIF (time_difference > (60*60))
THEN SELECT CONCAT(ROUND(time_difference/60/60)," hours ago") INTO the_result;
ELSEIF (time_difference > (60))
THEN SELECT CONCAT(ROUND(time_difference/60)," minutes ago") INTO the_result;
ELSEIF (time_difference > (0))
THEN SELECT CONCAT(time_difference," seconds ago") INTO the_result;
END IF;
RETURN the_result;
END$$

DELIMITER ;


Name of MySQL function is fn_x_time_ago. It takes a date as argument. So the query to call this function will become.

SELECT fn_x_time_ago('2012-11-10 10:15:00');

3 comments: