CODEWITHSUNDEEP
phpmysqltimezone
Sean Bone
Sean Bone

Reputation: 3546

PHP timezone function not working

I'm creating a forum, which also stores the time a post was sent, and I need to convert that into the user's timezone.
Now, the MySQL DataBase stores the time with UTC_TIMESTAMP() (in a column with the DATETIME type), and I created a little function from the code on http://www.ultramegatech.com/blog/2009/04/working-with-time-zones-in-php/ to convert the time to the user's timezone. This is the function:

function convertTZ($timestamp, $tz='UTC', $format='d-m-Y H:i:s') {
    // timestamp to convert
    $timestamp = strtotime($timestamp);
    // the time formatting to use
    $format = $format;
    // the time zone provided
    $tz = $tz;

    // create the DateTimeZone object for later
    $dtzone = new DateTimeZone($tz);

    // first convert the timestamp into an RFC 2822 formatted date
    $time = date('r', $timestamp);

    // now create the DateTime object for this time
    $dtime = new DateTime($time);

    // convert this to the user's timezone using the DateTimeZone object
    $dtime->setTimeZone($dtzone);

    // print the time using your preferred format
    $time = $dtime->format($format);

    return $time;
}

And I made a test page at http://assets.momo40k.ch/timezones.php.

Now, when I insert a post into the DataBase at, say, 11:50 in my timezone (which is Europe/Rome), it inserts 09:50 in UTC, wich is correct, according to some online timezone converters.
But when I try to convert it back to Europe/Rome with the convertTZ() function, it returns 09:50, as if Europe/Rome is UTC. If I try converting it to a GMT+2:00 timezone, it returns 10:50. Can anyone fugure out why this is?


P.S: I'm not using the CONVERT_TZ() SQL function because my server does not support named timezones, so this function is my workaround.

Upvotes: 2

Views: 1153

Answers (2)

Stu
Stu

Reputation: 1317

MySQL always stores TIMESTAMP fields in UTC internally (that's the definition of a unix timestamp, in fact). So when you SELECT or UPDATE/INSERT/REPLACE, the time you get or set is always in the MySQL server's local time zone.

So a common mistake is to store UTC_TIMESTAMP(), which MySQL interprets as a local time and so the current time gets double-converted to UTC when it stores it internally in the field as a unix TIMESTAMP.

Upvotes: 1

Arnaud Le Blanc
Arnaud Le Blanc

Reputation: 99921

Make sure your stored timestamps are UTC:

$date = new DateTime($timestamp, new DateTimeZone("UTC"));
$date->format(DATE_W3C); // does it gives the expected result ?

BTW your function can be simplified to this:

function convertTZ($timestamp, $tz='UTC', $format='d-m-Y H:i:s') {
    $dtime = new DateTime($timestamp, new DateTimeZone("UTC"))
    $dtime->setTimezone(new DateTimeZone("UTC"));
    return $dtime->format($format);
}

Upvotes: 1

Related Questions