Delete old data

[Pilly]

Hi,

Ive done a few searches but cant seem to find what im looking for.

I have a table and want to contain only the last 31 days of information in it.
Any ideas on what the sql part would be to delete everything but the last 31 days worth is??

[bubblenut]

Do you have a timestamp field in there?

[Pilly]

yeah the field is called Datestamp.

[bubblenut]

Right, assuming Datestamp is a unix timestamp (if it's not I'm sure you can find a function to convert it to such with whichever DBMS you're using) and TIME() gives the current unix timestamp.

DELETE FROM table WHERE Datestamp<TIME()-(86400*31)

86400 is the number of seconds in a day.

[Pilly]

ah, just realised its a date stamp, not date/time stamp.records are held like 2004-09-21