Need Help: MYSQL Time Difference

[opdsimran]

Hi,

I have 2 fields in a table in_time and out_time..

format for in_time,out_time is yyyymdHis[20011201094949]

What i need is a select statement to get a time difference between in_time and out_time..

[Sphynx]

I don't know about a query, but I had a function I adapted if it would help. You could deal with the calculation on the PHP side.

$outtime  = "20011201093105";
$intime = "20011201094949";

checkout_time($outtime,$intime);

function checkout_time($outtime,$intime){
  $outyear = substr($outtime,0,4);
  $outmonth = substr($outtime,4,2);
  $outday = substr($outtime,6,2);
  $outhour = substr($outtime,8,2);
  $outminute = substr($outtime,10,2);
  $outsecond = substr($outtime,12,2);

  $inyear = substr($intime,0,4);
  $inmonth = substr($intime,4,2);
  $inday = substr($intime,6,2);
  $inhour = substr($intime,8,2);
  $inminute = substr($intime,10,2);
  $insecond = substr($intime,12,2);

  $totaltime = mktime($inhour,$inminute,$insecond,$inmonth,$inday,$inyear) - mktime($outhour,$outminute,$outsecond,$outmonth,$outday,$outyear);
  $activedays = $totaltime/86400;
  $activehours = ($totaltime%86400)/3600;
  $activeminutes = (($totaltime%86400)%3600)/60;
  $activeseconds = ((($totaltime%86400)%3600)%60);

  echo 'You had this file checked out for: '.floor($activedays).' days, '.floor($activehours).' hours, '.floor($activeminutes).' minutes, '.floor($activeseconds).' seconds';
}

EDIT: I don't know why it wrapped the mktime line like that. sorry.