time difference

xanderman

hi there,

i have a time that i will get from a mysql database (so it will be in mysql format, YYYY/MM/DD HH:MM:SS). what i would like to do is subtract this time from the current server time in order to get the difference between the two times, expressed as 'x days, x hours, x minutes and x seconds'. any pointers or tips on how to do this would be greatly appreciated!

cheers.

bpat1434

Use the mySQL functions already in there..... easy enough!!

or, you can convert to UNIX timestamp (via mySQL function) and then do the arithmatic yourself....
Date and Time Functions
The one's you'll want are:
DATE_SUB() or SUBDATE()
UNIX_TIMESTAMP()

If you do the arithmatic on your own, remember UNIX_TIMESTAMP returns a value of number of seconds, so you'd have to do
6060 = 1 hour
606024 = 1 day
6060247 = 1week
606024752 = 1 year

Notice how it builds off itself?

xanderman

thanks for the quick reply. are you able to post an example on how to achieve this through mysql - im a mysql newbie and i think i may be way over my head here.

would i do something like..
select unix_timestamp('time from db'); - then would it output the timediff in unix time? if so, how do i fill in the 'time from db' bit - would i first need to get the time from the db, set it as a variable and then query the db again?

bpat1434

well, if you want precise time, you'd have to use PHP:

<?php

/** A function to automatically compute the time difference **/
function get_difference($end = 0; $start = 0)
{
  // Define some usefull intervals (in seconds):
  $min = 60;
  $hour = $sec*60;
  $day = $hour*24;
  $week = $day*7;
  $year = $week*52;

  // Start computation:
  $diff = $end-$start; // Generate seconds difference
  if($diff==0)
  {
    // No difference, so start and end must be same!!
    return 'No Difference';
  }
  else
  {
    $days = floor($diff/$day); // Round down....
    $diff = $diff-($day*$days); // Update the time difference

$hours = floor($diff/$hour);
$diff = $diff-($hour*$hours);

$minutes = floor($diff/$min);
$diff = $diff-($min*$minutes);

$seconds = $diff;  // Remainder should be less than 60

// We're done computation, return the string:
return $days.' days '.$hours.' hours '.$minutes.' minutes '.$seconds.' seconds difference between '.date('m/d/Y H:i:s', $start).' and '.date('m/d/Y H:i:s');
  }
}

$diffs = array();
$query = "SELECT UNIX_TIMESTAMP(startDate) start, UNIX_TIMESTAMP(endDate) end FROM `table`";
$result = mysql_query($query);
while($row = mysql_fetch_array($result))
{
  $diffs[] = get_difference($row['end'], $row['start']);
}

foreach($diffs as $dif)
{
  echo $dif.'<br>';
}

?>

Something like that.... The function should work (although very limited)....

It shouldn't handle periods spanning midnight (i.e 10 pm - 4 am), but if it does, let me know....

xanderman

thanks a lot mate, i was able to alter your code into exactly what i needed. cheers!