使用 MySQL 时如何以分钟为单位找到记录的年龄? [英] How can I find the age of a record in minutes when using MySQL?

问题描述

我有一个历史记录表，其中包含一个名为 createdAt 的 datetime 字段.我正在尝试确定记录的年龄，但年龄列返回为 Null:

I have a history table with a datetime field named createdAt. I'm trying to determine the age of the record but the age column comes back as Null:

SELECT id, (MINUTE(TIMEDIFF(NOW(),IFNULL(history.createdAt,0)))) AS age
FROM history
WHERE history.type = @type

我尝试了 DATEDIFF() 而不是 TIMEDIFF() 并且我得到了 0 返回

I tried DATEDIFF() instead of TIMEDIFF() and I get a 0 back

知道为什么这个查询没有按预期运行吗?

Any idea why this query is not behaving as expected?

更新:

在检查数据库后，我发现 createdAt 列是一个 date 而不是 datetime 列.如果使用 datetime 列，代码会按预期工作.

After introspecting the database, I found that the createdAt column was a date and not a datetime column. The code works as expected if using a datetime column.

推荐答案

现在无法测试，但 API 说:

Not able to test right now, but the API says:

返回时间的分钟，在范围 0 到 59.

Returns the minute for time, in the range 0 to 59.

mysql> SELECT MINUTE('2008-02-03 10:05:03');
        -> 5

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_minute

——不是你想做的.对来自 TIMEDIFF 的结果尝试 TIME_TO_SEC()，并将其除以 60 以获得分钟.

-- not what you want to do. Try TIME_TO_SEC() on the result from TIMEDIFF, and divide it by 60 to get minutes.

这篇关于使用 MySQL 时如何以分钟为单位找到记录的年龄?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！