你可以尝试这个查询。计算间隔TIMESTAMPDIFF
and TIMESTAMPADD
功能。
SELECT
id,
time,
NOW(),
CONCAT (
TIMESTAMPDIFF(DAY,`time`, NOW()),' Days ',
TIMESTAMPDIFF(HOUR, TIMESTAMPADD(DAY, TIMESTAMPDIFF(DAY, `time`, NOW()), `time`), NOW()),' Hours ',
TIMESTAMPDIFF(MINUTE, TIMESTAMPADD(HOUR, TIMESTAMPDIFF(HOUR, `time`, NOW()), `time`), NOW()), ' Minutes ',
TIMESTAMPDIFF(SECOND, TIMESTAMPADD(MINUTE, TIMESTAMPDIFF(MINUTE, `time`, NOW()), `time`), NOW()), ' Seconds '
' ago' ) AS `ago`
FROM
`test`;
sqlfiddle:http://sqlfiddle.com/#!9/5e8085/2
NOTE
例如,您想要获取间隔SECOND
让我们一步步来了解一下:
1.TIMESTAMPDIFF(MINUTE, time, NOW())
获取间隔MINUTE
之间time
and NOW()
2.TIMESTAMPADD(MINUTE, TIMESTAMPDIFF(MINUTE, time, NOW()), time)
追加间隔MINUTE
to time
,让他们只有间隔时间second
.
3.计算秒间隔。