Get relative date from NOW () function

Question

I am trying to get the relative date and time from the MySQL NOW() function (e.g. 2 seconds ago, 3 days ago, December 31), but I have no idea how to do this! Any ideas? Thank you very much!

Answer 1

Use TIMESTAMPDIFF() . Example:

 SELECT TIMESTAMPDIFF(SECOND, `stamp_column`, NOW()) as `seconds` FROM `YourTable` 

Or use this saved function:

 CREATE FUNCTION `PassedSince`(`stamp` TIMESTAMP) RETURNS VARCHAR(100) DETERMINISTIC BEGIN DECLARE `result` VARCHAR(100) DEFAULT ''; DECLARE `seconds`, `minutes`, `hours`, `days` INT; SET `seconds` = TIMESTAMPDIFF(SECOND, `stamp`, NOW()); SET `days` = `seconds` DIV (24 * 60 * 60); SET `seconds` = `seconds` MOD (24 * 60 * 60); IF `days` > 0 THEN SET `result` = CONCAT(`result`, `days`, ' Days '); END IF; SET `hours` = `seconds` DIV (60 * 60); SET `seconds` = `seconds` MOD (60 * 60); IF `hours` > 0 THEN SET `result` = CONCAT(`result`, `hours`, ' Hours '); END IF; SET `minutes` = `seconds` DIV 60; SET `seconds` = `seconds` MOD 60; IF `minutes` > 0 THEN SET `result` = CONCAT(`result`, `minutes`, ' Minutes '); END IF; IF `seconds` > 0 THEN SET `result` = CONCAT(`result`, `seconds`, ' Seconds '); END IF; RETURN TRIM(`result`); END 

For request:

 SELECT `PassedSince`('2013-06-19 08:00') as `result` UNION ALL SELECT `PassedSince`('2013-01-01 00:00') 

Shows:

 result -------------------------------------- 1 Hours 20 Minutes 55 Seconds 169 Days 9 Hours 20 Minutes 55 Seconds 

Answer 2

You must specify the time, or in your case seconds, in advance.

Let me illustrate what I mean with a simple example based on a PDO-Mysql query

 $time = '0:01:00'; // one minute $stmt = $conn->prepare("SELECT chat_user FROM chatters WHERE TIMEDIFF(NOW(), login_time) < TIME (?) ); 

In the example above, if you use a query, you will actually look for a user whose login time is less than $time or 01:00:00