Asked  7 Months ago    Answers:  5   Viewed   33 times

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?



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

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);

Wednesday, March 31, 2021
answered 7 Months ago


$file = '';
$file_headers = @get_headers($file);
if(!$file_headers || $file_headers[0] == 'HTTP/1.1 404 Not Found') {
    $exists = false;
else {
    $exists = true;

From here and right below the above post, there's a curl solution:

function url_exists($url) {
    return curl_init($url) !== false;
Wednesday, March 31, 2021
answered 7 Months ago

The DateTime class, here, might help (quoting):

Each component of date (e.g. year) is internally stored as 64-bit number so all imaginable dates (including negative years) are supported.

But note that:

  • It's only exists in PHP >= 5.2
  • And several methods only exist in PHP >= 5.3

So: beware of which methods you're using, if you're developping on PHP 5.3 and want your software to be compatible with PHP 5.2

Another solution (especially, if using Zend Framework in your application) would be the Zend_Date component (quoting):

Although PHP 5.2 docs state, "The valid range of a timestamp is typically from Fri, 13 Dec 1901 20:45:54 GMT to Tue, 19 Jan 2038 03:14:07 GMT," Zend_Date supports a nearly unlimited range, with the help of the BCMath extension

Wednesday, March 31, 2021
answered 7 Months ago

In general, the idea of locating an exact time zone (such as Europe/Helsinki or Europe/Paris) based solely on an offset (such as UTC+02:00) is not possible with any sort of reliability or accuracy. Many time zones share the same offset at any given point in time, and many time zones switch between two different offsets throughout the year due to daylight saving time rules.

You can see a list of all of the time zones, and their standard and daylight offsets here.

Even the idea of going from a time zone abbreviation to a time zone is not sound, as several time zones could share the same abbreviation. For example, "CST" could be "Central Standard Time" (USA), "Central Standard Time" (Australia), "Central Summer Time" (Australia), "China Standard Time", or "Cuba Standard Time". See this list for details.

Given these concerns, it is rather strange that PHP would give you a function called timezone_name_from_abbr. I recommend avoiding using it, as it is clearly conceptually flawed.

If you're after detection of the user's time zone, consider using the JavaScript library jsTimeZoneDetect. It is not perfect either, but it works in general, and is based on a more logical algorithm.

Wednesday, March 31, 2021
answered 7 Months ago

Just found the problem: PHPExcel translates to UTC time inside: PHPExcel/Shared/Date.php

The function PHPToExcel change temporarily the timezone to UTC, and then go back to the default timezone.

The solution is simple but requires to change that file. Simply copy paste the PHPToExcel function, name it PHPToExcelWithoutUTC (or any name you want) and comment the following lines:

//$saveTimeZone = date_default_timezone_get();


And it works.

EDIT: It seems it's intended to always use UTC time by default. I'm not deleting this thread because it might be useful for those who want the user timezone.

Wednesday, August 18, 2021
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :