Get relative date from the NOW() function

Question

I try to get the relative date and time from the MySQL NOW() function, (like 2 seconds ago, 3 days ago, on the 31st of december...) but I have no idea how! Any ideas? Thank you so much!

you want to look add date_add and date_subtract ` SELECT .., NOW() - INTERVAL 1 DAY as yeseterday ... etc — , Jun 19 '13 at 02:36
I have a field in MySQL that is TIMESTAMP. I want to obtain on my website the relative time based on the date shown in the TIMESTAMP field. — , Jun 19 '13 at 02:39

BlitZ · Accepted Answer · 2013-06-19T03:28:46.263

3

Use TIMESTAMPDIFF() function. See example:

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

Or use this stored 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 query:

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

edited Jun 19 '13 at 03:28

answered Jun 19 '13 at 02:40

BlitZ

12,038
3
49
68

It wont fit for 3 days ago, 1 hour ago. I'm afraid more if/else has to be done after fetching from mysql. – 蒋艾伦 Jun 19 '13 at 02:44
i don't like the back ticks ever where, but that's just me. – Jun 19 '13 at 02:45
@AaronJiang yes. Just give me some time `:)` I'm not an AI to compute it in nanoseconds. Stored functions is the way. – BlitZ Jun 19 '13 at 02:45
2

something like this http://www.hawkee.com/snippet/9368/ there are a few of theses about – Jun 19 '13 at 02:47
How do you get de TIMESTAMPDIFF? – Jun 19 '13 at 02:51
@fred8695 its a 'built-in' function – Jun 19 '13 at 02:57

score 0 · Answer 2 · answered Jun 19 '13 at 02:43

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

Let me illustrate what I mean, with 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 above example, if you use the query, you will actually be searching for a user whose login time, is less than $time or 01:00:00

Get relative date from the NOW() function

2 Answers2

Linked

Related