MySQL time zone problems

Source: Internet
Author: User
In the United States, the website server published an article with a timestamp of 1320115246. After being corrected using PHP, it can be displayed as a normal time, but the time for classification goes back to the article of July. When running SQL on the server side, we found that the article was released from 2011 to 10-3120: 40: 46, which means the local time zone of the server is used.

In the United States, the website server published an article with a timestamp of 1320115246. After being corrected using PHP, it can be displayed as a normal time, but the time for classification goes back to the article of July. When running SQL on the server side, we found that the article was published at 20:40:46, and adopted the local time zone of the server.

In the United States, the website server published an article with a timestamp of 1320115246. After being corrected using PHP, it can be displayed as a normal time, but the time for classification goes back to the article of July. When running SQL on the server side, we found that the article was published at 20:40:46, which adopted the local time zone of the server. The article was actually published at 10:40:46.

PHP cannot be used for classification, so use SQL for correction. It is very easy to execute such an SQL statement before classifying the SQL statement, and you can set it to the UTC + 8 zone:

mysql_query("SET time_zone = '+8:00' ");

1. View MySQL Time Zone

Connect to MySQL and execute the following SQL statement:

mysql> show variables like '%time_zone%';+------------------+--------+| Variable_name    | Value  |+------------------+--------+| system_time_zone |        || time_zone        | SYSTEM |+------------------+--------+2 rows in set

2. modify my. cnf

Add default-time-zone = timezone under [mysqld] to modify the time zone.

For example, change to + 8 zone: default-time-zone = '+'. After the modification is complete, restart MySQL and you will be OK ~

Note: The configuration item must be added under [mysqld]; otherwise, the unknown variable 'default-time-zone = + 8:00 'will appear'

You can also run the set time_zone = timezone command, for example, Beijing time (GMT + 0800) set time_zone = '+ ';

This is a little different from the time zone settings in php, such as Beijing time in php is date_default_timezone_set ('etc/GMT-8 ');

US pst time (GMT-08: 00) set time_zone = ';

The first method is temporary modification. If you cannot modify the server configuration file, use the first method.

It is best to directly change the configuration file. First find the mysql configuration file my. cnf, usually under/etc/my. cnf, you can find cd/etc and find my. cnf to see if it can be found. After finding it, modify it and use VI to open it: vi/etc/my. cnf, open it, find [mysqld], input "/" in VI, and then enter [mysqld] to locate the location of [mysqld, add the default-time-zone = '+ 8:00' line under it, save and exit, and restart MYSQL to make the time zone take effect. At this time, check the two commands in MYSQL and you will find that the time zone is set correctly.

In this way, the time zone problem is solved.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.