You have some data which many users edit often, and you want to return a field with some sort of relative time stamp on it.

For example, “27 minutes ago” for a record edited within the last half hour?

Use the following table for example. The field “modified_date” has the date in it the last time the record was modified.

SELECT record_id, record_title,
CASE
WHEN modified_date BETWEEN DATE_SUB(NOW(), INTERVAL 60 MINUTE) AND NOW() THEN CONCAT(MINUTE(TIMEDIFF(NOW(), modified_date)), ' minutes ago')
WHEN modified_date BETWEEN DATE_SUB(NOW(), INTERVAL 24 HOUR) AND NOW() THEN CONCAT(HOUR(TIMEDIFF(NOW(), modified_date)), ' hours ago')
ELSE DATE_FORMAT(modified_date, '%Y-%m-%d')
END AS relative_date
FROM example_table;

Jobs a good’un, relative_date will have your details in. My example was for fast moving data, so it only goes up to hours. And for most uses, this isn’t suitable, as you’d need to add days and weeks, for instance:

SELECT record_id, record_title,
CASE
WHEN modified_date BETWEEN DATE_SUB(NOW(), INTERVAL 60 MINUTE) AND NOW() THEN CONCAT(MINUTE(TIMEDIFF(NOW(), modified_date)), ' minute(s) ago')
WHEN modified_date BETWEEN DATE_SUB(NOW(), INTERVAL 24 HOUR) AND NOW() THEN CONCAT(HOUR(TIMEDIFF(NOW(), modified_date)), ' hour(s) ago')
WHEN modified_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 WEEK) AND NOW() THEN CONCAT(DATEDIFF(NOW(), modified_date), ' day(s) ago')
WHEN modified_date BETWEEN DATE_SUB(NOW(), INTERVAL 6 WEEK) AND NOW() THEN CONCAT(ROUND(DATEDIFF(NOW(), modified_date)/7), ' week(s) ago')
ELSE CONCAT(ROUND(DATEDIFF(NOW(), modified_date)/30), ' month(s) ago')
END AS relative_date
FROM example_table;