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?

Nothing, if that's the standard way of doing it. I just don't want to have to paste that dozens of places throughout my codebase if there is something simpler. — Chad Johnson, Jun 04 '09 at 21:05
Not what you asked for, but just in case: Remember to set the MySQL time zone as well. Or MySQL will convert the dates by itself. See: http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html — Philippe Gerber, Jun 04 '09 at 21:22
phpphil, how will it do so? DATETIME fields should be timezone-agnostic. Are you saying MySQL will use the system's timezone with functions like NOW() and CURDATE()? I've added --timezone=UTC to MySQL's my.cnf. — Chad Johnson, Jun 04 '09 at 21:30
the manual says: "The current session time zone setting affects display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as NOW() or CURTIME(), and values stored in and retrieved from TIMESTAMP columns. Values for TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval." with the entry in you my.cnf it should work properly! — Philippe Gerber, Jun 04 '09 at 22:45

score 22 · Answer 1 · edited Jun 18 '12 at 15:08

22

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://php.net/manual/en/timezones.php

edited Jun 18 '12 at 15:08

Jonathan

996
1
7
27

answered Jun 05 '09 at 04:35

Jordan S. Jones

13,703
5
44
49

fyi: DateTime::setTimezone is dispensable when one already set the default time zone using date_default_timezone_set(). – Philippe Gerber Jun 08 '09 at 11:58
Right, but if he date_default_timezone_set() to UTC and he wants the date to be in PDT or etc. he will have to set it explicitly. – Jordan S. Jones Jun 08 '09 at 16:08

score 9 · Answer 2 · answered Apr 19 '11 at 18:34

What you're doing is the right way of doing things. I'd recommend sticking with working in only UTC and just converting at the last minute for the display.

Here's a quick function I put together for time zone conversion using the DateTime class that comes with PHP. It's a bit more code than you have but I think it's easier and a better way to structure things...

function convert_time_zone($date_time, $from_tz, $to_tz)
    {
    $time_object = new DateTime($date_time, new DateTimeZone($from_tz));
    $time_object->setTimezone(new DateTimeZone($to_tz));
    return $time_object->format('Y-m-d H:i:s');
    }

http://richardwillia.ms/blog/2011/04/time-zone-conversion-using-datetime-class/

Hope that helps.

I actually like this solution, I have my DateTime stored in MySQL, and I get to convert the time as per user's timezone settings. — Deano, Apr 14 '15 at 17:36
Great solution only thing i changed was passing in the format as sometimes i don't always want a specific format especially when displaying to the end user. +1 great answer — William Worley, Sep 08 '15 at 04:01

score 6 · Accepted Answer · answered Jun 08 '09 at 05:57

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

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

Configuring MySQL

Import timezones into MySQL if necessary:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
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);
    }

}

We ended up doing the same thing, pretty much. We set the timezone to UTC in PHP via date_default_timezone_set('UTC'), and we also change the default timezone in MySQL. Then, to convert, we use the PEAR Date class, which even takes care of daylight savings. — Chad Johnson, Jun 05 '10 at 19:59

score 1 · Answer 4 · answered Jan 30 '14 at 05:57

This worked for me and it's pretty clean

function convert_to_user_date($date, $userTimeZone = 'America/Los_Angeles', $serverTimeZone = 'UTC', $format = 'n/j/Y g:i A')
{
    $dateTime = new DateTime ($date, new DateTimeZone($serverTimeZone));
    $dateTime->setTimezone(new DateTimeZone($userTimeZone));
    return $dateTime->format($format);
}

function convert_to_server_date($date, $userTimeZone = 'America/Los_Angeles', $serverTimeZone = 'UTC', $format = 'n/j/Y g:i A')
{
    $dateTime = new DateTime ($date, new DateTimeZone($userTimeZone));
    $dateTime->setTimezone(new DateTimeZone($serverTimeZone));
    return $dateTime->format($format);
}

score 1 · Answer 5 · answered Mar 03 '16 at 05:08

Convert user timezone to server timezone and vice versa, with a single function:

function convertTimeZone($date, $convertTo = 'userTimeZone', $userTimeZone = 'America/Los_Angeles', $serverTimeZone = 'UTC', $format = 'n/j/Y g:i A')
{
    if($convertTo == 'userTimeZone'){
       $dateTime = new DateTime ($date, new DateTimeZone($serverTimeZone));
       $dateTime->setTimezone(new DateTimeZone($userTimeZone));
       return $dateTime->format($format);   
    } else if($convertTo == 'serverTimeZone'){
       $dateTime = new DateTime ($date, new DateTimeZone($userTimeZone));
       $dateTime->setTimezone(new DateTimeZone($serverTimeZone));
       return $dateTime->format($format);   
    }
}

echo convertTimeZone(date('Ydm h:i:s'),'serverTimeZone');

score 1 · Answer 6 · answered Jan 18 '12 at 03:15

<?php

  function getNoteDateTimeZone($date = null, $from_dtz = 'US/Central', $to_dtz = null) {
        //$from_zt = 'US/Central'; // Time Zone = -06:00
        if (is_null($date) == FALSE && is_null($from_dtz) == FALSE && is_null($to_dtz) == FALSE) {
            // set TimeZone from
            $time_object = new DateTime($date, new DateTimeZone($from_dtz));
            $time_now_object = new DateTime("now", new DateTimeZone($from_dtz));
            // Change TimeZone
            $time_object->setTimezone(new DateTimeZone(trim($to_dtz)));
            $time_now_object->setTimezone(new DateTimeZone(trim($to_dtz)));
            // Is day = day in $time_now_object, $time_object..?
            if ($time_now_object->format('d') == $time_object->format('d')) {
                return $time_object->format('H:i:s');
            } else {
                return $time_object->format('Y-m-d H:i:s');
            }
        } else {
            return '';
        }
    }
?>

Use sample:

<?php 
 $date = '2008-06-02 20:32:46';
 $dtz = 'America/Argentina/Buenos_Aires';
 echo getNoteDateTimeZone($date, 'US/Central', $dtz); // Out = 2008-06-02 23:32:46
?>

score 1 · Answer 7 · answered Jun 08 '09 at 05:27

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:

If you use NOW() or any local time functions, you should update them to UTC_TIMESTAMP()
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.

Very interesting and good to know. This thread--http://stackoverflow.com/questions/998523/how-common-are-timestamps-over-datetime-fields--seems to disagree with you, saying that "TIMESTAMP is not intended to be used for storing general Date / Time information." Do you have any thoughts on this? — Chad Johnson, Jun 15 '09 at 22:33
Well, I heartily disagree. TIMESTAMP can be used for general date/time information, as long as you don't run into the issues around it, specifically that it is stored as a UTC unsigned integer in the database, and it will run out of dates in 2038 (or so...) If you need to store dates before 1970 or after 2038, use DATETIME and do the gymnastics required to support time zones. Otherwise, save yourself the effort and use timestamps and get TZ translation for (almost) free. — razzed, Jun 16 '09 at 03:42

score 0 · Answer 8 · edited Nov 09 '11 at 19:28

0

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

edited Nov 09 '11 at 19:28

Jason Plank

2,336
5
31
40

answered Nov 07 '09 at 18:29

e2p

1

How can I easily convert dates from UTC via PHP?

8 Answers8

Configuring CentOS

Configuring MySQL

PHP Code

Linked