PHP - Time - MySql

Displaying how long ago, rather then what time

[nfs2]

Is there a way in php to say how long ago a mysql table was updated?

In the table has the time inserted into it when it is updated, but instead of displaying it as "Friday, March 10, 2006" or something, i'd like it to display "2 hours ago" or "3 days ago" etc..

Can php do that?

[Grinler]

You can use the show table status to see when a table was last udpated:

[dev.mysql.com...]

You can compute it by comparing the current timestamp minus the amount of seconds for the time difference. For example 7200 is two hours. So if you get a number the current time - 2 hours, and the update_time is greater, then you are within the two hour window.

if($row['Update_time'] > (time() - 7200))
 echo "Less than 2 hours ago."; 

[nfs2]

if($row['Update_time'] > (time() - 7200)) {
echo "Less than 2 hours ago.";
}

Doesnt seem to work, but it seems limited anyways. Id like to reflect exactly how long ago the table was updated. If that code worked, id have to do an "if" for every minute/hour possible

Is there another way?

[Grinler]

None that I know of unfortunately.

[inveni0]

If you split both time values into Days, Hours, Minutes, Seconds, you can compare each value. Just subtract each from its match and you have the difference for each item. String 'em together (using an 'if' statement to rid any zero differences) and you win!

[nfs2]

Well if i could do that i woulndt need to split it up! The problem is comparing and subtracting to get the time difference.

For example, someone makes a blog post, and that goes into the mysql table with the time of the post.

So id need to find the difference between that time and the current time. I dont see how splitting it up makes it any easier, when i cant find the difference of the whole time to begin with

[inveni0]

I'll make it easier:

Format your time like, WeekDay:Month:Hour:Minute:Second:Year

Make sure each value is NUMERICAL. Name this $OldTime

split(":", $OldTime);

Now you have an array. Do this same thing with $TimeNow and subtract each array entry from its pair....then piece the results back together, converting back to a non-numerical format....see?

[SeanW]

Since you have the timestamp in the table, you can pull out the latest entry (ie with the table foo, column lastupdated)

SELECT UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(MAX(lastupdated)) FROM foo;

...
$last = fetch...

if ($last > 24*3600) {
$string = floor($last/(24*3600)) . " days ago";
} else if ($last > 3600) {
$string = floor($last/3600) . " hours ago";
) else...

Make sense?

[nfs2]

The date and time is put into the database with date("F j, Y, g:i a");

[nfs2]

...but yea i think i get that, i'll change my database to update with a unix timestamp.

[SeanW]

@nfs2: Make sure to index that timestamp column, mysql should then be able to get the MAX() value from the index rather than requiring a table scan.

Sean

[nfs2]

On second thought i have no idea how to use that query.

I changed my time column to update with a timestamp, but i didnt see any mention of unix

[edited by: nfs2 at 11:07 pm (utc) on April 2, 2006]

[nfs2]

"Since you have the timestamp in the table, you can pull out the latest entry"

...and since i need this for the 5 most recent entries, will it still work, or is it just for the latest entry?

Also, how do i work it into my existing query, since i need to use it there anyways?

argh this just seems impossible.. Rational thought would dictate that something so basic as to say "this so-and-so was updates x long ago" would be easy.. This has to be the most infuriatingly fustrating experiance i've ever had with php.. I just can't do it :(

</whining>

[nfs2]

Here's the query im using to get the last 5 updated blogs

$result7=mysql_query("SELECT DISTINCT blog_id FROM blog_entries ORDER BY entry_id DESC LIMIT 5");

Where would i fit you query into that?

[nfs2]

Heres my whole code that im working on now

  $result7=mysql_query("SELECT DISTINCT blog_id FROM blog_entries ORDER BY entry_id DESC LIMIT 5");
  $journal = array();
  while ($i = mysql_fetch_array($result7)) {
  $last = mysql_query("SELECT UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(MAX(entry_time)) FROM blog_entries") or die(mysql_error());
  if ($last > 24*3600) { 
  $string = floor($last/(24*3600)) . " days ago"; 
  } else if ($last > 3600) { 
  $string = floor($last/3600) . " hours ago"; 
  }
  $journal[] = '<td style="padding: 10px; border: 1px solid #0056a1; vertical-align: top; background: url(\'bluegrad.gif\') repeat-x;"><center><b><a href="http://www.example.com/profiles/'.$i['blog_id'].'">'.$i['blog_id'].'</a></b><br/><br/><a href="http://www.example.com/users/'.$i['blog_id'].'">'.$mainpic.'</a><div style="color: red; font-size: 9px;">'.$string.'</div></center></td><td width="5px"></td>';
  }
  echo $journal[0];
  echo $journal[1];
  echo $journal[2];
  echo $journal[3];
  echo $journal[4];

[jatar_k]

what is going wrong?

is it repeating the same last updated time for each entry?

[nfs2]

Well yes, but at also displays nothing right now. What i mean is with the following code

if ($last > 24*3600) {
$string = floor($last/(24*3600)) . " days ago";
} else if ($last > 3600) {
$string = floor($last/3600) . " hours ago";
}

It will display nothing for any of the last 5 entries, even though most of them were made many hours ago. But if i add an "else" at the end to test it like this

if ($last > 24*3600) {
$string = floor($last/(24*3600)) . " days ago";
} else if ($last > 3600) {
$string = floor($last/3600) . " hours ago";
} else {
$sring = "none of the above is true";
}

Or something, it will say that for each entry.. The other problem is i dont know how to get it down to minutes and seconds.

[barns101]

If I understand what you are trying to do, one way would be like this:

1) Subtract the timestamp (time of post) from the current time to give you the number of seconds that have elapsed since the post was made. Call this $elapsed_time

2) Divide $elapsed_time by the number of seconds in a month / week / day and if the floor() result is more than 1, display a message:

// Lets say that the post is 2h 5m old
$elapsed_time = 7500;
// There are 604800 seconds in a week
$1_week = 604800;
$weeks = floor($elapsed_time / $1_week);
if($weeks ==1)
{
echo 'One week';
}
elseif ($weeks > 1)
{
echo "$weeks weeks";
}
// If $week = 0 then nothing is printed.

Then do the same for days and minutes.

I hope that is what you were trying to achieve and I have not completely misinterpreted ;)

[nfs2]

Thank you! I now have the following working code

$now = time();
$then = $i['entry_time'];
$elapsed_time = $now - $then;

if ($elapsed_time > 604799) {

$one_week = '604800';
$weeks = floor($elapsed_time / $one_week);
if($weeks == 1)
{
$time = 'One week';
}
elseif ($weeks > 1)
{
$time = "$weeks weeks";
}

} else if ($elapsed_time > 86399){

$one_day = '86400';
$days = floor($elapsed_time / $one_day);
if($days == 1)
{
$time = 'One day';
}
elseif ($days > 1)
{
$time = "$days days";
}

} else if ($elapsed_time > 3599) {

$one_hour = '3600';
$hours = floor($elapsed_time / $one_hour);
if($hours == 1)
{
$time = 'One hour';
}
elseif ($hours > 1)
{
$time = "$hours hours";
}

} else if ($elapsed_time > 60){

$one_minute = '60';
$minutes = floor($elapsed_time / $one_minute);
if($minutes == 1)
{
$time = 'One minute';
}
elseif ($minutes > 1)
{
$time = "$minutes minutes";
}

} else {

$one_second = '1';
$seconds = floor($elapsed_time / $one_second);
if($seconds == 1)
{
$time = 'Right now';
}
elseif ($seconds > 1)
{
$time = "$seconds seconds";
}

Thanks again to everyone who helped me with this

[slade7]

or, once you get the elapsed time, you could do this:

function spitback_timestring($elapsed_time)
{
$num_weeks = floor($elapsed_time/604800);
$time_left = $elapsed_time - ($num_weeks*604800);

$num_days = floor($time_left/86400);
$time_left = $time_left - ($num_days*86400);

$num_hours = floor($time_left/3600);
$time_left = $time_left - ($num_hours*3600);

$num_minutes = floor($time_left/60);
$time_left = $time_left - ($num_minutes*60);

$num_seconds = $time_left;

// and then to print...

echo "$num_weeks weeks<br>
$num_days days<br>
$num_hours hours<br>
$num_minutes minutes<br>
$num_seconds seconds<br>";

}// end function

You could use the ifs to print or not print - depending - I suppose. I tried this briefly and it seems to work. I used something similar a while back.