Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 713
  • Last Modified:

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
0
rlambell
Asked:
rlambell
  • 2
2 Solutions
 
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
 
elimesikaCommented:
HI

Sorry, my mistake

here it is


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

Open in new window

0
 
philipjonathanCommented:
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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now