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{);

DELETE FROM table_name WHERE timestamp < (NOW() - INTERVAL 20 MINUTE) — , Apr 05 '20 at 13:20

score 31 · Answer 1 · edited Oct 24 '14 at 18:11

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? :)

If you used a numeric PHP timestamp, you need this extra function `UNIX_TIMESTAMP(NOW() - INTERVAL 1 YEAR);` — PJ Brunet, Jun 11 '15 at 19:40

score 10 · Accepted Answer · answered Apr 17 '12 at 19:18

10

delete from events where timestamp < NOW()

should be enough.

answered Apr 17 '12 at 19:18

Łukasz Rysiak

2,898
1
22
19

Thanks for the quick reply but that is not working! Its deleting all the records even though the timestamp is not older than the current time/date – Arihant Apr 17 '12 at 19:22
by "older" you meant that timestamp is > than current time? then you have to turn the statement into timestamp > NOW() – Łukasz Rysiak Apr 17 '12 at 19:30

score 2 · Answer 3 · answered Apr 17 '12 at 19:19

2

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

or if it's a standard datetime

DELETE FROM events WHERE timestamp < NOW()

answered Apr 17 '12 at 19:19

Kris Robison

698
7
13

Thanks for the quick reply but that is not working! Its deleting all the records even though the timestamp is not older than the current time/date – Arihant Apr 17 '12 at 19:23
Turn the delete into a select timestamp. Can you show a couple of the timestamp values that shouldn't be in the result set? Also do a SELECT NOW(). – Kris Robison Apr 17 '12 at 19:27

Ranvijay Sachan · Answer 4 · 2015-06-15T11:43:22.340

1

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);
    }

edited Jun 15 '15 at 11:43

answered Apr 01 '14 at 20:05

Ranvijay Sachan

2,407
3
30
49

score -4 · Answer 5 · edited Oct 24 '14 at 18:11

-4

DELETE FROM table WHERE date < '2011-09-21 08:21:22';

edited Oct 24 '14 at 18:11

Unihedron

10,902
13
62
72

answered Jun 01 '13 at 13:13

Rajiv

7

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

5 Answers5