php now函数date,php – 从NOW()函数获取相对日期

使用

TIMESTAMPDIFF()功能.见例子：

SELECT

TIMESTAMPDIFF(SECOND, `stamp_column`, NOW()) as `seconds`

FROM

`YourTable`

或者使用这个存储的函数：

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

查询：

SELECT

`PassedSince`('2013-06-19 08:00') as `result`

UNION ALL

SELECT

`PassedSince`('2013-01-01 00:00')

显示：

result

--------------------------------------

1 Hours 20 Minutes 55 Seconds

169 Days 9 Hours 20 Minutes 55 Seconds