从NOW()函数获取相对日期 [英] Get relative date from the NOW() function
本文介绍了从NOW()函数获取相对日期的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我尝试从MySQL NOW()
函数获取相对日期和时间(例如2秒前,3天前,12月31日)。 )但我不知道怎么样有任何想法吗?非常感谢你!
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!
推荐答案
使用 TIMESTAMPDIFF()
功能。参见示例:
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
查询:
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
这篇关于从NOW()函数获取相对日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文