CODEWITHSUNDEEP
phpmysqlsqldate
Lion King
Lion King

Reputation: 33823

how to delete the expired data from database (depends on a period column)

I want to delete the data that expired.

I have two columns:
created_time : That contain a value indicate to time() function (ex: 1395836716).
period :That contain the period of the remain the row in database (ex: 3 or 7 or 15 or 30 per day).

I want to create a query to check on all rows in the table to know the rows that expired.
The problem is I don't know how to do that, but I tried.

$twentyFour = 60 * 60 * 24;
mysql_query("DELETE FROM content WHERE created_time + (period * $twentyFour ) > '" . time() . "' ");

But unfortunately, did not work as I expected.

Edit:

This is a result of row

enter image description here

Edit 2

I did it, by using php conditions:

$twentyFour = 60 * 60 * 24;
$query = mysql_query("SELECT * FROM content");
while ($data = mysql_fetch_assoc($query)) {
    if ($data['created_time'] + ($data['period'] * $twentyFour) < time()) {
        mysql_query("DELETE FROM content WHERE id = $data[id]");
    }
}

The previous code works fine as was I want.
the problem is the previous code will occurs more server load.
I want a query to doing as the previous code does without using conditions.

Upvotes: 0

Views: 1758

Answers (3)

Ryan Vincent
Ryan Vincent

Reputation: 4513

See edit2: of the question

Given that times are held as unix timestamps this is what is required:

 DELETE FROM content 
 WHERE (created_time + period * 60 * 60 * 24) <= NOW()

here is the: SQLFiddle

I had forgotten how much fun playing with dates and times in SQL wasn't ;-/ 

----------------------------------------

If using 'real' DateTime columns then this is the way to go:

Use: 

 DELETE FROM content 
 WHERE created_time < DATE_SUB(NOW(), INTERVAL `period` DAY)

or:

DELETE from content
WHERE NOW() > DATE_ADD(`created_time`, INTERVAL `period` DAY)

Dates and date differences are always difficult to get right. The issue is that you need to pick a reference point: 

1) NOW() against created_at + interval, 
2) NOW() against  `expiry_date
3) created_at + interval against NOW()
4) expiry_date against NOW().

that while you try and work it out, you tend to switch the reference point and it is very non-intuitive.

I have created an SQL Fiddle that demonstrates all the calculations. The 'created_at' date is always midnight to 'simplify' the checking. You can change the variable called 'is_now' and see the result of all the date differences and the boolean results. It is 'interesting', maybe.

TL;DR

SQLFiddle gives the correct answer.

Upvotes: 0

shrmn
shrmn

Reputation: 1533

May I suggest you edit your data structure and include a new column delete_time?

This delete_time should contain the UNIX timestamp of when your column should be deleted calculated from your given period.

Assuming your POST data of the period is numerical value indicating the number of days to save the data for, delete_time should be calculated by

$delete_time = time() + $_POST['period']*24*60*60;

This way, your pruning/deleting query can just be a one-liner:

mysql_query("DELETE FROM content WHERE delete_time < ".time());

That query deletes all rows whose delete_time has passed the current time().

Upvotes: 0

John Conde
John Conde

Reputation: 219924

DELETE FROM content 
WHERE created_time < DATE_ADD(NOW(), INTERVAL `period` DAY)

Upvotes: 2

Related Questions