• Registered: 2007-04-15
  • Posts: 12

Topic: time storage and formatting

according to the documentation, times are stored in tables such as `posts` as a UNIX timestamp in an INT field.

as far as i know, the UNIX timestamp returned by mysql is seconds since the epoch in UTC.

this begs the question: if the times stored in the database are in UTC, why do you adjust for it in your format_date() function?

function format_time($timestamp, $date_only = false)
{
    global $pun_config, $lang_common, $pun_user;

    if ($timestamp == '')
        return $lang_common['Never'];

    $diff = ($pun_user['timezone'] - $pun_config['o_server_timezone']) * 3600;
    $timestamp += $diff;
    $now = time();

    $date = date($pun_config['o_date_format'], $timestamp);
    $today = date($pun_config['o_date_format'], $now+$diff);
    $yesterday = date($pun_config['o_date_format'], $now+$diff-86400);

    if ($date == $today)
        $date = $lang_common['Today'];
    else if ($date == $yesterday)
        $date = $lang_common['Yesterday'];

    if (!$date_only)
        return $date.' '.date($pun_config['o_time_format'], $timestamp);
    else
        return $date;
}

you apply the user's timezone and the server's timezone as $diff to $timestamp. but _why_ would you diff the server's timezone if the UNIX timestamp is in UTC anyway?

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: time storage and formatting

Timestamps are taken from PHP's time() function, which returns timestamps based on the server's clock (not on GMT).
In PunBB 1.3, we use gmdate instead of date, which means server_timezone becomes obsolete wink

  • Registered: 2007-04-15
  • Posts: 12

Re: time storage and formatting

    $now = time();

    // Did everything go according to plan?
    if (empty($errors) && !isset($_POST['preview']))
    {
        // If it's a reply
        if ($tid)
        {
            if (!$pun_user['is_guest'])
            {
                // Insert the new post
                $db->query('INSERT INTO '.$db->prefix.'posts (poster, poster_id, poster_ip, message, hide_smilies, posted, topic_id) VALUES(\''.$db->escape($username).'\', '.$pun_user['id'].', \''.get_remote_address().'\', \''.$db->escape($message).'\', \''.$hide_smilies.'\', '.$now.', '.$tid.')') or error('Unable to create post', __FILE__, __LINE__, $db->error());

so it is! but php.net says:

Returns the current time measured in the number of seconds since the Unix Epoch (January 1 1970 00:00:00 GMT).

it says GMT and not the local server time. so is the manual wrong?

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: time storage and formatting

The epoch happened at 00:00:00 GMT, the PHP function does not return the timestamp in GMT wink
http://us2.php.net/manual/en/ref.datetime.php

Note:  Please keep in mind that these functions are dependent on the locale settings of your server. Make sure to take daylight saving time (use e.g. $date = strtotime('+7 days', $date) and not $date += 7*24*60*60) and leap years into consideration when working with these functions.

5 Reply by lucasreddinger (edited by lucasreddinger 2007-04-15 19:20)

  • Registered: 2007-04-15
  • Posts: 12

Re: time storage and formatting

so you're saying that if my server is configured for GMT-5, then on Jan 1 1970 00:00:00, time()==(-5*60*60) ?

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: time storage and formatting

lucasreddinger wrote:

so you're saying that if my server is configured for GMT-5, then on Jan 1 1970 00:00:00, time()==(-5*60*60) ?

If your server's timezone is GMT -5, at Jan 1 1970 00:00:00 GMT, time() would return (60 * 60 * -5), yes.

  • Registered: 2007-04-15
  • Posts: 12

Re: time storage and formatting

but mysql's UNIX_TIMESTAMP() doesn't depend on the server locale?

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: time storage and formatting

lucasreddinger wrote:

but mysql's UNIX_TIMESTAMP() doesn't depend on the server locale?

Right, it takes the timezone information it knows and corrects for it so the timestamp is GMT/UTC. Of course, using something like from_unixtime() on it will return a value that isn't GMT/UTC

  • Registered: 2007-04-15
  • Posts: 12

Re: time storage and formatting

man, i don't like this.

mysql says the time is: 1176666741

php says the time is: 1176666741

php says the server tz is: MDT

is the output from:

<?php

$dbms_host = "localhost";
$dbms_user = "root";
$dbms_pass = "";
$dbms_db   = "wlw";

$dbms_cnx = mysql_connect($dbms_host, $dbms_user, $dbms_pass);
mysql_select_db($dbms_db);
$result = mysql_query("SELECT UNIX_TIMESTAMP()");
list($mysql_time) = mysql_fetch_row($result);
$php_time = time();
$php_tz = date("T");

echo "mysql says the time is: ".$mysql_time."<br /><br />\n";
echo "php says the time is: ".$php_time."<br /><br />\n";
echo "php says the server tz is: ".$php_tz."<br /><br />\n";

mysql_close();

?>

and if i load that page at the same time as http://www.unixtimestamp.com/, i get the exact same number of seconds. and http://www.unixtimestamp.com/ claims to be running EDT, i'm running MDT!

10 Reply by Smartys (edited by Smartys 2007-04-15 20:18)

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: time storage and formatting

OK, so I managed to confuse myself big_smile
time() and UNIX_TIMESTAMP() both return GMT/UTC values. date() and FROM_UNIXTIME() take unix timestamps and return a date/time based on the current location of the server. The reason PunBB needs to adjust is to correct for the corrections that date() will make.

Picture it like this:
The timestamp in the database is 1176666741, my timezone is set to -4, the server's timezone is +5 (we'll assume PunBB has that set as well)
We add ((user timezone - server timezone) * 3600), or ((-4 - 5) * 3600), or -32400 seconds.
We call the date function, which adds (server timezone * 3600), or (5 * 3600), or 18000 seconds.
That leaves us with a change of -14400 seconds, or (-4 * 3600).

I hate timezones with a passion tongue
Sorry for the confusion smile

Edit: And as I said, in 1.3 PunBB will use gmdate, which means we won't have to correct for the server timezone tongue

  • Registered: 2007-04-15
  • Posts: 12

Re: time storage and formatting

i get it now. thanks a lot! big_smile

  • Registered: 2007-04-15
  • Posts: 12

Re: time storage and formatting

Smartys wrote:

I hate timezones with a passion tongue
Sorry for the confusion smile

the only thing i hate worse than timezones is DST. i'll make some fun threads about that later..