Calculating time difference between 2 dates in minutes

mysql sql timestamp
Akshay picture Akshay · Oct 3, 2011 · Viewed 133.4k times · Source

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?

Answer

Nicola Peluchetti picture Nicola Peluchetti · Oct 3, 2011

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

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

Related questions

How to select rows that have current day's timestamp?

I am trying to select only today's records from a database table. Currently I use SELECT * FROM `table` WHERE (`timestamp` > DATE_SUB(now(), INTERVAL 1 DAY)); But this takes results for the last 24 hours, and I need it to only …

Read More
CURRENT_TIMESTAMP in milliseconds

Is there any way to get milliseconds out of a timestamp in MySql or PostgreSql (or others just out of curiosity)? SELECT CURRENT_TIMESTAMP --> 2012-03-08 20:12:06.032572 Is there anything like this: SELECT CURRENT_MILLISEC --> 1331255526000 or the …

Read More
When is a timestamp (auto) updated?

If I have a column in a table of type TIMESTAMP and has as default: CURRENT_TIMESTAMP does this column get updated to the current timestamp if I update the value of any other column in the the same row? …

Read More