[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

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
0
BrianGEFF719
Asked:
BrianGEFF719
  • 10
  • 4
  • 4
1 Solution
 
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
 
star_trekCommented:
try
delete yourtable where timeInserted < DATE_SUB(NOW(),INTERVAL -1 MINUTE);
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
UPDATE yourtable set lastupdate = NOW() where ...
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 10
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now