Calculating time difference between 2 dates in minutes

Question

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  gt  20 minutes   Are there any MySQL functions for this  or any way to do this in SQL

User · Answer

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

User · Answer

ROUND time to sec  TIMEDIFF NOW     2015-06-10 20 15 00       60

User · Answer

I think you could use TIMESTAMPDIFF unit datetime expr1 datetime expr2  something like   select   from MyTab T where TIMESTAMPDIFF MINUTE T runTime NOW     gt  20

User · Answer

Try this one   select   from MyTab T where date add T runTime  INTERVAL 20 MINUTE   lt  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  gt  20 60   UNIX TIMESTAMP   function returns you current unix timestamp

[mysql] Calculating time difference between 2 dates in minutes

Examples related to mysql

Examples related to sql

Examples related to timestamp