"Time ago" from MySQL timestamp

Question

I'm trying to make a table of results including the timestamp for each entry from the database. However, I need the time to be displayed as "X seconds/minutes/hours ago".

I've tried the jQuery timeago plugin with zero success and am now wondering how to do this for each entry.

$result = mysql_query("SELECT * from realmfeed order by ID asc");

echo "<table class='display'>\n";

while($row = mysql_fetch_array($result))
{
    echo "<tr><td><b>".$row['ID']."</b></td>\n";
    echo "<td>".$row['eventType']."</td>\n";
    echo "<td>".$row['server']."</td>\n";
    echo "<td>".$row['realm']."</td>\n";
    echo "<td>".$row['name']."</td>\n";
    echo "<td>".$row['time']."</td>\n</tr>\n\n";
}


echo "</table>\n";

How is it possible to create a "time ago" function for each result?

I would modify the query to use a diff() function from the stamp to now, and just display that on the page. No need to get any client side stuff involved. — Christopher Johnson, Feb 09 '12 at 20:18
@Dagon the reason I didn't specify DATEDIFF() is that I believe that will only return days. He will probably have to create a UDF to make it work. Converting to unix_timestamp or the like. — Christopher Johnson, Feb 09 '12 at 20:27
a quick search on SO yielded this: http://stackoverflow.com/questions/5279079/mysql-convert-timediff-output-to-day-hour-minute-second-format — Christopher Johnson, Feb 09 '12 at 20:34
you can wrap what ever you need to around DATEDIFF, to get the required format. — , Feb 09 '12 at 20:36

score 5 · Accepted Answer · answered Feb 09 '12 at 21:13

5

$time = strtotime($row['time']);
$dbDate = new DateTime($time);
$currDate = new DateTime();
$interval = $currDate->diff($dbDate);
echo $interval->d." days ".$interval->h." hours";

please refer to DateInterval for available functions and fields

answered Feb 09 '12 at 21:13

mdprotacio

842
6
18

score 2 · Answer 2 · answered May 31 '20 at 09:47

The problem with using PHP to do the time difference calculations is you're assuming the MySQL and PHP servers are in the same time zones. Better to do the difference calculation in the SELECT:

SELECT TIMEDIFF(NOW(), `time`) AS ago FROM realmfeed ORDER BY ID ASC

This will return hh:mm:ss formatted time difference.

Or if you'd prefer just the number of seconds:

SELECT TIME_TO_SEC(TIMEDIFF(NOW(), `time`)) AS secondsAgo FROM realmfeed ORDER BY ID ASC

score 1 · Answer 3 · answered Feb 09 '12 at 20:50

I would get the time stamp from MYSQL then do the maths in php eg.

$result = mysql_query("SELECT ..., UNIX_TIMESTAMP(time) as time FROM realfeed ORDER BY ID ASC");


$row = mysql_fetch_array($result);
$then = $row['time'];
$now = time();

$diff = $now - $then; //Now you have the difference in seconds

There is a nice function here you could use the difference on although I have not checked it myself.....

http://itwigle.com/twig/PHP_Time_Ago_Function

score -1 · Answer 4 · answered Mar 19 '13 at 12:13

try this this may help

<html>
<head>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
<script src="jquery.timeago.js"></script>
</head>

<body>
<?php 
$result = mysql_query("SELECT * from realmfeed order by ID asc");

echo "<table class='display'>\n";

while($row = mysql_fetch_array($result))
{
    echo "<tr><td><b>".$row['ID']."</b></td>\n";
    echo "<td>".$row['eventType']."</td>\n";
    echo "<td>".$row['server']."</td>\n";
    echo "<td>".$row['realm']."</td>\n";
    echo "<td>".$row['name']."</td>\n";
    echo "<td><abbr class=\"timeago\" title=\"".date("j F Y h:i:s A",$row['time'])."\"></abbr></td>\n</tr>\n\n";
}


echo "</table>\n";
?>

    <script>
    jQuery(document).ready(function($){
     $("abbr.timeago").timeago()

    });
    </script>

</body>
</html>

It's probably better to do the time conversion on the server side either in the PHP or Mysql. — Ben Guthrie, Sep 03 '14 at 23:07
I think that this answer is best, as it provides the ability to control the time manipulation on the client side rather than a server side. When it is done on a server side, it gives a redundant information, such information can be easily calculated on a client side, plus giving the separation of such DISPLAY information between the server and the client, makes it easier to maintain, without changing the server side. — Slavik Meltser, Feb 21 '17 at 19:02

"Time ago" from MySQL timestamp

4 Answers4