MySQL Query to delete rows whose timestamp is older than current timestamp

Question

I am looking for a query that is able to delete all rows from a table in a database where timestamp is older than the current date/time or current timestamp.

Would really appreciate some help out here urgently!

Here's the query I am using but as I thought it ain't working:

delete from events where timestamp<CURRENT_TIMESTAMP{);

Answer 1

Um... This may seem silly, but every record in the table will be older than Now(), since Now() is calculated at the time that query is processed. If you you want to delete a record that's older than another record, then you don't want to use Now(), but the timestamp from the record you're comparing the rest to. Or, if you want to delete records that are older than a specific point in time, then you need to calculate the timestamp that you want to use to compare against. For example, to delete records older than 10 minutes, you could use this:

DELETE FROM events WHERE timestamp < (NOW() - INTERVAL 10 MINUTE)

Or, for deleting records that are over a day old:

DELETE FROM events WHERE timestamp < (NOW() - INTERVAL 1 DAY)

For specific points in time (e.g. Oct. 12th, 2012 at 4:15:00 PM GMT), there's a method to do that, but the syntax escapes me, right now. Where's my MySQL manual? :)

Answer 2

delete from events where timestamp < NOW()

should be enough.

Answer 3

DELETE FROM events WHERE timestamp < UNIX_TIMESTAMP(NOW())

or if it's a standard datetime

DELETE FROM events WHERE timestamp < NOW()

Answer 4

Hibernate (hql) Delete records older than 7 days

I am not sure, but you can Try this:

    String hqlQuery = "from PasswordHistory pwh "
          + "where pwh.created_date < datediff(curdate(), INTERVAL 7 DAY)";

            List<Long> userList = (List<Long>)find(hqlQuery);
    deleteAll(userList );// from baseDao

public void deleteAll(Collection list) {
        getHibernateTemplate().deleteAll(list);
    }