CODEWITHSUNDEEP
mysqlsqltimestamp
Akshay
Akshay

Reputation: 2487

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 any way to do this in SQL?

Upvotes: 115

Views: 211697

Answers (6)

Sergio Abreu
Sergio Abreu

Reputation: 2889

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 ;

Upvotes: 0

Deepak Dholiyan
Deepak Dholiyan

Reputation: 1912

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

Upvotes: 33

Nicola Peluchetti
Nicola Peluchetti

Reputation: 76870

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

Upvotes: 184

JoiBoy
JoiBoy

Reputation: 1

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

Upvotes: 0

Mouloud
Mouloud

Reputation: 3795

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

Upvotes: 28

itsmeee
itsmeee

Reputation: 1627

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.

Upvotes: 6

Related Questions