Problem with finding DATETIME + 5 minutes.

Discussion in 'MySQL' started by pictureboarduk, Apr 9, 2012.

0
  1. #1
    Hi,

    I have a field in my database (fieldName), of type DATETIME, which I populate using the NOW() function.

    For example: 2012-04-09 13:53:48

    I want to delete this record if 5 minutes have passed, ie 2012-04-09 13:58:48 or later.

    The query I am using is:
    
DELETE FROM myTable WHERE DATE_ADD(NOW(), INTERVAL 5 MINUTE) > fieldName
    Code (markup):
    But despite that more than 5 minutes have passed, the query still deletes zero rows.

    Can anyone help me with writing the query correctly?

    Many thanks for any help. :)
     
    pictureboarduk, Apr 9, 2012 IP
  2. iama_gamer

    iama_gamer Active Member

    Messages:
    404
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    60
    #2
    If you ask me that query should delete all rows if you populate them using NOW(). Check the data in the table and try using select queries to see where the issue is.

    There is a problem with your logic. You need to subtract 5 mins from the current time and check if that is greater than field name rather than add.
     
    iama_gamer, Apr 10, 2012 IP
  3. Arttu

    Arttu Member

    Messages:
    139
    Likes Received:
    2
    Best Answers:
    8
    Trophy Points:
    40
    #3
    Try this:
    DELETE FROM myTable WHERE (Unix_Timestamp(now())-300)<Unix_Timestamp(fieldName)
    Code (markup):
     
    Arttu, Apr 10, 2012 IP