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

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

我该怎么做？

我想要的是:

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

是否有任何 MySQL 函数来实现这一点，或者在 SQL 中有任何方法来实现这一点？

178658 次浏览
小开

If you have MySql version above 5.6 you could use TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) something like 

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

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:

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

UNIX_TIMESTAMP() function returns you current unix timestamp.

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

MySql version >=5.6

I am using below code for today and database date.

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
小开

You can try this:
SELECT * FROM MyTab T WHERE CURRENT_TIMESTAMP() > T.runTime + INTERVAL 20 MINUTE;

The CURRENT_TIMESTAMP() is a function and returns the current date and time. This function works From MySQL 4.0

小开

If you have MySql version prior than 5.6 you don't have TIMESTAMPDIFF. So,I wrote my own MySql function to do this. Accets %i or %m for minutes and %h for hours. You can extend it.

Example of usage:

 SELECT MYTSDIFF('2001-01-01 10:44:32', '2001-01-01 09:50:00', '%h')

Here goes the function. Enjoy:

 DROP FUNCTION IF EXISTS MYTSDIFF;


DELIMITER $$
CREATE FUNCTION `MYTSDIFF`( date1 timestamp, date2 timestamp, fmt varchar(20))
returns varchar(20) DETERMINISTIC


BEGIN


declare secs smallint(2);
declare mins smallint(2);
declare hours int;
declare total real default 0;
declare str_total varchar(20);


if date1 > DATE_ADD( date2, interval 30 day) then
return '999999.999'; /* OUT OF RANGE TIMEDIFF */
end if;


select cast( time_format( timediff(date1, date2), '%s') as signed) into secs;
select cast( time_format( timediff(date1, date2), '%i') as signed) into mins;
select cast( time_format( timediff(date1, date2), '%H') as signed) into hours;


set total = hours * 3600 + mins * 60 + secs;


set fmt = LOWER( fmt);


if fmt = '%m' or fmt = '%i' then
set total = total / 60;
elseif fmt = '%h' then
set total = total / 3600;
else
/* Do nothing, %s is the default: */
set total = total + 0;
end if;


select cast( total as char(20)) into str_total;


return str_total;


END$$
DELIMITER ;

提交答案