Php, MySQL and Time Zones

Set timezone in PHP and MySQL

In PHP:

<?php
define('TIMEZONE', 'Europe/Paris');
date_default_timezone_set(TIMEZONE);

For MySQL:

<?php
$now = new DateTime();
$mins = $now->getOffset() / 60;
$sgn = ($mins < 0 ? -1 : 1);
$mins = abs($mins);
$hrs = floor($mins / 60);
$mins -= $hrs * 60;
$offset = sprintf('%+d:%02d', $hrs*$sgn, $mins);

//Your DB Connection - sample
$db = new PDO('mysql:host=localhost;dbname=test', 'dbuser', 'dbpassword');
$db->exec("SET time_zone='$offset';");

The PHP and MySQL timezones are now synchronized within your application. No need to go for php.ini or MySQL console!

This is from this article on SitePoint.

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.

Using time zones in a PHP web application

I dealt with this situation extensively in a PHP/MySQL application I wrote for a private jet operator a little over a year ago. There are different strategies to handle timezones in these two platforms, but I will explain how I did it. I set the MySQL server to UTC and run each PHP script in the timezone that the user specifies during the signup process for the user profile.

MySQL and PHP (PHP 5.2 and above) both have native datetime datatypes. MySQL's datetime is a primitive data type, while PHP 5.2 and above offers the built-in DateTime class. The MySQL datetime datatype does not include metadata for the timezone, but a PHP DateTime object always includes a timezone. If the PHP datetime constructor does not specify the optional timezone in the second argument, then the PHP datetime constructor uses the php environment variable.

Both MySQL and PHP have default timezone set in the configuration files. MySQL uses the datetime set in the config file for each db connection unless the user specifies a different timezone after connection is started with the command SET time_zone = [timezone];. PHP also sets a timezone environment variable for each script using the timezone set in the server config file, and this environment variable can be overriden using the PHP function date_default_timezone_set() after the script starts.

The PHP DateTime class has a property called timezone, which is a PHP DateTimeZone object. The DateTimeZone object is specified using a string for the exact time zone. The list of timezones is comprehensive, having hundreds of individual time zones across the world. The PHP time zones will account for daylight savings time automatically.

When the user generates a datetime in the web app, construct a PHP datetime object in the timezone of the user's profile. Then use the setTimezone method to modify the DateTime object to the UTC timezone. Now you have the user's datetime in UTC, and you can store the value in the database. Use the DateTime format method to express the data as a string in the format accepted by MySQL.

So the user generates a datetime, and you make a PHP datetime object in the user's specified timezone:

// set using an include file for user profile
$user_timezone = new DateTimeZone('America/New_York'); 

// 1st arg in format accepted by PHP strtotime
$date_object1 = new DateTime('8/9/2012 5:19 PM', $user_timezone); 
$date_object1->setTimezone(new DateTimeZone('UTC'));
$formated_string = $date_object1->format('Y-m-d H:i:s');
$query_string = "INSERT INTO `t_table1` (`datetime1`) VALUES('$formated_string')";

When you retrieve the value from the database, construct in UTC and then convert to the user's time zone.

$query_string = "SELECT `datetime1` FROM `t_table1`";
$date_object1 = new DateTime($datetime_string_from_mysql, new DateTimeZone('UTC'));
$date_object1->setTimezone($user_timezone);
$string_for_display_in_application = $date_object1->format('m/d/Y g:i a');

Using this method, your datetime values are always stored in UTC inside the db, and the user always experiences the values in his/her profile's time zone. PHP will correct for Daylight Savings Time if necessary for each time zone.

One gotcha: This explanation does not cover the MySQL timestamp datatype. I recommend using the MySQL datetime datetype to store datetime values, not the timestamp datatype. The timestamp datatype is covered in the manual here.

Edit:
You can produce an array containing every PHP timezone string using listIdentifiers, which is a static method of the DateTimeZone class.

PHP, MySQL and Time Zones

This answer has been updated to accomodate the bounty. The original, unedited answer is below the line.

Almost all of the question points added by the bounty owner are in relation to how MySQL and PHP datetimes should interact, in the context of timezones.

MySQL still has pathetic timezone support, which means that the intelligence has to be PHP-side.

• Set your MySQL connection timezone to UTC as documented in the link above. This will cause all datetimes handled by MySQL, including NOW(), to be handled sanely.
• Always use DATETIME, never use TIMESTAMP unless you very expressly require the special behavior in a TIMESTAMP. This is less painful than it used to be.
  
  
  • It's ok to store the Unix epoch time as an integer if you have to, such as for legacy purposes. The epoch is UTC.
  • MySQL's preferred datetime format is created using the PHP date format string Y-m-d H:i:s
• Convert all PHP datetimes to UTC when storing them in MySQL, which is a trivial thing as outlined below
• Datetimes returned from MySQL can be handed safely to the PHP DateTime constructor. Be sure to pass in a UTC timezone as well!
• Convert the PHP DateTime to the user's local timezone on echo, no sooner. Thankfully DateTime comparison and math against other DateTimes will take into account the timezone that each is in.
• You're still up to the whims of the DST database provided with PHP. Keep your PHP and OS patches up to date! Keep MySQL in the blissful state of UTC to remove one potential DST annoyance.

That addresses most of the points.

The last thing is a doozy:

• What should someone do if they have previously inserted data (e.g. using NOW()) without worrying about the time zone to make sure everything stays consistent?

This is a real annoyance. One of the other answers pointed out MySQL's CONVERT_TZ, though I'd personally have done it by hopping between server-native and UTC timezones during selects and updates, 'cause I'm hardcore like that.

---

the app should also be able to SET/Choose the DST accordingly itself for each user.

You don't need to and should not do this in the modern era.

Modern versions of PHP have the DateTimeZone class, which includes the ability to list named timezones. Named timezones allow the user to select their actual location, and have the system automatically determine their DST rules based on that location.

You can combine DateTimeZone with DateTime for some simple but powerful functionality. You can simply store and use all of your timestamps in UTC by default, and convert them to the user's timezone on display.

// UTC default
    date_default_timezone_set('UTC');
// Note the lack of time zone specified with this timestamp.
    $nowish = new DateTime('2011-04-23 21:44:00');
    echo $nowish->format('Y-m-d H:i:s'); // 2011-04-23 21:44:00
// Let's pretend we're on the US west coast.  
// This will be PDT right now, UTC-7
    $la = new DateTimeZone('America/Los_Angeles');
// Update the DateTime's timezone...
    $nowish->setTimeZone($la);
// and show the result
    echo $nowish->format('Y-m-d H:i:s'); // 2011-04-23 14:44:00

By using this technique, the system will automatically select the correct DST settings for the user, without asking the user whether or not they're currently in DST.

You can use a similar method to render the select menu. You can continually reassign the time zone for the single DateTime object. For example, this code will list the zones and their current times, at this moment:

$dt = new DateTime('now', new DateTimeZone('UTC')); 
foreach(DateTimeZone::listIdentifiers() as $tz) {
    $dt->setTimeZone(new DateTimeZone($tz));
    echo $tz, ': ', $dt->format('Y-m-d H:i:s'), "\n";
}

You can greatly simplify the selection process by using some client-side magic. Javascript has a spotty but functional Date class, with a standard method to get the UTC offset in minutes. You can use this to help narrow down the list of likely timezones, under the blind assumption that the user's clock is right.

Let's compare this method to doing it yourself. You'd need to actually perform date math every single time you manipulate a datetime, in addition to pushing a choice off on the user that they aren't going to really care about. This isn't just sub-optimal, it's bat-guano insane. Forcing users to signify when they want DST support is asking for trouble and confusion.

Further, if you wanted to use the modern PHP DateTime and DateTimeZone framework for this, you'd need to use deprecated Etc/GMT... timezone strings instead of named timezones. These zone names may be removed from future PHP versions, so it'd be unwise to do that. I say all of this from experience.

tl;dr: Use the modern toolset, spare yourself the horrors of date math. Present the user with a list of named time zones. Store your dates in UTC, which won't be impacted by DST in any way. Convert datetimes to the user's selected named time zone on display, not earlier.

---

As requested, here's a loop over the available time zones displaying their GMT offset in minutes. I selected minutes here to demonstrate an unfortunate fact: not all offsets are in whole hours! Some actually switch half an hour ahead during DST instead of a whole hour. The resulting offset in minutes should match that of Javascript's Date.getTimezoneOffset.

$utc = new DateTimeZone('UTC');
$dt = new DateTime('now', $utc); 
foreach(DateTimeZone::listIdentifiers() as $tz) {
    $local = new DateTimeZone($tz);
    $dt->setTimeZone($local);
    $offset = $local->getOffset($dt); // Yeah, really.
    echo $tz, ': ', 
         $dt->format('Y-m-d H:i:s'),
         ', offset = ',
         ($offset / 60),
         " minutes\n";
}

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.

How to store time with timezone infomation in mysql datetime column?

What exactly 2018-12-16T17:36:29+11:00 means?

This is a UTC offset timestamp, so you have:

UTC Date: `2018-12-16`
UTC Time: `17:36:29`
Offset: `+11:00`

I'm not familiar with Shopify and their APIs, however, if it is important to know / store the timezone, I would store the UTC time in one column of your database (using datetime fieldtype), and the offset hours in another column.

Example:

|    Date                  |   Timezone  |
==========================================
|    2018-12-16 17:36:29   |   +11       |

This way, you can now get an accurate time in the desired timezone. Example:

$strtime = $record['Date'].' '.$record['Timezone']' hours';
echo date('Y-m-d H:i:s', strtotime($strtime));

Dealing with PHP server and MySQL server in different time zones

As of PHP 5.1.0 you can use date_default_timezone_set() function to set the default timezone used by all date/time functions in a script.

For MySql (quoted from MySQL Server Time Zone Support page)

Before MySQL 4.1.3, the server operates only in the system time zone set at startup. Beginning with MySQL 4.1.3, the server maintains several time zone settings, some of which can be modified at runtime.

Of interest to you is per-connection setting of the time zones, which you would use at the beginning of your scripts

SET timezone = 'Europe/London';

As for detecting the client timezone setting, you could use a bit of JavaScript to get and save that information to a cookie, and use it on subsequent page reads, to calculate the proper timezone.

//Returns the offset (time difference) between Greenwich Mean Time (GMT) 
//and local time of Date object, in minutes.
var offset = new Date().getTimezoneOffset(); 
document.cookie = 'timezoneOffset=' + escape(offset);

Or you could offer users the chioce to set their time zones themselves.

---

Related Topics