Time Since Function

This function converts time into english time. For instance, 65 seconds will display ’1 minute’ and 190 seconds will display ’2 minutes’ etc. A usage example it displaying the time since a timestamp

SELECT timeconvert(TIME_TO_SEC(TIMEDIFF(NOW(), timestamp))) AS ‘sincetime’

Which will display the time in english since the field ‘timestamp’ as ‘sincetime’

Obviously you can do the math to speed things up a little bit but I thought I would leave it so you can see what’s going on here :)

Enjoy!

DROP FUNCTION IF EXISTS `timeconvert`;
DELIMITER $$

CREATE FUNCTION `timeconvert`(seconds INT)
RETURNS text CHARSET utf8
BEGIN

DECLARE displaytime VARCHAR(20);

IF seconds = 1 THEN SET displaytime = ’1 second’;
ELSEIF seconds < 60 THEN SET displaytime = (Concat(seconds,CONVERT(‘ seconds’ USING utf8)));
ELSEIF seconds < (60*2) THEN SET displaytime = ’1 minute’;
ELSEIF seconds < (60*60) THEN SET displaytime = (Concat(round(seconds/60),CONVERT(‘ minutes’ USING utf8)));
ELSEIF seconds < (60*60*2) THEN SET displaytime = ’1 hour’;
ELSEIF seconds < (60*60*24) THEN SET displaytime = (Concat(round(seconds/(60*60)),CONVERT(‘ hours’ USING utf8)));
ELSEIF seconds < (60*60*24*2) THEN SET displaytime = ’1 day’;
ELSEIF seconds < (60*60*24*30) THEN SET displaytime = (Concat(round(seconds/(60*60*24)),CONVERT(‘ days’ USING utf8)));
ELSEIF seconds < (60*60*24*30*2) THEN SET displaytime = ’1 month’;
ELSE SET displaytime = (Concat(round(seconds/(60*60*24*30)),CONVERT(‘ months’ USING utf8)));
END IF;

RETURN displaytime;

END$$

SELECT timeconvert(1) AS test

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

EnglishFrenchGermanItalianPortugueseRussianSpanish