Dealing with Timezones in PHP

What is the best way to handle timezone on PHP & MySQL?

As described in MySQL Server Time Zone Support:

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.

Therefore, if you use TIMESTAMP type columns, MySQL will handle timezone conversion for you automatically: just set the appropriate timezone for the session in its time_zone variable.

Handling date and time based on timezone on forum sites

It is simple. Don't store timestamp directly tends to user timezone.

Whatever user time zone u convert to your php default timezone and store it to Database

If user1 from india ,convert it to your php timezone save the message post time, while showing to user2 convert the time stamp from your php timezone to user2 time zone and show the data. It will give correct value

Make sure your php and MySQL timezone are same.

For example 
User 1 from IST
User 2 from UTC

Your php and MySQL time zone is PsT

If user1 post msg u convert IST to PST store to your db.
If user2 post msg u convert UTC to PST
store to it db.

While showing post to user1
U should convert PST to IST 
for user2 Convert PST to UTC

USE MYSQL FUNCTION convert_tz 

IN PHP to show ago follow below method

function time_elapsed_string($datetime, $full = false) {
    $now = new DateTime;
    $ago = new DateTime($datetime);
    $diff = $now->diff($ago);

    $diff->w = floor($diff->d / 7);
    $diff->d -= $diff->w * 7;

    $string = array(
        'y' => 'year',
        'm' => 'month',
        'w' => 'week',
        'd' => 'day',
        'h' => 'hour',
        'i' => 'minute',
        's' => 'second',
    );
    foreach ($string as $k => &$v) {
        if ($diff->$k) {
            $v = $diff->$k . ' ' . $v . ($diff->$k > 1 ? 's' : '');
        } else {
            unset($string[$k]);
        }
    }

    if (!$full) $string = array_slice($string, 0, 1);
    return $string ? implode(', ', $string) . ' ago' : 'just now';
}

Get User Time zone using http://ipinfodb.com/ip_location_api.php

or by Java script

get user timezone

Working with timezone in PHP & MySQL

Area and location is important if you care about retaining all the data. The best way (in my opinion) to store dates is to store a UTC timestamp + the location.

Just the offset may be enough for certain calculations, but it could not be enough if you have a timestamp, and want to know the exact time for something like "+1 day". As this varies in different countries with different rules for daylight savings time.

So if you want to be absolutely certain you are not 'losing information' and unable to do time-based calculations in the future, store the UTC timestamp and the olson id (e.g.: Europe/Amsterdam).

To answer your second question, if you have these two pieces of information, you can easily reconstruct it with DateTime:

$dt = new DateTime('@' . $timeStamp);
// Now convert it to the users timezone
$dt->setTimeZone(new DateTimeZone('Europe/Berlin'));

// Now you have a 'DateTime' object which you can easily display with the ->format function.

Addition

I personally prefer to store timestamps as integers. The TIMESTAMP type does automatic conversion, and I feel it's better to let the PHP application handle this, this makes especially sense for what I think your use-case is (simple localization for users).

Using DATETIME works too, but the storage requirements are much higher than just using an integer. If you do prefer DATETIME, try to make it a rule within your application to store every value always as UTC, as there is never any confusion especially in relation to DST transitions and law changes in your local timezone.

If you simply want to show times on your web application calculated based on the users' local timezone, the offset is useless. The offset changes twice a year for most countries, and almost every year one or two country changes when this happens.

You only need the location if you use PHP's awesome DateTime and DateTimeZone objects.

A last bit of advice:

People tend to confuse dates and times in PHP applications and sending around these values in many different formats (strings, ints, etc) and mix GMT and UTC. Try to make it a rule for yourself to only ever send around DateTime objects in function arguments and return values, so you can typehint and there is never any doubt in what format a variable is in. It will be worth it.

Best way to handle storing/displaying dates in different timezones in PHP?

It can be made simpler still. Since you are using JavaScript then why not use JavaScript to adjust the time zone on the client as well?

1. Store all times on the server as UTC
2. Serve them to the client as UTC
3. Client uses JavaScript adjust time to local time zone

Not only does this make things simpler but it also overcomes a problem with your model. If I registered my account in New York but travelling to Australia, I want to see the times as per the Australian time zone. In fact, using JavaScript you use can easily adjust the settings, making the design even more dynamic. Second, you can avoid the overhead of storing the user's time zone.

That said, if you want your design to degrade to non-JavaScript browsers then you are better off taking a full server side approach relying on HTTP cookies (as opposed to relying on JS to fetch cookies).

What is the most effective way to manage time zones in a PHP/MySQL application?

I'd propose another solution:

1. Store all the dates in mysql timestamp column type
2. Right after connect to mysql - specify current user's timezone with SET time_zone='<tz>' where <tz> can be either +01:00 offset or timezone name Asia/Vladivostok (for the latter special timezones db should be imported by DBA)

The benefit of using timestamp is that it automatically converts the date to and from UTC, according to the time_zone you've specified in the current connection.

More details about timestamp: http://dev.mysql.com/doc/refman/5.1/en/timestamp.html

About retrieving current user's timezone (even though there are a lot of answers here on SO about this topic, let's do that once again):

1. As long as there is a TZ value in cookies (look at step 4) - use it.
2. For registered/authenticated users - you can ask to specify their timezone and store it permanently in DB.
3. For guests you can retrieve the timezone from javascript (Date.getTimezoneOffset()) and send it with ajax
4. If it is the first request/nothing passed from ajax - guess it by IP
5. For each step (1-3) - save timezone to a cookie.

Dealing with PHP timezone according to user

Bottom line, you should store everything UTC

When you display times for a particular user, use a timezone of their choosing. Store the timezone of the user, like "Asia/Calcutta" and simply convert the time when displaying it using the date_default_timezone_set method.

I will attempt to answer your questions from the comment here.

You store everything UTC always. It is the baseline. When you display the times associated with anything you convert based on the user. If you want to display Posted 8 mins ago then you are taking the delta between the current UTC time and the UTC time associated with a post. If you send a message from user A (in India) to user B (in Los Angeles, USA) then you would store the message time in UTC. If user A is viewing it, the time would be converted to "Asia/Calcutta" and if user B is viewing it, the time would be converted to "America/Los_Angeles". Using UTC will make your life a lot easier. Trust me.

PHP date() with timezone?

For such task, you should really be using PHP's DateTime class. Please ignore all of the answers advising you to use date() or date_set_time_zone, it's simply bad and outdated.

I'll use pseudocode to demonstrate, so try to adjust the code to suit your needs.

Assuming that variable $tz contains string name of a valid time zone and variable $timestamp contains the timestamp you wish to format according to time zone, the code would look like this:

$tz = 'Europe/London';
$timestamp = time();
$dt = new DateTime("now", new DateTimeZone($tz)); //first argument "must" be a string
$dt->setTimestamp($timestamp); //adjust the object to correct timestamp
echo $dt->format('d.m.Y, H:i:s');

DateTime class is powerful, and to grasp all of its capabilities - you should devote some of your time reading about it at php.net. To answer your question fully - yes, you can adjust the time zone parameter dynamically (on each iteration while reading from db, you can create a new DateTimeZone() object).

---

Related Topics