Getting exact time difference from MYSQL datetime field

Question

I want to do what StackOverflow is doing which is saying exactly how long it's been since the last post. There is a catch though, SO displays certain information based on how long the ago the last post was - for example, if the post was less than a day ago, they post how many hours ago the last post was; if the post was less than an hour ago they post how many minutes ago it was, etc.

I'm working with a MYSQL DateTime field in the following format:

2012-09-19 13:28:45

I want to compare the above to the time NOW and so I converted that time using PHP's strtotime function and tried comparing the two dates through a function I put together (below). Granted, this is probably the WORST possible way of doing this but after reading about PHP's Date and DateTime functions I'm starting to become very, very confused.

function get_date_format($strToTimeString){

        $minute = 60;
        $hour = $minutes * 60;
        $day = $hour * 24;
        $week = $day * 7;
        $month = $week * 4;
        $year = $month * 12;

        $timeNow = strtotime("now");

        $timeDiff = $timeNow - $strToTimeString;

        if($timeDiff > $minute){
            if($timeDiff > $hour){
                if($timeDiff > $day){
                    if($timeDiff > $week){
                        if($timeDiff > $month){
                            if($timeDiff > $year){
                                // Years ago
                            }
                            else{
                                // Months ago
                            }
                        }
                        else{
                            // Weeks ago
                        }
                    }
                    else{
                        // Days ago
                    }
                }
                else
                {
                    // Hours ago
                }
            }
            else{
                // Minutes ago
            }

        }
        else{
            // Seconds ago
        }


    }

Is there a better way to do this? As I mentioned above, I had no luck when trying to use DateTime->diff

I really appreciate any help.

Answer 1

After long searching, I got something close to what rottentomatoes used on their forum:

function realTimeSince($time){
    define(MINUTE, 60);
    define(HOUR, 60*60);
    define(DAY, 60*60*24);
    define(MONTH, 60*60*24*30);
    $delta = strtotime(gmdate("Y-m-d H:i:s", time())) - strtotime($time);

    if ($delta < 1 * MINUTE) {
        return $delta == 1 ? "one second ago" : $delta . " seconds ago";
    }
    if ($delta < 2 * MINUTE) {
        return "a minute ago";
    }
    if ($delta < 45 * MINUTE) {
        return floor($delta / MINUTE) . " minutes ago";
    }
    if ($delta < 90 * MINUTE) {
        return "an hour ago";
    }
    if ($delta < 24 * HOUR) {
        return floor($delta / HOUR) . " hours ago";
    }
    if ($delta < 48 * HOUR) {
        return "yesterday";
    }
    if ($delta < 30 * DAY) {
        return floor($delta / DAY) . " days ago";
    }
    if ($delta < 12 * MONTH) {
        $months = floor($delta / DAY / 30);
        return $months <= 1 ? "one month ago" : $months . " months ago";
    } else {
        $years = floor($delta / DAY / 365);
        return $years <= 1 ? "one year ago" : $years . " years ago";
    }
}

so you can use it like this:

realTimeSince('2012-11-12 00:09:54'); //which can be got from a MySQL TIMESTAMP field

modified a little based on http://www.ferdychristant.com/blog//archive/DOMM-7QEFK4

Answer 2

Use DateTime. Here's sample:

$now = new DateTime('now');
$posted = new DateTime($postDateFromDBHere);
$interval = $posted->diff($now);
var_dump($interval);
echo $interval->format('%y-%m-%d %h:%m:%s'); //You can do similar to format your output as you wish.

Answer 3

Use DateTime and DateTime:diff then check each value:

function returnInterval($date){
    $datetime1 = new DateTime($date);
    $datetime2 = new DateTime();
    $diff = $datetime1->diff($datetime2);
    $string = '';
    $pass = '';

    if($diff->y){
        $string .= ($diff->y == 1) ? $diff->y." year" : $diff->y." years";
        $pass = ', ';
    }
    if($diff->m){
        $string .= $pass;
        $string .= ($diff->m == 1) ? $diff->m." month" : $diff->m." months";
        $pass = ', ';
    }
    if($diff->d){
        $string .= $pass;
        $string .= ($diff->d == 1) ? $diff->d." day" : $diff->d." days";
        $pass = ', ';
    }
    if($diff->h){
        $string .= $pass;
        $string .= ($diff->h == 1) ? $diff->h." hour" : $diff->h." hours";
        $pass = ', ';
    }
    if($diff->i){
        $string .= $pass;
        $string .= ($diff->i == 1) ? $diff->i." minute" : $diff->i." minutes";
        $pass = ', ';
    }
    if($diff->s){
        $string .= $pass;
        $string .= ($diff->s == 1) ? $diff->s." second" : $diff->s." seconds";
    }
    $pos = strrpos($string, ',');
    $string = substr_replace($string, ' and ', $pos, 2);
    return $string;
}

echo returnInterval('2012-09-19 13:28:45');
// 8 days, 13 hours, 47 minutes and 44 seconds

Answer 4

Try the following function

 function time_ago($date)
        {
            //echo "ss";
            if (empty($date)) { 
                return "No date provided";
            }      
            $periods = array("sec", "min", "hr", "day", "week", "month", "year", "decade");
            $lengths = array("60","60","24","7","4.35","12","10");
            $now = time();
            $unix_date = strtotime($date);
            // check validity of date
            if (empty($unix_date)) {   
                return "Bad date";
            }
            // is it future date or past date
            if ($now >= $unix_date) {   
                $difference= $now - $unix_date;
                $tense = "ago";
            } else {
                $difference = $unix_date - $now;
                $tense = "from now";
            }
            for ($j = 0; $difference >= $lengths[$j] && $j < count($lengths)-1; $j++) {
                $difference /= $lengths[$j];
            }
            $difference = round($difference);

            if ($difference != 1 && $j != 0) {
                $periods[$j].= "s";
            }
            if($difference!=0)
            return "$difference $periods[$j] {$tense}";
            else
            return "a few seconds ago";
        }

or

function time_elapsed_since ($postedDateTime){

    $time = time() - $postedDateTime; // to get the time since that moment

         $tokens = array (
                      31536000 => 'year',
                      2592000 => 'month',
                      604800 => 'week',
                      86400 => 'day',
                      3600 => 'hour',
                      60 => 'minute',
                      1 => 'second'
                  );

                  foreach ($tokens as $unit => $text) {
                      if ($time < $unit) continue;
                      $numberOfUnits = floor($time / $unit);
                      return $numberOfUnits.' '.$text.(($numberOfUnits>1)?'s':'');
                  }

    }





time_elapsed_since($postedDateTime).' ago';