2 min ago and timestamps??

Hello,

I have column in database named as recente_visited, and it has type as timestamps. basically i am checking the user recent most time for a perticular page on my website.

so my recent visited time is stored something like this

visitor _id recent_visited
4 2009-05-13 10:37:55

now i want to show on page that this visitor visited 2 min ago, 30 min ago something like that. and i have to show 5 visitors on that page with there time. how can i achive that?

i have logic in mind

$visit = NOW() -recent_visited

but then again how can i apply it in my selection query?

Code:

"SELECT a.username,a.avatar,r.visitor_id,r.visiting_count,r.recent_visited FROM recent_visitor as r ,author as a WHERE (r.visitor_id = a.id) AND r.profile_owner = '$uid' ORDER BY r.recent_visited DESC LIMIT $start, $limit_value"

can anyone help me?

thanks in advance.

Select the timestamp as is, then format it in PHP:

PHP Code:

$seconds = time() - strtotime($row['recent_visited']); $minutes = round($seconds / 60); echo $minutes . " ago";

thannks for your reply Dan_Grossman,

Getting output something like this:-

-330 ago

Could you please tell me is this logic ll consider the same thing in case of

1 hour 20 min ago or 2 days ago

one more question, where to put this $minutes in my while loop?

PHP Code:

<? $sqlpage = $op->runsql("SELECT a.username,a.avatar,r.visitor_id,r.visiting_count,r.recent_visited FROM recent_visitor as r ,author as a WHERE (r.visitor_id = a.id) AND r.profile_owner = '$uid' ORDER BY r.recent_visited DESC LIMIT $start, $limit_value"); if(mysql_num_rows($sqlpage) > 0){ ?> <p class="msgText">Total <strong><?=$total_records;?></strong><?if($total_records >1){ ?> Recent Visitor's<?}else{?> Recent Visitor<?}}?></p> <? if(mysql_num_rows($sqlpage) > 0){ while($row1 = $op->select($sqlpage)){ $imagearr = explode(",",$row1['avatar']); $imagearr[0]= preg_replace("/\/avatar\//","/small/", $imagearr[0]); $seconds = time() - strtotime($row1['recent_visited']); $minutes = round($seconds / 60); echo $minutes . " ago"; ?> <a href='/profile/<?=$blog->spacereplace($row1['username']);?>/' title="<?=$row1['username']?>"><img src="<?=$retpath;?>/<?=$imagearr[0];?>" width="40" height="40" alt="<?=$row1['username']?>"/></a>(<?=$row1['visiting_count']?> visit)<? echo"\t"; $i++; } } ?>

PHP Code:

function time_since($original) { // array of time period chunks $chunks = array( array(60 * 60 * 24 * 365 , 'year'), array(60 * 60 * 24 * 30 , 'month'), array(60 * 60 * 24 * 7, 'week'), array(60 * 60 * 24 , 'day'), array(60 * 60 , 'hour'), array(60 , 'minute'), ); $today = time(); /* Current unix time */ $since = $today - $original; if($since > 604800) { $print = date("M jS", $original); if($since > 31536000) { $print .= ", " . date("Y", $original); } return $print; } // $j saves performing the count function each time around the loop for ($i = 0, $j = count($chunks); $i < $j; $i++) { $seconds = $chunks[$i][0]; $name = $chunks[$i][1]; // finding the biggest chunk (if the chunk fits, break) if (($count = floor($since / $seconds)) != 0) { // DEBUG print "\n"; break; } } $print = ($count == 1) ? '1 '.$name : "$count {$name}s"; return $print . " ago"; }

I got this function. but it showing me output like this

Jan 1st, 197039 years ago

could you please help me?

It's negative?

Is the time zone on the web server and the database server the same? You shouldn't get a negative unless the time in the database was AFTER the current time.

Quote:

Originally Posted by Dan Grossman

It's negative?

Is the time zone on the web server and the database server the same? You shouldn't get a negative unless the time in the database was AFTER the current time.

record which i have inserted today showing this negative value like

-330 ago

and for the others which i had inserted yesterday are showing like this:

850 ago sidharth(15 visit) 865 ago narendra(5 visit)

any suggestion?

I suggest you compare the current time at the database to the current time in your PHP code so that you can answer my question :)

Quote:

Is the time zone on the web server and the database server the same

how to check that?

2009-05-13 10:37:55

but one thing i can tell you here, 10.37 am is an Indian time. so now how can i check my web server time? (confused?) because still i am working on locally.i haven't tried this on my server yet.

so could you please tell me ?

PHP Code:

echo date('r');

For the database:

Code:

select NOW()

mikem
--------

Thanks mikem,

my server time is - Wed, 13 May 2009 06:50:31

my database time is - 2009-05-13 12:20:16

so now how can i change my server time with database time?

Hello,

i have solved my problem with the help of other forum. so just dumping my logic here, may it will be helpful for someone like me

first changed my query

Code MySQL:

"SELECT  a.username,a.avatar,r.visitor_id,r.visiting_count,TIMESTAMPDIFF(MINUTE, r.recent_visited, NOW()) AS MinsSinceLastVisit FROM recent_visitor as r ,author as a WHERE (r.visitor_id = a.id) AND r.profile_owner = '$uid' ORDER BY r.recent_visited DESC  LIMIT $start, $limit_value"

and then my php code is

Code PHP:

<? $minutes_dif = $row1['MinsSinceLastVisit'];
 
	   $Ymin = 60 * 24 * 365;
	   $Mmin = 60 * 24 * 30;
	   $Wmin = 60 * 24 * 7;
	   $Dmin = 60 * 24;
	   $Hmin = 60;
 
       $Smin =60
	   $Y = (int)($minutes_dif / $Ymin);
	   $minutes_dif = $minutes_dif % $Ymin;
 
	   $MON = (int)($minutes_dif / $Mmin);
	   $minutes_dif = $minutes_dif % $Mmin;
 
	   $W = (int)($minutes_dif / $Wmin);
	   $minutes_dif = $minutes_dif % $Wmin;
 
	   $D = (int)($minutes_dif / $Dmin);
	   $minutes_dif = $minutes_dif % $Dmin;
 
	   $H = (int)($minutes_dif / $Hmin);
	   $minutes_dif = $minutes_dif % $Hmin;
 
	   if($Y > 0 ){
	   echo "$Y year(s) ";
 
	   }else if($MON > 0){
	   echo "$MON month(s) ";
	   }
	   else if($W > 0){
	   echo "$W week(s) ";
	   }
	   else if($D > 0){
	   echo "$D day(s) ";
	   }
	   else if($H > 0){
 
	   echo "$H hour(s) ";
	   }
	   else if($minutes_dif > 0){
 
	   echo "$minutes_dif minute(s) ";
	   }
      echo "ago";  ?>

:)