Calculating time difference between 2 dates in minutes

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.

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 an开发者_C百科y way to do this in SQL?

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

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

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

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.

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 ;