SQL: How can I delete a row after a time

Question

I have a process that it create a row in a SQL table with date and time.

I need that the valid period of this row is only half hour, so, If the process create a row at 04:00, I need that this row has been deleted automatically at 04:30.

How can I do this in SQL or in my PHP code?

possible duplicate of [How to delete mysql row after time passes?](http://stackoverflow.com/questions/9865393/how-to-delete-mysql-row-after-time-passes) — Mojtaba Rezaeian, Jun 10 '15 at 07:32

score 2 · Answer 1 · answered Dec 16 '13 at 15:56

General speaking if you have such temporal related data you need to save the date (of cause). If you just want to hide older rows for some jobs or something like that simply create a database view which hides the rows based on a where condition. (This is also helpful if you may need to proof that you have done something.)

If you don't care for that just add that datetime/timestamp field and create a cronjob which deleted the data for you with the inverse logic of the query above.

If you have problems with implementing that by your own add your database schema to your question and post a comment.

I agree with you, adding logics to views is more accurate and stable, and if deletes were necessary we can do it later with mysql commands (even without defining daily/hourly cronjobs) [+1]. — Mojtaba Rezaeian, Jun 10 '15 at 06:22

score 2 · Answer 2 · answered Dec 16 '13 at 15:57

2

Add a column date_added timestamp column to your table. This value is automatically set when you insert the row.

Then write a PHP script which executes this query DELETE FROM [mytable] WHERE UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(date_added) > 1800. Schedule a cron job to run this every minute or so.

answered Dec 16 '13 at 15:57

Andy Jones

6,205
4
31
47

I prefer to use this option if I don't filter outputs by view [+1]. – Mojtaba Rezaeian Jun 10 '15 at 06:24

score 1 · Answer 3 · answered Dec 16 '13 at 15:58

you can easly make this in a stocked procedur http://dev.mysql.com/doc/refman/5.0/fr/create-procedure.html or if you want make it in php (symfony2) you can create a croned command that you launch every x minutes (or secondes) to verify every process.

score 0 · Answer 4 · answered Dec 16 '13 at 15:55

I would create a PHP file which runs a delete statement on any records where the created date/time was greater than half an hour ago.

You can then run this on the cron however frequently you feel necessary.

For example, if entries could be created at any time, I would run the PHP script every minute or two.

If entries could be created only at half an hour intervals (E.g. 5.00, 5.30) then running the script at XX:01 and XX:31 would probably be sufficient.

The timing of running the script depends on at what times the database entries might be entered, and the required accuracy of deleting the row as close to half an hour after creation as possible.

Thanks, I thought in this, but the row can be created in any moment. When a user enter in one sectino, this row is created. But the period of the values in this row is only for half hour. Thanks! — user2794692, Dec 16 '13 at 16:06

score 0 · Answer 5 · answered Dec 16 '13 at 15:57

0

I would create a scheduled job on the database itself that will cleanup the records older than 30 minutes.

answered Dec 16 '13 at 15:57

Tom

2,180
7
30
48

score 0 · Answer 6 · answered Dec 16 '13 at 15:57

If you have a process that reads from this table, then use a clause that limits period based on this time.

You could run an additional process that marks the row as disabled for additional piece of mind, personally I wouldn't delete such data as you would lose a log of events.

score 0 · Answer 7 · answered Dec 16 '13 at 15:58

0

Use cron

from (console) SSH: crontab -e

Add Line:

0,30 * * * * php /path/to/script.php

in script.php write code to look rows with >= 30 min and delete them.

answered Dec 16 '13 at 15:58

Jeckerson

106
2
6

score 0 · Answer 8 · answered Dec 16 '13 at 16:00

php is not needed in this task, you can create a new mysql database event of type RECURRING with no end date, where you simply put

DELETE FROM `table` WHERE `expiryTime`-UNIX_TIMESTAMP() < 1

Comparison expression may vary due to different methods of dates saving. In this case the field expiryTime would be saved as a unix timestamp to the time of expiry.

SQL: How can I delete a row after a time

8 Answers8