Page 1 of 1

delete SQL data .. expiration date?

Posted: Mon Oct 22, 2007 11:09 am
by jramaro
Hi
i would like to delete SQL data after two hours.
I've not done that before , so im wondering how this works in the database.
I currently have TIMESTAMP as a database field.

Thank you

Posted: Mon Oct 22, 2007 1:13 pm
by Christopher

Code: Select all

$sql = "DELETE FROM mytable WHERE time<'$time_two_hours_ago'";
Check the data/time functions in the manual for calculating the time two hours ago -- or see the database dogs to do it in SQL.

Posted: Mon Oct 22, 2007 1:20 pm
by jramaro
That code snippet looks like a a command made from a webpage/ script

will that work for data already in a database?
because i want the data in a database to expire automatically after two hours.
Without having to make any query to it manually.


Thank you

Posted: Mon Oct 22, 2007 1:23 pm
by John Cartwright

Code: Select all

WHERE DATE_SUB(CURDATE(), INTERVAL 24 HOUR)) <= time;

Posted: Mon Oct 22, 2007 1:29 pm
by Christopher
jramaro wrote:That code snippet looks like a a command made from a webpage/ script

will that work for data already in a database?
because i want the data in a database to expire automatically after two hours.
Without having to make any query to it manually.
You posted this in PHP Code so I assumed that you wanted a PHP solution. What database are you using?

Posted: Mon Oct 22, 2007 1:48 pm
by jramaro
database type: MySQL database
I posted this in PHP code, because i didnt know if the command for expiration has to start with PHP on the insert SQL?

---

That snip by Jcart seems to look like what I'm tryin to find.

on DATE_SUB .. should this be a column name , or a command in SQL similar to TIMESTAMP?


Thank you

Posted: Mon Oct 22, 2007 4:15 pm
by gregwhitworth
Jcart = MySQL Dictionary