[Mysql] modify mysql time zone
First you need to look at the current time zone of the mysql, using the time_zone parameter
Mysql > show global variables like'% zone%'
+-+ +
| | Variable_name | Value |
+-+ +
| | system_time_zone | CST |-system time, Beijing time |
| | time_zone | SYSTEM |-the time zone used by mysql. This is the time zone of the calling system, that is, Beijing time |
+-+ +
2 rows in set (0.17 sec)
Verify:
Mysql > select now ()
+-+
| | now () |
+-+
| | 2018-11-30 17:11:55 |
+-+
1 row in set (0.18 sec)
Change to utc time zone
Mysql > set time_zone='+0:00'
Mysql > select now ()
+-+
| | now () |
+-+
| | 2018-11-30 09:12:06 |
+-+
1 row in set (0.18 sec)
Changed to East eighth District.
Mysql > set time_zone='+8:00'
Query OK, 0 rows affected (0.24 sec)
Mysql > select now ()
+-+
| | now () |
+-+
| | 2018-11-30 17:21:40 |
+-+
1 row in set (0.72 sec)
Permanent modification can be done by modifying my.cnf
Add under [mysqld]
Default-time-zone=timezone
To change the time zone. Such as:
Default-time-zone ='+ 8VR 00'
Remember to restart msyql when the modification is finished.
Be sure to add it under [mysqld], otherwise unknown variable 'default-time-zone=+8:00' will appear.
Note: the time zone has an impact on timestamp types, while datetime has no effect, such as:
Mysql > select * from uc_smallfeature_sharerecords (timestamp is of type timestamp)
+-+ +
| | id | create_time | destination | platform | userid | username | |
+-+ +
| | 3425 | 2018-12-25 19:42:51 | 123213 | 123,123 | 123,123 |
+-+ +
Mysql > select * from uc_push_token (timestamp is of type datetime)
+-+
| | user_id | push_type | token | create_time | update_time | |
+-+
| | 1 | 0 | | 2018-12-25 19:40:49 | 2018-12-25 19:40:49 |
+-+
Mysql > show global variables like'% zone%'
+-+ +
| | Variable_name | Value |
+-+ +
| | system_time_zone | UTC |
| | time_zone | + 08:00 |
+-+ +
2 rows in set (0.00 sec)
Mysql > set global time_zone='+0:00'
Query OK, 0 rows affected (0.00 sec)
Reconnect query:
Mysql > select * from uc_smallfeature_sharerecords; (timestamp type has changed)
+-+ +
| | id | create_time | destination | platform | userid | username | |
+-+ +
| | 3425 | 2018-12-25 11:42:51 | 123213 | 123,123 | 123,123 |
+-+ +
1 row in set (0.00 sec)
Mysql > select * from uc_push_token; (datetime type will not change)
+-+
| | user_id | push_type | token | create_time | update_time | |
+-+
| | 1 | 0 | | 2018-12-25 19:40:49 | 2018-12-25 19:40:49 |
+-+
