Calculating time difference between 2 dates in minutes

MysqlSqlTimestamp

Mysql Problem Overview

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?

Mysql Solutions

Solution 1 - Mysql

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

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

Solution 2 - Mysql

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

Solution 3 - Mysql

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

Solution 4 - Mysql

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.

Solution 5 - Mysql

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

Categories
Recommended Mysql Solutions
Recommended Sql Solutions
Recommended Timestamp Solutions

Previous Solution:

Operator new initializes memory to zero

C++Memory Management

Next Solution:

JavaScript triple greater than

Javascript

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionAkshayView Question on Stackoverflow
Solution 1 - MysqlNicola PeluchettiView Answer on Stackoverflow
Solution 2 - MysqlMouloudView Answer on Stackoverflow
Solution 3 - MysqlDeepak DholiyanView Answer on Stackoverflow
Solution 4 - MysqlitsmeeeView Answer on Stackoverflow
Solution 5 - MysqlJoiBoyView Answer on Stackoverflow