DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Date Difference In Two Dates

10.13.2009
| 1719 views |
  • submit to reddit
        // mysql> select mysql.TimeDiffUnits('2009-01-01', now());
// +------------------------------------------+
// | mysql.TimeDiffUnits('2009-01-01', now()) |
// +------------------------------------------+
// | 9 Months                                 | 




DELIMITER //

CREATE FUNCTION TimeDiffUnits (old DATETIME, new DATETIME) RETURNS CHAR(50) DETERMINISTIC NO SQL
BEGIN
DECLARE diff INTEGER;
SET diff = UNIX_TIMESTAMP(new) - UNIX_TIMESTAMP(old);

CASE
	
WHEN (diff < 3600) THEN
RETURN CONCAT(FLOOR(diff / 60) , ' Minutes');
	WHEN (diff < 86400) THEN
RETURN CONCAT(FLOOR(diff / 3600), ' Hours');
	WHEN (diff < 604800) THEN
RETURN CONCAT(FLOOR(diff / 86400), ' Days');
	WHEN (diff < 2592000) THEN
RETURN CONCAT(FLOOR(diff / 604800), ' Weeks');
	WHEN (diff < 31536000) THEN
RETURN CONCAT(FLOOR(diff / 2592000), ' Months');
	ELSE
RETURN CONCAT(FLOOR(diff / 31536000), ' Years');
END CASE;
END //

DELIMITER ;