473,320 Members | 1,926 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Delete rows every 30 minutes

I have a table with a datetime field.
When I insert a row in this table, I use NOW() to insert the current time in the datetime field. Once the whole processed is finished, I update the inserted rows setting the datetime field to 0.

What I need to do is to automatically delete all rows that are more than 30 minutes old. Something like 'DELETE FROM table WHERE date != 0 and date > now_minus_30minutes' Of course, I need to know how to calculate now minus 30 minutes.

One more thing, I was going to use a cronjob to execute a php script that would perform this task every 30 minutes. Or should I use a stored procedure? I never worked with stored procedures, so I have no idea how they work or when should they be used (of course I don't expect you to explain everything to me, I'll look it up if you recommend stored procedures).

Thanks in advance.
Fede
May 31 '07 #1
3 10672
miller
1,089 Expert 1GB
Expand|Select|Wrap|Line Numbers
  1. mysql> SELECT NOW(), DATE_SUB(NOW(), INTERVAL 30 MINUTE);
  2. +---------------------+-------------------------------------+
  3. | NOW()               | DATE_SUB(NOW(), INTERVAL 30 MINUTE) |
  4. +---------------------+-------------------------------------+
  5. | 2007-05-31 13:29:43 | 2007-05-31 12:59:43                 |
  6. +---------------------+-------------------------------------+
  7. 1 row in set (0.00 sec)
  8.  
mysqldoc DATE_ADD DATE_SUB

- Miller
May 31 '07 #2
Expand|Select|Wrap|Line Numbers
  1. mysql> SELECT NOW(), DATE_SUB(NOW(), INTERVAL 30 MINUTE);
  2. +---------------------+-------------------------------------+
  3. | NOW()               | DATE_SUB(NOW(), INTERVAL 30 MINUTE) |
  4. +---------------------+-------------------------------------+
  5. | 2007-05-31 13:29:43 | 2007-05-31 12:59:43                 |
  6. +---------------------+-------------------------------------+
  7. 1 row in set (0.00 sec)
  8.  
mysqldoc DATE_ADD DATE_SUB

- Miller
Thank you!! I was sure it could be done in SQL!!
May 31 '07 #3
pbmods
5,821 Expert 4TB
One more thing, I was going to use a cronjob to execute a php script that would perform this task every 30 minutes. Or should I use a stored procedure? I never worked with stored procedures, so I have no idea how they work or when should they be used (of course I don't expect you to explain everything to me, I'll look it up if you recommend stored procedures).
Rather than do that, all you have to do is check for 'expired' entries whenever you read data from the table.

If you really really really needed to run a periodic script instead (and you are running MySQL v5.1), have a look at CREATE EVENT.
Jun 3 '07 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Ryan | last post by:
I have a table in my database on SQL Server which holds a file name that refers to a file that is stored on the server. I would like to create a trigger to delete this file from the server if the...
4
by: MAB71 | last post by:
I'm running an ISP database in SQL 6.5 which has a table 'calls'. When the new month starts I create a new table with the same fields and move the data of previous month into that table and delete...
16
by: robert | last post by:
been ruminating on the question (mostly in a 390/v7 context) of whether, and if so when, a row update becomes an insert/delete. i assume that there is a threshold on the number of columns of the...
5
by: jamie | last post by:
Hi, I have a table with 1 million rows that I need to delete. Is there a way to delete them quickly considering I get errors if I try to select them all and then hit delete? What I'm...
3
by: vcornjamb | last post by:
Hello, I am developing a web form that contains some buttons and a data grid which has as its last column link buttons that will delete the data associated with that row. Everything works fine,...
9
by: nnelson | last post by:
I have a base customer table of 2 million records. We're doing some testing and I added 33000 rows incorrectly. No biggie, we'll just delete them, right? Nope....9 hours later, process is still...
14
by: Michel Esber | last post by:
Linux RH 4.0 running DB2 V8 FP 11. I have a table with ~ 11M rows and running DELETE statements is really slow. Deleting 1k rows takes more than 3 minutes. If I run select statements on the same...
1
by: jan.marien | last post by:
we have a table with jobs and a table with job_history information. Users can define jobs and let them run every X minutes/hours , like a cronjob. The jobs table has the following trigger:...
3
by: Michel Esber | last post by:
Hello, Environment: DB2 LUW v8 FP15 / Linux I have a table with 50+ Million rows. The table structure is basically (ID - Timestamp). I have two main applications - one inserting rows, and the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.