Calculation of the time difference between 2 dates in minutes

Question

I have a Timestamp time field in my MySQL database that maps to a DATE type in my bean. Now I need a query by which I can get all the records in the database for which the difference between the current timestamp and the record stored in the database is> 20 minutes.

How can i do this?

I want to:

 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?

Answer 1

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

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

Answer 2

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

Answer 3

Try the following:

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

NOTE. This should work if you are using the MySQL DateTime format. If you use a Unix timestamp (integer), then this will be even simpler:

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

The UNIX_TIMESTAMP () function returns the current unix timestamp.

Answer 4

I am using the code below for today and the database date.

TIMESTAMPDIFF(MINUTE,T.runTime,NOW()) > 20

Here we can use HOUR (for hours), DAY (for days) instead of MINUTE , if we need.