CODEWITHSUNDEEP
mysqlsqldatetimesql-update
Bablod
Bablod

Reputation: 101

Update SQL if row's time is 5 minutes or longer than now

I want to update SQL in which row's time is 5 minutes ago (or longer than 5 minutes):

UPDATE mytable SET status='EXPIRED' 
WHERE (a column's time is 5 minutes or longer before now)

I tried to use DATE_ADD(NOW(), INTERVAL 5 MINUTE) but I had no luck!

Upvotes: 0

Views: 2976

Answers (3)

Mureinik
Mureinik

Reputation: 311373

You can compare now() with the row's date column plus 5 minutes:

UPDATE mytable 
SET    status = 'EXPIRED' 
WHERE  DATE_ADD(date_col, INTERVAL 5 MINUTE) <= NOW()

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269843

A typical way would use date_sub():

UPDATE mytable
    SET status = 'EXPIRED'
    WHERE <columntime> < date_sub(now(), interval 5 minute);

You can also use date_add() with a negative number of units. Also, you should put the function call on now() rather than on the column, so MySQL can take advantage of an index on the column, if appropriate.

Upvotes: 1

Rahul
Rahul

Reputation: 77876

Not sure but considering that your time column is of DATETIME you can use BETWEEN operator like below

UPDATE mytable SET status='EXPIRED' 
WHERE your_time_column BETWEEN DATE_ADD(NOW(), INTERVAL -5 MINUTE) AND DATE_ADD(NOW(), INTERVAL 5 MINUTE)

Upvotes: 1

Related Questions