计算两个日期之间的时差(以分钟为单位)

I have a field of time Timestamp in my MySQL database which is mapped to a DATE datatype in my bean. Now I want a query by which I can fetch all records in the database for which the difference between the current timestamp and the one stored in the database is > 20 minutes.

我的MySQL数据库中有一个时间戳字段，它映射到bean中的日期数据类型。现在我想要一个查询，通过这个查询，我可以在数据库中获取当前时间戳和存储在数据库中的数据之间的差值为> 20分钟的所有记录。

How can I do it?

我怎么做呢?

What i want is:

我想要的是:

SELECT * FROM MyTab T WHERE T.runTime - now > 20 minutes

Are there any MySQL functions for this, or any way to do this in SQL?

这里有MySQL函数吗，或者用SQL来做这个?

3 个解决方案

#1

I think you could use TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) something like

我认为您可以使用TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)。

select * from MyTab T where
TIMESTAMPDIFF(MINUTE,T.runTime,NOW()) > 20

#2

ROUND(time_to_sec((TIMEDIFF(NOW(), "2015-06-10 20:15:00"))) / 60);

#3

Try this one:

试试这个:

select * from MyTab T where date_add(T.runTime, INTERVAL 20 MINUTE) < NOW()

NOTE: this should work if you're using MySQL DateTime format. If you're using Unix Timestamp (integer), then it would be even easier:

注意:如果您使用的是MySQL DateTime格式，那么这应该会起作用。如果您使用的是Unix时间戳(整数)，则更容易:

select * from MyTab T where UNIX_TIMESTAMP() - T.runTime > 20*60

UNIX_TIMESTAMP() function returns you current unix timestamp.

函数的作用是:返回当前unix时间戳。

#1