Stack Stack - 5 months ago 13
SQL Question

How can I delete all expired rows (before last year)?

I have a table like this:

// cookies
+---------+-------------------------+------------------+------------+
| id | email | cookie | date_time |
+---------+-------------------------+------------------+------------+
| int(11) | varchar(50) | varchar(128) | int(11) |
+---------+-------------------------+------------------+------------+
| 1 | jack_2009@gmail.com | ojer0f934mf2... | 1467204523 |
| 2 | peter.zm@yahoo.com | ko4398f43043... | 1467205521 |
| 3 | matrix_john23@gmail.com | 34fjkg3j438t... | 1467205601 |
| 4 | peter.zm@yahoo.com | 0243hfd348i4... | 1467206039 |
+---------+-------------------------+------------------+------------+


I'm trying to delete all rows which are expired. expired means all old rows. old rows means every row which is before last year.

DELETE FROM cookies WHERE date_time < unix_timestamp() - (60 * 60 * 24 * 365)


But my query seems not normal to me. Is there any better approach to do that?

Answer

You can use date_SUB interv 1 Year from now

 DELETE FROM cookies WHERE  date_time < from_unixtime(DATE_SUB(now(),INTERVAL 1 year))