delete records older than 1 hour

Posted on 2012-08-14
Medium Priority
Last Modified: 2012-08-19

When a user submits a form, I am trying to delete all previous records older than 1 hour. So right after my insert statement I am adding a delete statement.  My date field is named "added_on" and the date values look like 2012-07-31 21:45:03.  Using the below deletes everything including the current record, instead of the records older than 1 hour.

$sql = "INSERT INTO info_requests SET
email = '".addslashes($data['email'])."',
ip_address = '".$_SERVER['REMOTE_ADDR']."',
added_on = now() ";

$sql = "DELETE FROM info_requests WHERE added_on < (now() - interval 1 hour)";
Question by:web5dev7
LVL 11

Accepted Solution

Lara F earned 500 total points
ID: 38294565
try this

first write this test to  see what date is returned from dateadd
select now(), DATE_ADD(now(),INTERVAL -1 hour)

then try
select top 10 added_on, *   FROM info_requests
WHERE added_on < DATE_ADD(now(),INTERVAL -1 hour) order by added_on  desc

this will show if your assumption about added_on format is correct - it could be just date, without time.

Assisted Solution

et_me earned 500 total points
ID: 38294610
The NOW() function provides the current time in the local timezone of the server.  Please check the  local timezone of the server is your intended timezone.
LVL 14

Assisted Solution

by:Scott Madeira
Scott Madeira earned 500 total points
ID: 38297114
What happens when you do this:

$sql = "DELETE FROM info_requests WHERE added_on < Date_sub(now(), interval 1 hour)";
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 500 total points
ID: 38297194

I expect that there may be some confusion of timezones.  PHP and MySQL do not share the same time zone information.  To see this in action, create a test table with a column of the data type TIMESTAMP.  INSERT a row into the table.  SELECT the row and print it out with var_dump().  Then print out the PHP value of date('c') and compare them.

Author Closing Comment

ID: 38309730
Thanks all!  Yes there may well be something about timezone difference between my location and server?  

The data is being collected at a very low activity kiosk site so we decided it was better to schedule a Cron job to empty records twice a day - since it would likely be more dependable/frequent than the next user.  The data was only being used for temporary validation and so we do not want to store it beyond its usefullness.

The Cron is working as desired, but I'll still experiment with your suggestions to see if I can solve the mystery.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses
Course of the Month15 days, 13 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question