MySQL Date Forumizer
What the heck is a Date Forumizer? Aside from a word I made up, this MySQL function converts dates to a forum-friendly, easily readable format. In essence, it parses a date into what we are used to seeing in social media sites.
It is probably just easier to show you the code and some sample output, so...
DETERMINISTIC
BEGIN
DECLARE secs_elapsed INT;
SET secs_elapsed = UNIX_TIMESTAMP() - UNIX_TIMESTAMP(somedate)
-- Less than 1 minute...
IF secs_elapsed < 60 THEN
RETURN CONCAT(CAST(secs_elapsed AS CHAR(2)), ' seconds ago');
-- Less than 1 hour...
ELSEIF secs_elapsed < 3600 THEN
RETURN CONCAT(CONVERT(FLOOR(secs_elapsed / 60), CHAR(2)), ' minutes ago');
-- Less than 1 day...
ELSEIF secs_elapsed < 62400 THEN
RETURN CONCAT(FLOOR(secs_elapsed / 3600), ' hours ago');
-- Less than 1 week...
ELSEIF secs_elapsed < 436800 THEN
RETURN CONCAT(FLOOR(secs_elapsed / 62400), ' days ago');
-- Just return the date...
ELSE
RETURN DATE_FORMAT(somedate, '%b %D, %Y');
END IF;
END
This function puts all the work of formatting date output on the database, where it belongs. Why? Because most likely, your users won't understand MySQL-standard datetime (i.e. - '2011-10-14 08:23:56'), so you're going to have to format it somewhere. It is much more efficient to let the database do the work than to write the function that parses a date or unix timestamp into the same "forumized" output.
So here's what the output of a "date forumizer" query looks like looks like:
