SQL - older than 5 minutes

Databases
1

I have a record of a failed transaction in a database.
I want to retry the transaction in 5 minutes.
I have a mysql time column for the time of failure.

How do I form the SQL to select records where that time is more than 5 minutes ago?

2

What format is your time in?

3

It is a MySQL time. It looks like 11:05:58
Perhaps a date & time would be more secure for cases where things happen close to midnight…
but still… is there a SQL statement designed for this type of scenario?
I suppose if there isn’t then I can select the record store it in a DATE class and construct another SQL statement based on the date class + 5minutes… No… that’s too much work. :wink:

4

Try and let me know if this works there are alternatives

Select * FROM myTable WHERE myTimeColumn < (NOW() - INTERVAL 5 MINUTE)
5 
Select * From MyTable Where NOW() > (MyTimeColumn + INTERVAL 5 MINUTE);

For Clarity?

6

Brian, Just tested the following and it works fine:

SELECT * FROM myTable WHERE  myColumn < CURTIME() - INTERVAL 1 MINUTE

myColumn in my particular instance is a MySQL TimeStamp of the format 2014-01-08 21:52:13

Hope this helps

7

And if you find it clearer, I have tested your version and that also works. I find my way easier to read but that is just personal pref. Your way the syntax should be:

SELECT * FROM myTable WHERE CURTIME() > myColumn + INTERVAL 5 MINUTE
8

Many thanks. :slight_smile: