Time Difference

Hello, I need code to search through a database that will compare a row called 'timeInserted' to the current time if the difference between the two is more than 1min, I need to remove the record from the database.

Please provide code to do so.

Brian
LVL 19
BrianGEFF719Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
UPDATE yourtable set lastupdate = NOW() where ...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
delete yourtable
where timeInserted < DATE_SUB(CURDATE(),INTERVAL -1 MINUTE);
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
or better, to check against the time :
delete yourtable
where timeInserted < DATE_SUB(CURTIME(),INTERVAL -1 MINUTE);
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
star_trekCommented:
try
delete yourtable where timeInserted < DATE_SUB(NOW(),INTERVAL -1 MINUTE);
0
 
BrianGEFF719Author Commented:
angelIII, is this a SQL statment?

brian
0
 
BrianGEFF719Author Commented:
can I add WHERE statments afterwards?
0
 
star_trekCommented:
in php
<?
mysql_connect(HOST,USERNAME,PASSWORD);
@mysql_select_db(DATABASE) or die( "Unable to select database");

$sql = "Delete <table-name> where `timeInserted` < DATE_SUB(NOW(),INTERVAL -1 MINUTE)";
$result=mysql_query($sql);
if($result) {
    //deletion successful
} else {
   //unsuccessful
}
mysql_close();
?>
0
 
BrianGEFF719Author Commented:
Does not work.


Insert like this ->
     $theTime = date("M j Y G:i:s");
     $varSQL = "INSERT INTO `tech` ( `techName` , `techIP`, `sessionID`, `port`, `lastUpdate` ) VALUES ('$name', '$techIP', '$ses', '$port', '$theTime')";
 

Check for old line this->

 $sql = "Delete `tech` where `lastUpdate` < DATE_SUB(NOW(),INTERVAL -3 MINUTE)";
 $result2 = mysql_query($sql, $dbh);
0
 
star_trekCommented:
Try this
$sql = "Delete `tech` where DATE_FORMAT(`lastUpdate`,GET_FORMAT(DATETIME,'ISO')) < DATE_SUB(NOW(),INTERVAL -3 MINUTE)";
0
 
BrianGEFF719Author Commented:
still doesnt work
0
 
BrianGEFF719Author Commented:
It does nothing.
0
 
BrianGEFF719Author Commented:
lastUpdate is varchar(100)


Brian
0
 
BrianGEFF719Author Commented:
Is there a way I can do this via PHP to atleast beable to evaluate the output?

Brian
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>angelIII, is this a SQL statment?
yes

>Does not work.
this is because of : lastUpdate is varchar(100)
how the "hell" did you come to make a datetime value into a varchar column.
get that column changed to datetime, and the delete will work correctly
0
 
BrianGEFF719Author Commented:
>>this is because of : lastUpdate is varchar(100)
>>how the "hell" did you come to make a datetime value into a varchar column.
>>get that column changed to datetime, and the delete will work correctly


LOL...that post made my night...will fix asap.

0
 
BrianGEFF719Author Commented:
AngelIII: That fixed that problem.

However, how do I update a record without using a string time that I generated. For example, one of the col's in the row is IP and one is lastUpdate.

I want to update IP with a string and lastUpdate I want to be the current Time. As you saw above I was using a string with the php date() function to do this.


-Brian
0
 
star_trekCommented:
Brian, what delete query you used?
0
 
BrianGEFF719Author Commented:
Once it was changed to a datetime field, angelIII's first post worked just fine.


Brian
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.