MySQL 以分钟为单位计算两个日期之间的时差
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7636599/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calculating time difference between 2 dates in minutes
提问by Akshay
I have a field of time Timestamp in my MySQL database which is mapped to a DATEdatatype 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 数据库中有一个时间时间戳字段，它映射到DATE我的 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 中以任何方式执行此操作？
回答by Nicola Peluchetti
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
回答by Mouloud
ROUND(time_to_sec((TIMEDIFF(NOW(), "2015-06-10 20:15:00"))) / 60);
回答by Deepak Dholiyan
I am using below code for today and database date.
我在今天和数据库日期使用下面的代码。
TIMESTAMPDIFF(MINUTE,T.runTime,NOW()) > 20
TIMESTAMPDIFF(MINUTE,T.runTime,NOW()) > 20
According to the documentation, the first argument can be any of the following:
根据文档，第一个参数可以是以下任何一个：
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
回答by itsmeee
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_TIMESTAMP() 函数返回您当前的 Unix 时间戳。
