Using time zones in a PHP web application

Question

I’ve been watching for several hours that the best way to use time zones in a PHP / MySQL web application is to find the final answer. From what I have learned so far, it is best to store all sorts of things in a database in UTC (correct me if I am wrong).

When a user logs in, I will ask them for the time zone there, and then save it against the user. It will be in this format as a dropdown menu:

<option value="Europe/London">(GMT) Greenwich Mean Time : London</option> 

The application that I create will allow users to establish agreements in the future with people (meetings), like a calendar. Obviously, during the year, different time zones have different summer periods, any idea how I will satisfy this?

Say that a user from the UK makes an appointment at 3:00 p.m. on January 24, 2013 and invites someone who lives in California to this meeting, how can I get it so that the American sees this meeting in his time zone and the UK user sees it in time zone? (Note that both users are registered and set their time zone).

Does anyone have a clear explanation and maybe some examples for this? Or can you tell me where I can find this?

thanks

Answer 1

I examined this situation in detail in the PHP / MySQL application that I wrote for a private jet operator just over a year ago. There are various time zone processing strategies on these two platforms, but I will explain how I did it. I installed the MySQL server in UTC and ran each PHP script in the time zone that the user indicates during the registration process for the user profile.

MySQL and PHP (PHP 5.2 and later) have their own datetime data types. MySQL datetime is a primitive data type, and PHP 5.2 and later offers a built-in DateTime class . MySQL's datetime data type does not include metadata for the time zone, but the PHP DateTime object always includes the time zone. If the PHP datetime constructor does not specify an optional time zone in the second argument, then the PHP datetime constructor uses the php environment variable.

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

The PHP DateTime class has a timezone property, which is a PHP DateTimeZone object. The DateTimeZone object is specified using a string for the exact time zone. The time zone list is comprehensive, with hundreds of individual time zones around the world. PHP time zones will automatically consider daylight saving time.

When a user creates a date-time in a web application, create a PHP datetime object in the time zone of the user profile. Then use the setTimezone method to change the DateTime object to the UTC time zone. Now you have the user datetime in UTC, and you can save the value in the database. Use the DateTime format method to express the data as a string in the format accepted by MySQL.

Thus, the user generates a date-time, and you create the PHP datetime object in the user-specified time zone:

 // 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('Ymd H:i:s'); $query_string="INSERT INTO `t_table1` (`datetime1`) VALUES('$formated_string')"; 

When you retrieve a value from the database, create in UTC and then convert it 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 db, and the user always experiences the values ​​in the time zone of his profile. PHP will adjust daylight saving time if necessary for each time zone.

One of them: this explanation does not apply to the MySQL timestamp data type. I recommend using the MySQL datetime datatype to store date and time values, not the timestamp datatype. The timestamp data type is given in the manual here .

Edit: You can create an array containing each line in the PHP time range using listIdentifiers , which is a static method of the DateTimeZone class.

Answer 2

In MySQL, you need to do the following:

• Store each user-selected time zone in a place where you can get it when you make database queries on behalf of that user. You can save it as a string.
• Before performing work on behalf of a specific user (for example, saving or receiving time and dates of a meeting), execute SET time_zone = (stored time zone setting)

This will cause the time zones to be appropriately converted to each person’s local time.

Edit:

It works because

• MySQL is trying to use UTC (universal time, formerly known as Greenwich Mean Time) to store DATETIME and TIMESTAMP data elements in tables.
• He can only do this correctly if he knows the correct local time zone for each data item that he sets for applications.
• In applications that do not care about different time zones, he does this in a MySQL server. See https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html . Most people who run multinational and multitasking applications set their server time zones in UTC, rather than in local time, because it simplifies the sorting process.
• It makes no sense to try to convert time from UTC to local time if you do not know the date and time zone, because local time is turned on and off at different times of the year. Just try to get it right for all three of Israel, Arizona and New York, I dare! Israel switches between daytime and standard time for Easter and Rosh Hashanah; Arizona does not switch, and New York switches to the whim of the US federal legislature.
• This sets the time_zone parameter for the session ( SET time_zone = something ). If you do not set it, it uses the time zone representation in paragraph 3 above. If you install it, the server will use this as a time zone to convert its internal representation to the representation that it sends back in requests.
• You can get a list of the names of the available time zones on your MySQL server by issuing SELECT Name from mysql.time_zone_name . This can populate a drop-down menu from which the user can select their time zone. If this query does not return any elements, see below https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html .

So this means that you can set the time_zone session setting to a specific time zone of the user, and then return all the time in that user time zone. In addition, any DATETIME or TIMESTAMP elements that you INSERT or UPDATE will be converted from this custom timezone to the internal MySQL view, as they are placed in your tables.

Be careful: in web applications with persistent MySQL connections, working for a new user query inherits the time_zone connection time setting. Do not forget to reset for the new user.

If you execute a query that returns local time data for several users, and these users are in different time zones, you cannot use this set of functions for each MySQL session. You can get around this by running different requests for different users and changing the time_zone setting between them.

Or you can use the MySQL function

 CONVERT_TZ(datetime,'UTC','user_time_zone') 

or similar for each item.

Alternatively, Java and DotNET have their own high-quality time zone manipulation systems. Thus, you can choose to start your MySQL server with the time_zone setting in UTC and make all your changes in the time zone in your application.