Problem
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
Solution #1
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
Solution #2
ROUND(time_to_sec((TIMEDIFF(NOW(), "2015-06-10 20:15:00"))) / 60);
Answered by Mouloud
Solution #3
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
Solution #4
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