CODEWITHSUNDEEP
phpmysqldatabase
AHSAN KHAN
AHSAN KHAN

Reputation: 436

how to subtract a time from a time in mysql in Where Clause

how to subtract time from already store time in database 

$v_id = mysqli_query($mysqli," SELECT V_ID FROM van WHERE (v_starttime -'$row4') BETWEEN -1030 AND 2040");

Upvotes: 2

Views: 143

Answers (2)

logsv
logsv

Reputation: 544

you should use TO_DAYS for number of days then subtract from date

 $v_id = mysqli_query($mysqli," SELECT V_ID FROM van WHERE (TO_DAYS(DATE(v_starttime)) -TO_DAYS(DATE('$row4'))) BETWEEN -1030 AND 2040");

where $row4 is also date.

  • know about TO_DAYS and extract date from datetime

  • You can use TO_SECONDS to do this and do division 60*60

     $v_id = mysqli_query($mysqli," SELECT V_ID FROM van WHERE ((TO_SECONDS(v_starttime) -TO_SECONDS('$row4'))/3600) BETWEEN -1030 AND 2040");

Upvotes: 1

AHSAN KHAN
AHSAN KHAN

Reputation: 436

i got a idea do it may be wrong way but give right result yup done it 

$van_id = array();


 $i=0;

while(empty($rows[$i]) === false){




  $v_id = mysqli_query($mysqli," SELECT V_ID ,v_starttime FROM van WHERE r_id = '$rows[$i]'");




      $f=0;
    while($rowb = $v_id->fetch_assoc()) {

      $ty= (strtotime($rowb['v_starttime']) - strtotime($row4))/60;

// here it is or how i do it !! 

      if($ty>-40 && $ty<40) {
           $van_id[$f]= $rowb['V_ID'];
           mysqli_query($mysqli,"UPDATE `emp` SET `V_ID` = '$van_id[$f]' WHERE `username` = 'bla'");
          }
          $f++;
    }


  $i++;

}

i simply extract time from database convert it then subtract already extracted time also convert it too then just divide whole with 60 to get mintues then store it $van_id array

Upvotes: 1

Related Questions