[SOLVED] Help with passed 20 minutes query

[EchoFool]

I have a time stamp in a query that i want to create which only updates rows where by the time stamp is 20 minutes or greater than from Now() in the passed... in other words 20 minutes or more has passed since that timestamp was created. But i don't know how to do it ?

 

This is what im aiming to do:

 

DELETE FROM users WHERE `TimeStamp` compared to NOW() is 20 minutes or more.")

 

[xtopolis]

Read here: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

[EchoFool]

I don't see a compare function in that list, i saw that list before posting.. is there no compare function set in mysql?

[corbin]

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-sub

[EchoFool]

So sumin like:

 

Field = timestamp field in database

 

WHERE (DAY_MINUTE,Field) > 20

 

??? right?

[xtopolis]

So sumin like:

 

Field = timestamp field in database

 

WHERE (DAY_MINUTE,Field) > 20

 

??? right?

That doesn't make sense.  If I inserted a row into your query at 2009-01-09 13:22:00 (Jan 9th, 2009 at 1:22pm) and you ran your script at 1:23pm .. it would delete my row even though 20 minutes hadn't passed.

 

You want to use the DATE_SUB() function which would look like this:

SELECT `TimeStamp` FROM users WHERE `TimeStamp` <= DATE_SUB(NOW(),  INTERVAL 20 MINUTE)

Test is with a select before actually trying to delete anything!  Make sure it returns the rows you intend for it to delete

 

The DATE_SUB() function means: Subtract 20 minutes from NOW, then select anything that is less than(before) or equal to that time.  Obviously if it's from before, it's older than 20 minutes.

 

Also, it is recommended to rename your column name "TimeStamp" because it is using a reserved word.

[EchoFool]

Thank you ! Works a treat