What is wrong with my timestamps?

Since you are doing the difference based on what is stored in MySQL, your choices are:

1. Use Network Time Protocol (see http://pool.ntp.org/) across your servers, and hope for the best...

2. Or fetch the time from MySQL using UNIX_TIMESTAMP() in options like below:

   • within your query: SELECT *, UNIX_TIMESTAMP() AS unix_timestamp FROM table
   • or call it separately: SELECT UNIX_TIMESTAMP() AS unix_timestamp

An optional second parameter is the secret which can help by passing in the "unix_timestamp" result from MySQL into the second optional parameter, coding an optimized difference (you found 5 to 10 'working'), or simply allow the PHP timestamp() function to become the default as already implemented above when your servers get synchronized.

Either way make a simple update to the function like this:

function time_elapsed_string($time, $time0 = NULL){    if ($time0 === NULL)     {        $time0 = time();    }    $time = $time0 - $time;    ...

EXAMPLE uses:

time_elapsed_string($recordTimeStamp);                         // as-istime_elapsed_string($recordTimeStamp, time() + 5);             // an "optimized" differencetime_elapsed_string($recordTimeStamp, $recordUnixTimeStamp);   // from MySQL additiontime_elapsed_string($recordTimeStamp, $databaseUnixTimeStamp); // from separate query

What isn't clear to me is how you're storing your timestamp in MySQL. There are at least three ways to store a date in MySQL:

1. As an int - output from time()
2. As DATETIME
3. As TIMESTAMP

The first two options don't even take into account the timezone of the database server. You don't even really say how you set the timezone either.

Assuming for the moment you're using a TIMESTAMP, you can get a unix timestamp from MySQL using the UNIX_TIMESTAMP(date) function.

When setting the timezone setting on the MySQL server, it's usually better to do it on a per-session basis, like this:

$sql = "SET names $charset, time_zone='".date("P")."'";

The root of your problem seems to be the function itself. There are not exactly "31536000" seconds in each year, nor are there "2592000" seconds in a month. This is because of there being different amount of days in a month, leap-years, etc.

This is based on a function I wrote a long time ago (rewrote it to make it easier to read). Hopefully it'll be useful to you:

function relativeTime($time){    $date = getdate($time);    $today = getdate();    $daydiff = $today["mday"]-$date["mday"];    $text = "";    if ($today["mon"] === $date["mon"] && $today["year"] === $date["year"] && $daydiff < 2) {        if ($daydiff === 1) {            $text =  sprintf('Yesterday, %s', date('g:i A T', $time));        } else if ($today["hours"] == $date["hours"]) {            $min = $today["minutes"] - $date["minutes"];            if ($min > 1) {                $text = sprintf('%s minutes ago', $min);            } else {                $text = 'A minute ago';            }        } else {            $text = sprintf('Today, %s', date('g:i A T', $time));        }    } else {        $text = date('j F Y - g:i A T', $time);    }    return $text;}

The function above only does "# minutes ago" and "Today", "Yesterday", and then the date. You can, of course, add to it to make it be even more precise.

Instead of using the MySQL CURRENT_TIMESTAMP when you insert your data, if use php to calculate it from time() they will match and you will not have the post time 60 seconds ahead of now. You could always use time()-1 when you insert that way there will always be at least 1 second difference.

To get a mysql DATETIME format from date just use this, then specify the value for your column as $date.

$date = date('Y-m-d H:i:s', (time()-1));

In most hosting environments the sql server and web servers are different servers so there can be some time drift between them, but it certainly shouldn't be in the order of a minute, any web host worth their salt will ensure they are all synced with the same time server so any drift should be measured in microseconds