How can I easily convert dates from UTC via PHP?

Question

I am storing dates in a MySQL database in datetime fields in UTC. I'm using PHP, and I've called date_timezone_set('UTC') so that all calls to date() (without timestamp) return the date in UTC.

I then have it so a given web site can select its timezone. Now I want dates to display in the site's timezone. So, if I have a date stored as '2009-04-01 15:36:13', it should display for a user in the PDT timezone (-7 hours) as '2009-04-01 08:36:13'.

What is the easiest (least code) method for doing this via PHP? So far all I've thought of is

date('Y-m-d H:i:s', strtotime($Site->getUTCOffset() . ' hours', strtotime(date($utcDate))));

Is there a shorter way?

Answer 1

I would probably do this:

date('Y-m-d H:i:s', strtotime($utcDate)+$Site->getUTCOffset()*60*60));

Assumes that the offset is in hours.

or better yet, just write function/method:

$date_local = date_local($utcDate, $Site->getUTCOffset());

function date_local($date, $offset) {

    return date('Y-m-d H:i:s', strtotime($date)+$offset*60*60);

}

Answer 2

Why not use the built in DateTime/TimeZone functionality?

<?php

$mysqlDate = '2009-04-01 15:36:13';

$dateTime = new DateTime ($mysqlDate);
$dateTime->setTimezone(new DateTimeZone('America/Los_Angeles'));

?>

DateTime Class: http://us3.php.net/manual/en/class.datetime.php DateTimeZone Class: http://us3.php.net/manual/en/class.datetimezone.php

PHP's supported Timezones: http://us3.php.net/manual/sv/timezones.php

Answer 3

Having spent a lot of time dealing with this issue, do not attempt to implement time zone translation yourself. It's a royal PIA, fraught with difficulties, and it's very hard to get it right internationally.

That said, the best option is to convert your datetimes in MySQL to timestamps, and just use the database to convert times:

 mysql> set time_zone='America/New_York';

timestamps in MySQL are smaller, and support time zone translation. datetime does not.

Before you display the site information on the page, just invoke the above command, and it will display correctly without any PHP code changes at all.

Caveats:

1. If you use NOW() or any local time functions, you should update them to UTC_TIMESTAMP()
2. timestamps have interesting update and insert properties which you may want to turn off.

To turn off timestamp properties:

ALTER TABLE mytable CHANGE COLUMN Created Created timestamp NULL DEFAULT 0;

The DEFAULT 0 disables the column being updated when you update other columns.

Answer 4

Here's what we did with our servers. We set everything to use UTC, and we display in the user's time zone by converting from UTC on the fly. The code at the bottom of this post is an example of how to get this to work; you should confirm that it works in all cases with your setup (i.e. daylight savings, etc).

Configuring CentOS

1. Edit /etc/sysconfig/clock and set ZONE to UTC
2. ln -sf /usr/share/zoneinfo/UTC /etc/localtime

Configuring MySQL

1. Import timezones into MySQL if necessary:

   mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

2. Edit my.cnf and add the following within the [mysqld] section:

   default-time-zone = 'UTC'

PHP Code

<?php
/*
Example usage:
  $unixtime = TimeUtil::dateTimeToTimestamp('2009-04-01 15:36:13');
  echo TimeUtil::UTCToPST("M d, Y - H:i:s", $unixtime);
*/

// You should move this to your regular init method
date_default_timezone_set('UTC'); // make this match the server timezone

class TimeUtil {
    public static function timestampToDateTime($timestamp) {
        return gmdate('Y-m-d H:i:s', $timestamp);
    }

    public static function dateTimeToTimestamp($dateTime) {
        // dateTimeToTimestamp expects MySQL format
        // If it gets a fully numeric value, we'll assume it's a timestamp
        // You can comment out this if block if you don't want this behavior
        if(is_numeric($dateTime)) {
            // You should probably log an error here
            return $dateTime;
        }
        $date = new DateTime($dateTime); 
        $ret = $date->format('U');
        return ($ret < 0 ? 0 : $ret);
    }

    public static function UTCToPST($format, $time) {
        $dst = intval(date("I", $time));
        $tzOffset = intval(date('Z', time()));
        return date($format, $time + $tzOffset - 28800 + $dst * 3600);
    }

}

Answer 5

ADDTIME($utcDate,$Site->getUTCOffset())