Purge old entries from MySQL table

I need to delete entries from a table which are over 24 hours old

i just cant get my head around all the different mysql date/time functions

the date field is type; timestamp
with CURRENT_TIMESTAMP as default
rlambellAsked:
Who is Participating?
 
elimesikaConnect With a Mentor Commented:
HI

Sorry, my mistake

here it is


delete from search_logs where TIMESTAMPADD(HOUR,24,search_date) < NOW()

Open in new window

0
 
elimesikaCommented:
Assuming your table is T amd timestamp field is t

delete from T where t < "%s"' % int(time.time() - 24)
0
 
rlambellAuthor Commented:
i cant get that to work, simple syntax error? tried variations on quotes around %s but didnt work

running this:
delete from search_logs where search_date < "%s"' % int(time.time() - 24)

returned this:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' % int(time.time() - 24)' at line 1

im using MySQL 5.0.45-community
0
 
philipjonathanConnect With a Mentor Commented:
Alternatively:
delete from search_logs where search_date + INTERVAL 24 HOUR < NOW()

The benefit of using this statement is you can add an index on the column search_date to speed up your query.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.