1.起因:
前两天发现有一台mysql的测试机环境出现了和系统时区不一致的情况,情况如下:
mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | PDT |
| time_zone | SYSTEM |
+------------------+--------+
2 rows in set (0.01 sec)
[root@test2 ~]# date
2017年 01月 18日 星期三 01:15:16 PST
夏令时是几月到几月?
2.解决方案:
my.cnf配制文件修改:
可以通过修改my.cnf
在 [mysqld] 之下加
default-time-zone=timezone
数据库时区设置、来修改时区。如:
default-time-zone = '+8:00'
通过命令:
set time_zone = timezone
哪个时区有夏令时。
比如北京时间(GMT+0800)
set time_zone = '+8:00';
eg:mysql> set time_zone = '+8:00';
Query OK, 0 rows affected (0.17 sec)mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2016-06-23 16:32:53 |
+---------------------+
1 row in set (0.00 sec)mysql> set time_zone = '-8:00';
Query OK, 0 rows affected (0.00 sec)mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2016-06-23 00:33:21 |
+---------------------+
1 row in set (0.00 sec)
mysql> show tables like "%time%";
+---------------------------+
| Tables_in_mysql (%time%) |
+---------------------------+
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
+---------------------------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM time_zone_name WHERE name LIKE 'America/%' limit 10;
+----------------------------------+--------------+
| Name | Time_zone_id |
+----------------------------------+--------------+
| America/Adak | 54 |
| America/Anchorage | 55 |
| America/Anguilla | 56 |
| America/Antigua | 57 |
| America/Araguaina | 58 |
| America/Argentina/Buenos_Aires | 59 |
| America/Argentina/Catamarca | 60 |
| America/Argentina/ComodRivadavia | 61 |
| America/Argentina/Cordoba | 62 |
| America/Argentina/Jujuy | 63 |
+----------------------------------+--------------+
10 rows in set (0.00 sec)
mysql> SELECT CONVERT_TZ('2016-03-13 01:30:00','-8:00','America/Los_Angeles') AS TIME\G
*************************** 1. row ***************************
TIME: 2016-03-13 01:30:00
1 row in set (0.00 sec)mysql> SELECT CONVERT_TZ('2016-03-13 02:30:00','-8:00','America/Los_Angeles') AS TIME\G
*************************** 1. row ***************************
TIME: 2016-03-13 03:30:00
1 row in set (0.00 sec)
版权声明:本站所有资料均为网友推荐收集整理而来,仅供学习和研究交流使用。
工作时间:8:00-18:00
客服电话
电子邮件
admin@qq.com
扫码二维码
获取最新动态