[SOLVED] 2 min ago and timestamps??

deepson2 · May 13, 2009

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?

"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.

kickstart · May 13, 2009

Hi

What do you mean by applying it to your selection query? Do you want the info brought back as something readable (ie, 1min, 5 mins, 10 mins, 30 mins, 1 hour, etc) or do you want to select people who fall into a band for when their last activity was?

All the best

Keith

deepson2 · May 13, 2009

Keith, thanks for your reply. ok i ll tell you what i am trying to do in brief.

i am building module where i am showing recent visitors,so i just want to show the additional that how much time is done since they left that page. and their total visits. for total visits i am have done that

my recent_visited column is storing the recent time like

2009-05-13 02:24:34

so my friend ask me to check your server time using php's date function so that is

Wed, 13 May 2009 08:51:16

so the main problem is how can i change my database time to server time or vice versa

i was trying with this

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

but its giving me o/p like

213 ago

329 ago.

here is myPHP 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++; }
         }

?>

so can you help me to achive this?

kickstart · May 13, 2009

Hi

I would be tempted to just use SQL to get the number of minutes since the last visit.

<?
$sqlpage    = $op->runsql("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");
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]);

     $minutes = $row1['MinsSinceLastVisit']);
     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++; }
         }

?>

All the best

Keith

deepson2 · May 13, 2009

Keith,

i have run your query. getting values like

MinsSinceLastVisit

1

160

3940

''

"

that means its showing like this

160 ago, 3940 ago

i want it like

20 min ago, 1 hour ago, 1 days ago somthing like this

please help me. :-[ ???

FuThAr · May 13, 2009

hi all,

kickstart is right, i only say that you can try also TIMEDIFF (x,y): it gives exactly hh:mm:ss elapsed.

so you can manipulate this output to say only "minutes ago" if is for example 00:xx:xx or "hours ago" if is something like xx:xx:xx, or days if you want

sorry for the "intrusion", bye

Alberto

deepson2 · May 13, 2009

Alberto,

Thnaks for your reply too. but first of all it should come somthing like this - 00:02:39 min

but it is coming 239, i am just tired :-X trying everything. please Kieth help me.

gffg4574fghsDSGDGKJYM · May 13, 2009

Add it after this line :

$minutes = $row1['MinsSinceLastVisit']);

$minutes_dif = $row1['MinsSinceLastVisit']);

$Ymin = 60 * 24 * 365; 
$Mmin = 60 * 24 * 30; 
$Wmin = 60 * 24 * 7; 
$Dmin = 60 * 24; 
$Hmin = 60; 
$Min = 1; 

$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; 

$MIN = (int)($minutes_dif / $Min); 
$minutes_dif = $minutes_dif % $Min; 

if($Y > 0 ) echo "$Y year(s) "; 
if($MON > 0) echo "$MON month(s) "; 
if($W > 0) echo "$W week(s) "; 
if($D > 0) echo "$D day(s) "; 
if($H > 0) echo "$H hour(s) "; 
if($MIN > 0) echo "$MIN minute(s) "; 
echo "ago";

Edit : fixed typo.

deepson2 · May 13, 2009

theonlydrayk, you rock man!

3 hour(s) 200 minute(s) ago

23 hour(s) 1434 minute(s) ago

23 hour(s) 1436 minute(s) ago

1 day(s) 24 hour(s) 1451 minute(s) ago

Got this output! i am so happy to see somthing like this. but only one more question on 2nd,3rd and 4th row can we modify minute as well as hour as we never say na 1434 min or 24 hour.

please tell me how can i get that?

kickstart · May 13, 2009

Hi

If you want to keep it in SQL, something like this can be used to give the time periods:-

SELECT 
case 
WHEN MinsAgo <= 1 THEN '1 Min Ago' 
WHEN MinsAgo < 60 THEN CONCAT(MinsAgo,' Mins Ago') 
WHEN MinsAgo < 120 THEN '1 Hour Ago'
WHEN MinsAgo < 1440 THEN CONCAT(CONVERT((MinsAgo/ 60),UNSIGNED),' Hours Ago')
WHEN MinsAgo < 2880 THEN '1 Day Ago'
ELSE CONCAT(CONVERT((MinsAgo/ 1440),UNSIGNED),' Days Ago')
END TimeDifference
FROM (SELECT TIMESTAMPDIFF(MINUTE,LastVisit,now()) MinsAgo
FROM TimeTest) Deriv1

All the best

Keith

gffg4574fghsDSGDGKJYM · May 13, 2009

Can you post the complete script ?

I made some small change but this should never show minutes over 60 :

$minutes_dif = $row1['MinsSinceLastVisit'];

$Ymin = 60 * 24 * 365; 
$Mmin = 60 * 24 * 30; 
$Wmin = 60 * 24 * 7; 
$Dmin = 60 * 24; 
$Hmin = 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) "; 
if($MON > 0) echo "$MON month(s) "; 
if($W > 0) echo "$W week(s) "; 
if($D > 0) echo "$D day(s) "; 
if($H > 0) echo "$H hour(s) "; 
if($minutes_dif > 0) echo "$minutes_dif minute(s) "; 
echo "ago";

Or as kickstart say you can do it in SQL too.

deepson2 · May 13, 2009

Kieth, your query is beyond my understanding. pretty new for it.

theonlydrayk,Yes now its working fine!

but my while loop is skipping my first record. from 2nd records its showing proper values. could you please have look and tell me to get all records.

here is my code:


if($blogdata->author != $to){

//echo $from;


$query              = $op->insert( "INSERT INTO recent_visitor (profile_owner, visitor_id, first_visited, recent_visited, visiting_count) VALUES
('".$blogdata->author."', '".$to."', NOW(), NOW(), 1)
  ON DUPLICATE KEY UPDATE `visiting_count`=`visiting_count`+1");
if($query > 0){
	 	 $err_msg_shout="Shout Posted";

		 }else{
	             $err_msg_shout="Error, Try Again";
              }


}


//echo "SELECT  a.username,r.visitor_id 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 ";


$result  = $op->runsql("SELECT count(*) as cnt FROM author a JOIN recent_visitor r ON a.id = r.visitor_id WHERE r.profile_owner= '$uid' ");


                                        $limit_value                   = 4;
		  	         $ts                            = mysql_fetch_array($result);
		  	         $total_records                 = $ts['cnt'];
		  	         $top                           = $total_records / $limit_value;
		  	         $total_pages                   = ceil($top);
		  	         $start = $_REQUEST['start'] == "" ? 0 : $_REQUEST['start'];
		  	         $imagarr=array();
		            $i=0;

$sqlpage    = $op->runsql("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");
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]);



  ?>
  <a href='/profile/<?=$blog->spacereplace($row1['username']);?>/' title="<?=$row1['username']?>"><img src="<?=$retpath;?>/<?=$imagearr[0];?>" width="40" height="40" alt="<?=$row1['username']?>"/></a><? echo"\t";
       $minutes_dif = $row1['MinsSinceLastVisit'];

   $Ymin = 60 * 24 * 365;
   $Mmin = 60 * 24 * 30;
   $Wmin = 60 * 24 * 7;
   $Dmin = 60 * 24;
   $Hmin = 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) ";
   if($MON > 0) echo "$MON month(s) ";
   if($W > 0) echo "$W week(s) ";
   if($D > 0) echo "$D day(s) ";
   if($H > 0) echo "$H hour(s) ";
   if($minutes_dif > 0) echo "$minutes_dif minute(s) ";
      echo "ago";
       $i++; }
         }

?>

gffg4574fghsDSGDGKJYM · May 13, 2009

The code seem fine (but it's hard to read)

Are you sure that the data in the database are what did you expect for the first row ?

You can echo it just to be sure (or use another way to look at it) :

...
$minutes_dif = $row1['MinsSinceLastVisit'];
echo "<h1>".$minutes_dif."</h1>";
...

deepson2 · May 13, 2009

theonlydrayk,

I got to know why its coming my first record empty because after every refresh till 1 min i can not get anything because we having put code for seconds. so it ll wait for 1 min.

i think my problem is solved.

thanks a lot to both of you!

kickstart · May 13, 2009

but my while loop is skipping my first record. from 2nd records its showing proper values. could you please have look and tell me to get all records.

Do you mean it is not displaying anything for the first row (ie, the first row you expect isn't returned) or that something is put out for the first row but the $minutes_dif is not displayed?

All the best

Keith

deepson2 · May 13, 2009

kickstart,

my that problem is solved. but now i want to optimize my code like

if its in minute than show 30 min ago 45 min ago

if it crosses 1 hour than dont show min show only 1 hour ago, 5 hour ago

if it crosses 1 day than dont show hour only show 1 day ago..

like your query, i am studying that. mean while give me your suggestion as well.

deepson2 · May 13, 2009

i have solved that problem kieth

$Ymin = 60 * 24 * 365;

$Mmin = 60 * 24 * 30;

$Wmin = 60 * 24 * 7;

$Dmin = 60 * 24;

$Hmin = 60;

i am calculating minites here but if i add to seconds as well then how can i do that. because i am keeping at time one 1 case for example like 30 min, 3 hour, 1 day, not showing 4 hour 45 min

using this

 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";  ?>(<?=$row1['visiting_count']?>visits)<? echo"\t";

so how can i get seconds now?

kickstart · May 13, 2009

Hi

You can either try using something like the SQL I posted, or do similar coding in php based on the results of your current SQL. Something like this (not test run so there might be a typo).

switch (true)
{
case $minutes_dif < 60 :
echo $minutes_dif " minutes ago";
break;
case $minutes_dif < 1440 :
echo intval($minutes_dif / 60)." hours ago";
break;
case $minutes_dif < 10080 :
echo intval($minutes_dif / 1440)." days ago";
break;
case $minutes_dif < 302400 :
echo intval($minutes_dif / 10080)." weeks ago";
break;
case $minutes_dif < 3628800:
echo intval($minutes_dif / 302400)." months ago";
break;
default :
echo intval($minutes_dif / 3628800)." years ago";
break;
}

If you want seconds rather than minutes then use SECOND rather than MINUTE in the TIMESTAMPDIFF function in the SQL. The just either multiply the various factors by 60 in your code, or using the code above multiply the constants by 60.

All the best

Keith

deepson2 · May 13, 2009

i have changed my query like this:-

TIMESTAMPDIFF(SECOND, r.recent_visited, NOW()) AS MinsSinceLastVisit

and added extra 60 in my this code

$Ymin = 60*60 * 24 * 365;
$Mmin = 60*60 * 24 * 30;

$Wmin = 60*60 * 24 * 7;

$Dmin = 60*60 * 24;

$Hmin = 60*60*60;

$Mmin =60*60

$Smin =60;

and then

$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;

$M = (int)($minutes_dif / $Mmin);

$minutes_dif = $minutes_dif % $Mmin;

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($M > 0){

echo "$M minute(s) ";

}

else if($minutes_dif > 0){

echo "$minutes_dif second(s) ";

}

echo "ago"; ?>

but its going crazy,

o/p is coming like

1 month(s) , 23 month(s)

please help me.

kickstart · May 13, 2009

Hi

First problem I noticed is that you have tried to have 2 variables with the same name:-

$Ymin = 60*60 * 24 * 365;

$Mmin = 60*60 * 24 * 30;

$Wmin = 60*60 * 24 * 7;

$Dmin = 60*60 * 24;

$Hmin = 60*60*60;

$Mmin =60*60

$Smin =60;

All the best

Keith

deepson2 · May 13, 2009

Hello,

you are back, thanks, yes, you were right, see i made chages no luck yet

 $Ymin = 60 * 60 * 24 * 365;
   	   $Mmin = 60 * 60 * 24 * 30;
   	   $Wmin = 60 * 60 * 24 * 7;
   	   $Dmin = 60 * 60 * 24;
   	   $Hmin = 60 * 60 * 60;
   	   $Pmin = 60 * 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;

   	   $M = (int)($minutes_dif / $Pmin);
   	   	   $minutes_dif = $minutes_dif % $Pmin;


   	   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($M > 0){

   	   	   	   echo "$M minute(s) ";
   	  }
   	  else if($minutes_dif > 0){

   	   	   echo "$minutes_dif second(s) ";
   	   }

         echo "ago";

o/p is like

2178 second(s)

1 minute(s) ago

2 minute(s) ago

6 minute(s) ago

1 day(s) ago

kickstart · May 13, 2009

Hi

Is that the output for one row or for several rows?

All the best

Keith

deepson2 · May 13, 2009

Hi

Is that the output for one row or for several rows?

All the best

Keith

all these are the rows selection.

fenway · May 13, 2009

OK, that's quite enough... if you want to write php scripts, I'll move this to the PHP Help section. The SQL variant was already posted... you guys decide.

deepson2 · May 14, 2009

OK, that's quite enough... if you want to write php scripts, I'll move this to the PHP Help section. The SQL variant was already posted... you guys decide.

I sorry fenway, its become too lengthy now. but i would like to request you to move this to php help because my problem is not solved yet.

kindly do the request.

Sign In

[SOLVED] 2 min ago and timestamps??

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information