Use PHP to truncate the age of a MySQL entry to the nearest unit of time

Question

I have a table with a datetime field, and I want to pull that time stamp and use PHP to convert it to the nearest largest unit of time. For example, if the entry was made 2 minutes and 36 seconds ago, I want to echo 2 minutes in PHP. If it was 3 hours and 5 minutes ago, I want it to say 3 hours. If it was 6 days and 4 hours and 40 minutes ago, it should say 6 days. You get my drift. And if it's under a minute, just the number of seconds.

I am not familiar at all about any of PHP's date or time functions, so please don't assume I already know anything. Thanks.

Answer 1

If your field is date or datetime use

  SELECT UNIX_TIMESTAMP(field) FROM...

if your field is stored as an int (seconds since 1970) just use

  SELECT field FROM...

Get that integer value from the database into a PHP variable $secs, this is the number of seconds since 1970. We assume the date is in the past, then

  $diff = time() - $secs;
  if ($diff < 60) echo $diff . ' seconds';
  else {
     $diff = intval($diff / 60);
     if ($diff < 60) echo $diff . ' minutes';
     else {
        $diff = intval($diff / 60);
        if ($diff < 24) echo $diff . ' hours';
        else {
          $diff = intval($diff / 24);
          echo $diff . ' days';
        }
     }
  }

Answer 2

Here's another function for you:

function relativeTime($timestamp, $format = "Y-m-d")
{
    $difference = time() - $timestamp;
    if ($difference >= 604800) { // 7 days
        return date($format, $timestamp);
    }

    $periods = array("Second", "Minute", "Hour", "Day");
    $lengths = array("60","60","24","7","4.35","12","10");
    $ending = "Ago";
    for($j = 0; $difference >= $lengths[$j]; $j++)
        $difference /= $lengths[$j];
    $difference = round($difference);
    if($difference != 1) $periods[$j].= "s";
    $text = "$difference $periods[$j] $ending";
    return $text;
}

It returns values like "5 Minutes Ago", "3 Days Ago", and "34 Seconds Ago". However if the date is over 7 days old, it just returns the full date, i.e. "2010-04-19".