In my MySQL database, I have a time Timestamp field that is mapped to a DATE datatype in my bean. Now I need a query that will return all records in the database when the discrepancy between the current timestamp and the one in the database is greater than 20 minutes.
I’m not sure how I’m going to accomplish it.
What I’m looking for is:
SELECT * FROM MyTab T WHERE T.runTime - now > 20 minutes
Is there a MySQL function for this, or a SQL way to accomplish this?
Asked by Akshay
I think you could use TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) something like
select * from MyTab T where TIMESTAMPDIFF(MINUTE,T.runTime,NOW()) > 20
Answered by Nicola Peluchetti
ROUND(time_to_sec((TIMEDIFF(NOW(), "2015-06-10 20:15:00"))) / 60);
Answered by Mouloud
I am using below code for today and database date.
TIMESTAMPDIFF(MINUTE,T.runTime,NOW()) > 20
The first parameter, according to the documentation, can be any of the following:
MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR
Answered by Deepak Dholiyan
Try this one:
select * from MyTab T where date_add(T.runTime, INTERVAL 20 MINUTE) < NOW()
NOTE: If you’re using MySQL DateTime format, this should work. It would be considerably easier if you used Unix Timestamp (integer):
select * from MyTab T where UNIX_TIMESTAMP() - T.runTime > 20*60
The function UNIX TIMESTAMP() returns the current unix timestamp.
Answered by itsmeee
Post is based on https://stackoverflow.com/questions/7636599/calculating-time-difference-between-2-dates-in-minutes