Some summary of PHP and MySQL time zone settings

Friends who use foreign hosting spaces often encounter problems with time zone settings. The city setting on the PHP script side can be set under php.ini to set the value of the date.timezone key to 'Asia/Shanghai'. Usually the shared virtual host itself does not have permission to modify php.ini. At this time, you should add it to the public part of the program: ini_set('date.timezone','Asia/Shanghai'); Dynamically modify the settings of php.ini. You can then test whether the time is correct:

var_dump(date()); If the local time of the server is correct, then the problem will usually be solved. Attached, PHP 5.1 and above provides a special function to modify the corresponding time zone: date_default_timezone_set('Asia/Shanghai'); It is recommended to use this function because it is more versatile. Corresponding to 'Asia/Shanghai', other mainland time zones that can be used are: Asia/Chongqing, Asia/Shanghai, Asia/Urumqi (in order, Chongqing, Shanghai, Urumqi); available in Hong Kong and Taiwan: Asia/Macao, Asia/Hong_Kong, Asia /Taipei (Macau, Hong Kong, Taipei in order); and Singapore: Asia/Singapore; other available values ​​are: Etc/GMT-8, Singapore, Hongkong, PRC; foreigners seem to have missed Beijing.

However, after I successfully modified the time zone on the PHP side, I found that the date was not recorded correctly. At this time, I considered whether it was a database problem. Sure enough, because the function inserted by the program does not call PHP's time, but directly uses MySQL's CURRECT_TIMESTAMP. At this time, you need to consider whether the time zone in MySQL can be modified.

Referring to the MySQL documentation, I found a feasible SQL statement: SET GLOBAL time_zone = '+8:00'; Among them, '+8:00' is the representation method of East Eighth District, and other urban areas can be deduced in sequence. And when I inserted a change statement into the database model, I found that the permissions were insufficient. Next, I debugged many statements, such as: DATE_ADD(UTC_TIMESTAMP(), INTERVAL 8 HOUR); SQL statement to display time zone: SHOW VARIABLES LIKE 'system_time_zone' etc. There is no complete solution due to MySQL permission limitations. I Googled it and found that foreigners have a very good solution. But he needs to modify each SQL statement that inserts data. This solution is not very effective. Once the database time zone is changed to normal, the corresponding SQL statement must be changed back.

And I think now that the PHP side can already solve the time problem correctly. Although the MySQL database can be solved using the corresponding functions, it will have to be changed back if it is migrated to another host environment in the future. The corresponding field is of type TIMESTAMP, and the default value is CURRECT_TIMESTAMP. Of course, the time can be specified.

My approach is to let PHP insert the current correct time, so although the program needs to be modified accordingly. However, if you modify the configuration in the future, you only need to modify one place. When finally inserting into the database, pay attention to the format: date('Y-m-d H:i:s')

Reference article: http://www.modwest.com/help/kb6-256.html You can set it up like this on your own host: SET GLOBAL time_zone = '+8:00'; It's a pity that functions like UNIX_TIMESTAMP() can no longer be used.