mysql+php to subtract two times and display the differrence

Question

hi!!

i've to display the differences between a column(reg_time) in mysql and the present time so that the final output (after some php to display in minutes or seconds or hours or days) be:

Registered : 8 hours ago.

is there any function to do that? thanks

Answer 1

//End date is current date
//$start_date, lets say sep 05 82 : 00:00:00

$seconds_dif = mktime(date("G"),date("i"),date("s"),date("m"),date("d"),date("Y")) - mktime('00','00','00','09','05','1982');

//Difference in seconds is $seconds_dif

//Now only math calculation to figure out months/years..

echo '<br />Years Difference   = '. floor($seconds_dif/365/60/60/24);
echo '<br />Months Difference  = '. floor($seconds_dif/60/60/24/7/4);
echo '<br />Weeks Difference   = '. floor($seconds_dif/60/60/24/7);
echo '<br />Days Difference    = '. floor($seconds_dif/60/60/24);
echo '<br />Hours Difference   = '. floor($seconds_dif/60/60);
echo '<br />Minutes Difference = '. floor($seconds_dif/60);

Found here: http://www.webhostingtalk.com/showthread.php?t=453668

You need to edit the 3rd line of code for your data format: mktime('00','00','00','09','05','1982')

Also you need to wrap this code into a function for easy using it:

/**
 * Return string with date time difference between two arguments
 * @param $start_date integer Start date in unix time (seconds from January 1 1970), you can use strtotime('24 November 2003 13:45:54'), for example
 * @param $end_date integer [optional] End date in unix time, by default it equals now
 * @return string
 */
function getDateTimeDifference($start_date, $end_date = time()) {
    $end_date = mktime(date("G", $end_date),date("i", $end_date),date("s", $end_date),date("m", $end_date),date("d", $end_date),date("Y", $end_date));
    $difference = $end_date - mktime(date("G", $start_date),date("i", $start_date),date("s", $start_date),date("m", $start_date),date("d", $start_date),date("Y", $start_date));
    $years = floor($seconds_dif/365/60/60/24);
    $months = floor($seconds_dif/60/60/24/7/4);
    $weeks = floor($seconds_dif/60/60/24/7);
    $days = floor($seconds_dif/60/60/24);
    $hours = floor($seconds_dif/60/60);
    $minutes = floor($seconds_dif/60);

    $ret = 'Difference: ';
    if (!empty($years))
        $ret .= $years . ' years, ';
    if (!empty($months))
        $ret .= $months . ' months, ';
    if (!empty($weeks))
        $ret .= $weeks . ' weeks, ';
    if (!empty($days))
        $ret .= $days . ' days, ';
    if (!empty($hours))
        $ret .= $hours . ' hours, ';
    if (!empty($minutes))
        $ret .= $minutes . 'minutes';

    return $ret;
}

Answer 2

Take a look at the MySQL functions timediff() (4.1.1+) and timestampdiff() (5.0+).
And the php method DateTime::diff() (5.3+)

e.g.

SELECT Now(), TIMESTAMPDIFF(HOUR, Now(), '2009-12-12 06:15:34')

just returned

"2009-12-12 12:34:00"; "-6"

on my machine. And

$dt = new DateTime('2008-03-18 06:15:34');
echo $dt->diff(new DateTime())->format('%y %d %h');

(currently) prints

1 25 6