CODEWITHSUNDEEP
phpmysql
glaston123
glaston123

Reputation: 25

Is there a way to dynamically delete a row from a table after a certain period of time?

I am trying to delete a row from my table after 12 hours have passed from the row being created.

I have a dateTime column in my table which records the time at which it was created, is there a way to delete a row after 12 hours dynamically? Another problem this creates is that the date may change during the 12 hours, which may render the timestamp of the row irrelevant.

I have tried to do some simple SQL Queries from examples I have found on similar questions although I do not know how to deal with the date-change aspect of the problem.

Example Queries:

DELETE FROM detail1 WHERE date < DATETIME('NOW', '-1 hours');

Here is my table:


ID   |Position|  dateApplied    
 1   |  1     |  2019-07-20 05:23:16
 2   |  2     |  2017-07-20 12:13:30

I would like to find a query to help me delete each row after 12 hours.

Upvotes: 0

Views: 314

Answers (1)

Amir MB
Amir MB

Reputation: 3418

You should create a script and call it periodically with Cron Jobs, In that script check all the rows and delete expired ones. It's easier if you save "expiry time" in the tabale:

$expireAfter = time() + (12 * 60 * 60); // time returns number of seconds since the Unix Epoch

Then in the periodic script, you can check if the row is expired or not:

$now = time();
$queryString = "DELETE FROM detail1 WHERE `expiry_time` < $now;";

With this approach, you don't need to worry about changing the date.

Upvotes: 1

Related Questions