Databases
StackOverflow2013
Postsdbo
POWhat is wrong with my timestamps?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhat is wrong with my timestamps?
    text
    copied!<p>So basicly, I played around with my MySQL database a couple of days ago and I set the timezone to MST to match with the server I have my PHP-script on (I've also set the PHP-server timezone to MST (<code>date_default_timezone_set("MST")</code>) to match with the database). I have a function which requires one parameter and that's an UNIX timestamp.</p> <pre><code>function time_elapsed_string($time) { $time = time() - $time; $tokens = array( 31536000 =&gt; 'year', 2592000 =&gt; 'month', 604800 =&gt; 'week', 86400 =&gt; 'day', 3600 =&gt; 'hour', 60 =&gt; 'minute', 1 =&gt; 'second' ); foreach($tokens as $unit =&gt; $text) { if($time &lt; $unit) continue; $numberOfUnits = floor($time / $unit); return $numberOfUnits.' '.$text.(($numberOfUnits&gt;1)?'s':''); } } </code></pre> <p>What this function does is that it takes the <code>$time</code>. It gets the time difference between the current time and the $time and sets <code>$time</code> to the difference. It then calculates the biggest possible timestamp the <code>$time</code> could be and echoes it. If it's in plural (2 weeks, 5 months etc) it adds a <em>s</em> to the end.</p> <p>However, if I put the current time as the <code>$time</code>-parameter it'll echo '<strong>[SPACE]ago</strong>'. I've solved that temporarily by replacing the row <code>$time = time() - $time;</code> with <code>$time = (time() + 80) - $time;</code> where I increase the current number <em>80</em> with 5 or 10 to make it accurate.</p> <p><strong>Short explanation:</strong> I have to add seconds to the current time when calculating the difference to make so that it doesn't echo just '<strong>[SPACE]ago</strong>'.</p> <p>I'm using this function when I've updated my status on my page, an insert-query gets sent to the database (without the time-column filled) and because the timestamp column is the current timestamp on default it gets set to the current timestamp. </p> <p>How can I fix this because I really understand that adding 5 or 10 everyday won't be stable in the future.</p> <p>PS. I don't think I can restart my MySQL server or such because I'm on a webhost and I guess I don't have the right privileges to do so.</p> <p><strong>EDIT1:</strong> It looks like the inserted timestamp is around 1 minute ahead then it is in real life. Could this be causing it?</p> <p><strong>EDIT2:</strong> Now, a couple of weeks later I need to add between 100 &amp; 200 seconds extra to make sure the <em>time ago</em> function is accurate.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload