BrianGEFF719
asked on
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
Please provide code to do so.
Brian
or better, to check against the time :
delete yourtable
where timeInserted < DATE_SUB(CURTIME(),INTERVA L -1 MINUTE);
delete yourtable
where timeInserted < DATE_SUB(CURTIME(),INTERVA
try
delete yourtable where timeInserted < DATE_SUB(NOW(),INTERVAL -1 MINUTE);
delete yourtable where timeInserted < DATE_SUB(NOW(),INTERVAL -1 MINUTE);
ASKER
angelIII, is this a SQL statment?
brian
brian
ASKER
can I add WHERE statments afterwards?
in php
<?
mysql_connect(HOST,USERNAM E,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();
?>
<?
mysql_connect(HOST,USERNAM
@mysql_select_db(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();
?>
ASKER
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);
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);
Try this
$sql = "Delete `tech` where DATE_FORMAT(`lastUpdate`,G ET_FORMAT( DATETIME,' ISO')) < DATE_SUB(NOW(),INTERVAL -3 MINUTE)";
$sql = "Delete `tech` where DATE_FORMAT(`lastUpdate`,G
ASKER
still doesnt work
ASKER
It does nothing.
ASKER
lastUpdate is varchar(100)
Brian
Brian
ASKER
Is there a way I can do this via PHP to atleast beable to evaluate the output?
Brian
Brian
>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
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
ASKER
>>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.
>>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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Brian, what delete query you used?
ASKER
Once it was changed to a datetime field, angelIII's first post worked just fine.
Brian
Brian
where timeInserted < DATE_SUB(CURDATE(),INTERVA