delete data after X number of days?

[idtstudios2]

I have a table that logs certain data for a website and it has a field that holds the date the row was added in the format 11/25/2005.

I have been trying for an hour and I cannot seem to write an sql query that will delete the rows older than 14 days.

Any advice?

Thanks as always,
Andrew

 

[MattNeeley]

Generally for something like this instead of saving the time it was put in in XX/XX/XXXX format I just use the unix timestamp. The timestamp is completely numeric and so you would then just subtract the known constant (how long in seconds 2 weeks is) from the the current time and if the value in the database is less then that, delete it.

 

[panatella]

Either using unix time timestamp or put dead date to your DB.

for example, data was added in on 11/01/2005. Before insert it into DB, you calculate the date 15 days later (11/15/2005). Then insert 11/15/2005 into DB instead of 11/01/2005. That's easy to write a script to delete data based on the date in DB

 

[KarveR]

what database?
mysql ?


______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[idtstudios2]

Yes, it's mysql. Thanks for all the replies guys.

 

[KarveR]

convert your date to a standard mysql format (YYYY-MM-DD),

DELETE from yourtable where yourdate > date_sub(curdate(), interval 14 day);




______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.