Working with timezone in PHP & MySQL

How should I go about dealing with timezone. Is it safe to just store the offset for the user? Or should I also have the Area/Location? When I compared the offset values in Wikipedia and PHP, some doesn't match. Which should I trust?

Lastly how should I go about with it in PHP. Can I just do a 开发者_JAVA百科"Time - Server Offset + User Offset"?

---

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.

---

You can save the timezone in the database as string using a dropdown menu filled with the timezones

When the user login save the timezone in a $_SESSION['timezone'] variable and set the timezone in your scripts like this:

date_default_timezone_set($_SESSION['timezone']);

---

You mention a user. I suggest you let the user selects his/her timezone and you store that in your database.

Here is how to synchronize PHP (>=5.3) and MySQL timezones per session and user settings. Put this where it runs when you need set and synchronized timezones.

date_default_timezone_set($my_timezone);
$n = new \DateTime();
$h = $n->getOffset()/3600;
$i = 60*($h-floor($h));
$offset = sprintf('%+d:%02d', $h, $i);
$this->db->query("SET time_zone='$offset'");

Where $my_timezone is one in the list of PHP timezones: http://www.php.net/manual/en/timezones.php

The PHP timezone has to be converted into the hour and minute offset for MySQL. That's what lines 1-4 do. The query line is using the Code Igniter framework, which you may not use, but the the query is show in the parentheses.

---

If you are dealing with the MySQL you can use the time zone information from (mysql_tzinfo_to_sql) it will automatically identify the users time according to the time zone.and you have to create a column of type timestamp that stores the users timezone information.