Delete all rows with timestamp older than x days

Question

I want to delete all the rows with timestamp older than 180 days from a specific table in my database.

I've tried the this:

DELETE FROM on_search WHERE search_date < DATE_SUB(NOW(), INTERVAL 180 DAY);

But that deleted all the rows and not only the rows older than 6 months.

I have a column in on_search table called search_date and contains the time when that row was created.

search_id   search_term    search_date 
660779      car games      1390052553

search_id search_term search_date 660779 car games 1390052553 — Khaleal, Jan 18 '14 at 16:07

score 91 · Accepted Answer · edited Jan 18 '14 at 16:22

91

DELETE FROM on_search 
WHERE search_date < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 180 DAY))

edited Jan 18 '14 at 16:22

juergen d

answered Jan 18 '14 at 16:13

Devesh

the column search_date is of type int? – Devesh Jan 18 '14 at 16:15
1

That did the trick! Thanks! I am sorry for asking so many questions, but is there any way to re-assign search_id with new ids starting from 1? (Deleting part of the data left the table with ids not starting from 1) – Khaleal Jan 18 '14 at 16:20
You can change the auto-increment starting number. But be careful; you might seriously foul up your table's keying. http://stackoverflow.com/questions/970597/change-auto-increment-starting-number – O. Jones Jan 18 '14 at 16:38
Is there a way we can run this command on all tables in the database? – Farveaz Jun 04 '18 at 10:43
@Farveaz it's not a command, it's a query and I suggest you create a question for that in Stackoverflow. – Dayron Gallardo Jun 26 '18 at 14:03

score 68 · Answer 2 · answered Jan 18 '14 at 16:07

68

DELETE FROM on_search WHERE search_date < NOW() - INTERVAL N DAY

Replace N with your day count

answered Jan 18 '14 at 16:07

Ali MasudianPour

2

That deleted all the rows despite the fact that there are stamps that were created today. – Khaleal Jan 18 '14 at 16:10
@Khaleal You can replace NOW() with any date you want – Ali MasudianPour Jan 18 '14 at 16:12
7

This works fine with datetime columns. Not with timestamp as asked. – Dayron Gallardo Jun 26 '18 at 14:00

2 Answers2